Search bar

[jacobsenm]

Hi

What I miss in LANSweeper 4, in comparison with version 3.5, is that a search is querying like "Starts with" in stead of "Contains".

For example if I want to search for software that contains the word "office" it does not find all the office licenses.
This is what I had liked so much in the old (current) 3.5 version.

Is this something that you can integrate again ?

Thank you

[jacobsenm]

Beautiful.

This is EXACTLY how it should be.

Thanks a lot for this quick resolution.

Best regards

[Hemoco]

Try this:

ALTER PROCEDURE dbo.web40fullsearch
(@q nvarchar(300))
AS SELECT     TOP 100 type, Thumbnail, searchfield, line1, line2,sortfield
FROM         (SELECT     1 AS Type, 'comp.gif' AS Thumbnail, cast(Computername as nvarchar) AS Searchfield, Computer AS line1, Domain + ' - ' + LastknownIP AS line2,computer as sortfield
                       FROM          dbo.tblComputers
                       WHERE      (Computer LIKE @q + '%')
 UNION
                       (SELECT DISTINCT 2 AS Type, 'domain.gif' AS thumbnail, Domain AS Searchfield, Domain AS line1, '' AS line2,Domain as sortfield
                        FROM         dbo.tblComputers
                        WHERE     (Domain LIKE @q + '%'))
UNION
(SELECT DISTINCT 3 AS Type, 'software.png' AS thumbnail, softwareName AS Searchfield, softwareName AS line1, SoftwarePublisher AS line2,softwareName as sortfield
 FROM         dbo.tblSoftware
 WHERE     (softwareName LIKE '%' + @q + '%'))
UNION
(SELECT     4 AS Type, Userdomain + '\' + Username AS Thumbnail, Userdomain + '\' + Username AS Searchfield, ISNULL(Displayname,userdomain +'\' + username ) AS Line1, 
                        Company + ' - ' + Department AS Line2,ISNULL(Displayname, username ) as sortfield
 FROM         dbo.tblADusers
 WHERE     (Username LIKE @q  + '%') OR
                        (Firstname LIKE @q  + '%') OR
                        (Lastname LIKE @q  + '%') OR
                        (Displayname LIKE @q  + '%'))
union
(
SELECT     5 AS Type, 'network.png' AS Thumbnail, cast(Computername as varchar) AS Searchfield, LastknownIP + ' - ' + Computer AS Line1, FQDN AS Line2,LastknownIP  as sortfield
FROM         dbo.tblComputers
WHERE     (LastknownIP  LIKE @q + '%'))
union
(SELECT     6 AS Type, 'rep.png' AS Thumbnail, Reportquery AS Searchfield, Reporttitle AS Line1, '' AS Line2,Reporttitle  as sortfield

FROM         dbo.tsysreports
WHERE     (Reporttitle LIKE @q + '%'))
union
(SELECT     7 AS Type, 'networkclient.png' AS Thumbnail, cast(dbo.tblComputers.Computername as nvarchar) AS Searchfield, 
                      dbo.tblNetwork.MACaddress + ' - ' + dbo.tblNetwork.Description AS Line1, dbo.tblComputers.Computer AS Line2,dbo.tblNetwork.MACaddress as sortfield
FROM         dbo.tblComputers INNER JOIN
                      dbo.tblNetwork ON dbo.tblComputers.Computername = dbo.tblNetwork.Computername
WHERE     (dbo.tblNetwork.MACaddress LIKE @q + '%') AND (NOT (dbo.tblNetwork.MACaddress IS NULL))
)
union
(SELECT     8 AS Type, 'tag.png' AS Thumbnail, cast(dbo.tblComputers.Computername as nvarchar) AS Searchfield, 
                      dbo.tblComputerSystemProduct.IdentifyingNumber + ' - ' + dbo.tblComputerSystemProduct.Vendor AS Line1, 
                      dbo.tblComputers.Computer + ' - ' + dbo.tblComputers.LastknownIP AS Line2,dbo.tblComputerSystemProduct.IdentifyingNumber as sortfield
FROM         dbo.tblComputers INNER JOIN
                      dbo.tblComputerSystemProduct ON dbo.tblComputers.Computername = dbo.tblComputerSystemProduct.Computername
WHERE     (dbo.tblComputerSystemProduct.IdentifyingNumber LIKE @q + '%'))
union
(SELECT     9 AS Type, 'comp.gif' AS Thumbnail, cast(dbo.tblComputers.Computername as nvarchar) AS Searchfield, dbo.tblOperatingsystem.Description AS Line1, 
                      dbo.tblComputers.Computer + ' - ' + dbo.tblComputers.LastknownIP AS Line2,computer as sortfield
FROM         dbo.tblComputers INNER JOIN
                      dbo.tblOperatingsystem ON dbo.tblComputers.Computername = dbo.tblOperatingsystem.Computername
WHERE     (dbo.tblOperatingsystem.Description LIKE @q + '%')
)

union
( SELECT DISTINCT 10 AS Type, 'comp.gif' AS Thumbnail, OU AS Searchfield, OU AS Line1, '' AS Line2,OU as sortfield
FROM         dbo.tblADComputers
WHERE     (OU LIKE @q + '%')
)

union
(
SELECT DISTINCT 11 AS Type, 'useringroups.png' AS Thumbnail, OU AS Searchfield, OU AS Line1, '' AS Line2,OU as sortfield
FROM         dbo.tblADusers
WHERE     (OU LIKE @q + '%')
)

union
(
SELECT DISTINCT 
                        12 AS Type, dbo.tsysDevicetypes.ItemTypeIcon16 AS Thumbnail, dbo.tblCustDevices.devicekey AS Searchfield, 
                        dbo.tblCustDevices.Displayname AS Line1, dbo.tblCustDevices.Ipaddress  + ' - ' + dbo.tsysDevicetypes.ItemTypename AS Line2,Displayname as sortfield
FROM          dbo.tsysDevicetypes INNER JOIN
                        dbo.tblCustDevices ON dbo.tsysDevicetypes.ItemType = dbo.tblCustDevices.Devicetype
WHERE      (dbo.tblCustDevices.Displayname LIKE @q + '%')
)

union
(
SELECT DISTINCT 
                        13 AS Type, dbo.tsysDevicetypes.ItemTypeIcon16 AS Thumbnail, dbo.tblCustDevices.devicekey AS Searchfield, 
                        dbo.tblCustDevices.IPaddress AS Line1, dbo.tblCustDevices.Displayname + ' - ' + dbo.tsysDevicetypes.ItemTypename AS Line2,IPaddress as sortfield
FROM          dbo.tsysDevicetypes INNER JOIN
                        dbo.tblCustDevices ON dbo.tsysDevicetypes.ItemType = dbo.tblCustDevices.Devicetype
WHERE      (dbo.tblCustDevices.IPaddress LIKE @q + '%')
)

union
(
SELECT DISTINCT 
                        14 AS Type, 'barcode.png' AS thumbnail, CAST(dbo.tblComputers.Computername AS varchar) AS Searchfield, dbo.tblCompCustom.BarCode AS line1, 
                        dbo.tblComputers.Computer + N' - ' + dbo.tblComputers.LastknownIP AS line2, dbo.tblCompCustom.BarCode AS sortfield
FROM          dbo.tblComputers INNER JOIN
                        dbo.tblCompCustom ON dbo.tblComputers.Computername = dbo.tblCompCustom.Computername
WHERE      (dbo.tblCompCustom.BarCode LIKE @q + N'%')
)


) DERIVEDTBL
ORDER BY Sortfield ASC

[jacobsenm]

Thanks !
If have modified the TOP10 to TOP100 to show more results.
However, when I type for example "Office" and hit return, than I do not get the results in the webpage

Result:
Results for search "office" - 0 items found


Is this something that you can adjust.
For the rest it is exactly what is expected/desired.

Thanks again

[Hemoco]

It's easy to change this behaviour in the query but you will get unexpected results when you search for other (non-software) items.

ALTER PROCEDURE dbo.web40Quicksearch
(@q nvarchar(300))
AS 
SELECT     TOP 10 type, Thumbnail, searchfield, line1, line2,sortfield
FROM         (SELECT     1 AS Type, 'comp.gif' AS Thumbnail, cast(Computername as nvarchar) AS Searchfield, Computer AS line1, Domain + ' - ' + LastknownIP AS line2,computer as sortfield
                       FROM          dbo.tblComputers
                       WHERE      (Computer LIKE @q + '%')
 UNION
                       (SELECT DISTINCT 2 AS Type, 'domain.gif' AS thumbnail, Domain AS Searchfield, Domain AS line1, '' AS line2,Domain as sortfield
                        FROM         dbo.tblComputers
                        WHERE     (Domain LIKE @q + '%'))
UNION
(SELECT DISTINCT 3 AS Type, 'software.png' AS thumbnail, softwareName AS Searchfield, softwareName AS line1, SoftwarePublisher AS line2,softwareName as sortfield
 FROM         dbo.tblSoftware
 WHERE     (softwareName LIKE '%' + @q + '%'))
UNION
(SELECT     4 AS Type, Userdomain + '\' + Username AS Thumbnail, Userdomain + '\' + Username AS Searchfield, ISNULL(Displayname,userdomain +'\' + username ) AS Line1, 
                        Company + ' - ' + Department AS Line2,ISNULL(Displayname, username ) as sortfield
 FROM         dbo.tblADusers
 WHERE     (Username LIKE @q  + '%') OR
                        (Firstname LIKE @q  + '%') OR
                        (Lastname LIKE @q  + '%') OR
                        (Displayname LIKE @q  + '%'))
union
(
SELECT     5 AS Type, 'network.png' AS Thumbnail, cast(Computername as varchar) AS Searchfield, LastknownIP + ' - ' + Computer AS Line1, FQDN AS Line2,LastknownIP  as sortfield
FROM         dbo.tblComputers
WHERE     (LastknownIP  LIKE @q + '%'))
union
(SELECT     6 AS Type, 'rep.png' AS Thumbnail, Reportquery AS Searchfield, Reporttitle AS Line1, '' AS Line2,Reporttitle  as sortfield

FROM         dbo.tsysreports
WHERE     (Reporttitle LIKE @q + '%'))
union
(SELECT     7 AS Type, 'networkclient.png' AS Thumbnail, cast(dbo.tblComputers.Computername as nvarchar) AS Searchfield, 
                      dbo.tblNetwork.MACaddress + ' - ' + dbo.tblNetwork.Description AS Line1, dbo.tblComputers.Computer AS Line2,dbo.tblNetwork.MACaddress as sortfield
FROM         dbo.tblComputers INNER JOIN
                      dbo.tblNetwork ON dbo.tblComputers.Computername = dbo.tblNetwork.Computername
WHERE     (dbo.tblNetwork.MACaddress LIKE @q + '%') AND (NOT (dbo.tblNetwork.MACaddress IS NULL))
)
union
(SELECT     8 AS Type, 'tag.png' AS Thumbnail, cast(dbo.tblComputers.Computername as nvarchar) AS Searchfield, 
                      dbo.tblComputerSystemProduct.IdentifyingNumber + ' - ' + dbo.tblComputerSystemProduct.Vendor AS Line1, 
                      dbo.tblComputers.Computer + ' - ' + dbo.tblComputers.LastknownIP AS Line2,dbo.tblComputerSystemProduct.IdentifyingNumber as sortfield
FROM         dbo.tblComputers INNER JOIN
                      dbo.tblComputerSystemProduct ON dbo.tblComputers.Computername = dbo.tblComputerSystemProduct.Computername
WHERE     (dbo.tblComputerSystemProduct.IdentifyingNumber LIKE @q + '%'))
union
(SELECT     9 AS Type, 'comp.gif' AS Thumbnail, cast(dbo.tblComputers.Computername as nvarchar) AS Searchfield, dbo.tblOperatingsystem.Description AS Line1, 
                      dbo.tblComputers.Computer + ' - ' + dbo.tblComputers.LastknownIP AS Line2,computer as sortfield
FROM         dbo.tblComputers INNER JOIN
                      dbo.tblOperatingsystem ON dbo.tblComputers.Computername = dbo.tblOperatingsystem.Computername
WHERE     (dbo.tblOperatingsystem.Description LIKE @q + '%')
)

union
( SELECT DISTINCT 10 AS Type, 'comp.gif' AS Thumbnail, OU AS Searchfield, OU AS Line1, '' AS Line2,OU as sortfield
FROM         dbo.tblADComputers
WHERE     (OU LIKE @q + '%')
)

union
(
SELECT DISTINCT 11 AS Type, 'useringroups.png' AS Thumbnail, OU AS Searchfield, OU AS Line1, '' AS Line2,OU as sortfield
FROM         dbo.tblADusers
WHERE     (OU LIKE @q + '%')
)

union
(
SELECT DISTINCT 
                        12 AS Type, dbo.tsysDevicetypes.ItemTypeIcon16 AS Thumbnail, dbo.tblCustDevices.devicekey AS Searchfield, 
                        dbo.tblCustDevices.Displayname AS Line1, dbo.tblCustDevices.Ipaddress  + ' - ' + dbo.tsysDevicetypes.ItemTypename AS Line2,Displayname as sortfield
FROM          dbo.tsysDevicetypes INNER JOIN
                        dbo.tblCustDevices ON dbo.tsysDevicetypes.ItemType = dbo.tblCustDevices.Devicetype
WHERE      (dbo.tblCustDevices.Displayname LIKE @q + '%')
)

union
(
SELECT DISTINCT 
                        13 AS Type, dbo.tsysDevicetypes.ItemTypeIcon16 AS Thumbnail, dbo.tblCustDevices.devicekey AS Searchfield, 
                        dbo.tblCustDevices.IPaddress AS Line1, dbo.tblCustDevices.Displayname + ' - ' + dbo.tsysDevicetypes.ItemTypename AS Line2,IPaddress as sortfield
FROM          dbo.tsysDevicetypes INNER JOIN
                        dbo.tblCustDevices ON dbo.tsysDevicetypes.ItemType = dbo.tblCustDevices.Devicetype
WHERE      (dbo.tblCustDevices.IPaddress LIKE @q + '%')
)

union
(
SELECT DISTINCT 
                        14 AS Type, 'barcode.png' AS thumbnail, CAST(dbo.tblComputers.Computername AS varchar) AS Searchfield, dbo.tblCompCustom.BarCode AS line1, 
                        dbo.tblComputers.Computer + N' - ' + dbo.tblComputers.LastknownIP AS line2, dbo.tblCompCustom.BarCode AS sortfield
FROM          dbo.tblComputers INNER JOIN
                        dbo.tblCompCustom ON dbo.tblComputers.Computername = dbo.tblCompCustom.Computername
WHERE      (dbo.tblCompCustom.BarCode LIKE @q + N'%')
)

) DERIVEDTBL
ORDER BY Sortfield ASC

[jacobsenm]

Indeed.
That was working only in the Software search bar.
And that is ok if it still will be in LANSweeper 4

Thanks

[Hemoco]

Do you only use this for software search in 3.5?