computer search - 0 results

[heinreyn]

Hi,
I have an issue with the computer search tool.
I have several pc's on which the problem occurs - not all!

When I try to search for a computername; the tool suggests the computer; but when I actually search for it, it returns 0 results. When I search for the user that is logged on to this pc, and I click the pc name, it returns the page with the computer details correctly.

It seems that the search results function works incorrectly (although the suggestion function works good)!

what could be the problem?

thanks in advance!

[heinreyn]

ok, seems to work now. thanks a lot!

[heinreyn]

In fact it's an existing setup from the previous IT people. I took over and trying to figure things out now...

[Hemoco]

I see now:
You changed the stored procedure yourself because "barcode" is not included in the default setup.

USE [lansweeperdb]
GO
/****** Object:  StoredProcedure [dbo].[web30compsearch]    Script Date: 12/29/2009 12:53:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[web30compsearch](@comp varchar(80))
AS
SELECT      dbo.tblComputers.Computername, dbo.tblOperatingsystem.Description, dbo.tblComputers.Domain, dbo.tblOperatingsystem.Caption, 
                        CAST(dbo.web30ProcessorCapacity.NrOfProcessors AS varchar) + ' * ' + CAST(dbo.web30ProcessorCapacity.MaxClockSpeed AS varchar) 
                        + ' Mhz' AS Processor, CAST(CAST(CAST(dbo.tblComputersystem.TotalPhysicalMemory AS bigint) / 1024 / 1024 AS NUMERIC) AS varchar) 
                        + ' MB' AS Memory, dbo.tblComputers.Lastseen AS [Last seen], CASE ISNULL(dbo.tblOperatingsystem.Description, 'NOT SCANNED') 
                        WHEN 'NOT SCANNED' THEN 1 ELSE 0 END AS Notscanned
FROM          dbo.tblComputers LEFT OUTER JOIN
                        dbo.tblCompCustom ON dbo.tblComputers.Computername = dbo.tblCompCustom.Computername LEFT OUTER JOIN
                        dbo.tblComputersystem ON dbo.tblComputers.Computername = dbo.tblComputersystem.Computername LEFT OUTER JOIN
                        dbo.web30ProcessorCapacity ON dbo.tblComputers.Computername = dbo.web30ProcessorCapacity.Computername LEFT OUTER JOIN
                        dbo.tblComputerSystemProduct ON dbo.tblComputers.Computername = dbo.tblComputerSystemProduct.Computername LEFT OUTER JOIN
                        dbo.tblOperatingsystem ON dbo.tblComputers.Computername = dbo.tblOperatingsystem.Computername
WHERE      (dbo.tblComputers.Domain LIKE @comp + '%') OR
                        (dbo.tblOperatingsystem.Description LIKE @comp + '%') OR
                        (dbo.tblComputers.Computername LIKE @comp + '%') OR
                        (dbo.tblComputerSystemProduct.IdentifyingNumber = @comp) OR
                        (ISNULL(dbo.tblCompCustom.BarCode, '') LIKE @comp + '%')
ORDER BY dbo.tblComputers.Computername

[heinreyn]

I figured out that the problem occurs with pc's from which the field "bar code" in custom information, is empty.
Usually we fill in the computernumber (4 digit number on every pc in the company) in this "bar code" field. So the computers from which the field hasn't been filled in, can't be searched for.

The question remains how to solve the issue...
Any suggestions?

[heinreyn]

here you go

USE [lansweeperdb]
GO
/****** Object:  StoredProcedure [dbo].[web30compsearch]    Script Date: 12/29/2009 12:53:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[web30compsearch](@comp varchar(80))
AS SELECT     tblComputers.Computername, tblOperatingsystem.Description, tblComputers.Domain, tblOperatingsystem.Caption, 
                      CAST(web30ProcessorCapacity.NrOfProcessors AS varchar) + ' * ' + CAST(web30ProcessorCapacity.MaxClockSpeed AS varchar) + ' Mhz' AS Processor, 
                      CAST(CAST(CAST(tblComputersystem.TotalPhysicalMemory AS bigint) / 1024 / 1024 AS NUMERIC) AS varchar) + ' MB' AS Memory, 
                      tblComputers.Lastseen AS [Last seen], CASE ISNULL(dbo.tblOperatingsystem.Description, 'NOT SCANNED') 
                      WHEN 'NOT SCANNED' THEN 1 ELSE 0 END AS Notscanned
FROM         tblComputers INNER JOIN
                      tblCompCustom ON tblComputers.Computername = tblCompCustom.Computername LEFT OUTER JOIN
                      tblComputersystem ON tblComputers.Computername = tblComputersystem.Computername LEFT OUTER JOIN
                      web30ProcessorCapacity ON tblComputers.Computername = web30ProcessorCapacity.Computername LEFT OUTER JOIN
                      tblComputerSystemProduct ON tblComputers.Computername = tblComputerSystemProduct.Computername LEFT OUTER JOIN
                      tblOperatingsystem ON tblComputers.Computername = tblOperatingsystem.Computername
WHERE     (tblComputers.Domain LIKE @comp + '%') OR
                      (tblOperatingsystem.Description LIKE @comp + '%') OR
                      (tblComputers.Computername LIKE @comp + '%') OR
                      (tblComputerSystemProduct.IdentifyingNumber = @comp) OR
                      (tblCompCustom.BarCode LIKE @comp + '%')
ORDER BY tblComputers.Computername

[Hemoco]

Thanks but I requested the wrong procedure.
I need : web30compsearch

[heinreyn]

In the middle of the holidays and not always at work, so a little late with the reply...

I found a stored procedure web30searchsoftware:

USE [lansweeperdb]
GO
/****** Object:  StoredProcedure [dbo].[web30searchsoftware]    Script Date: 12/29/2009 08:46:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[web30searchsoftware](@soft nvarchar(500))
AS SELECT     Software, Version, Total
FROM         dbo.web30repsoftwaretotal
WHERE     (Software LIKE @soft + '%') OR
                      (Software LIKE '%' + @soft + '%')
ORDER BY Software, Version

Is that helpful?

happy holidays!

[Hemoco]

Are you able to access the sql database.
Could you please post the stored procedure "web30softwaresearch"

[heinreyn]

I created 2 reports (see where there is no TrendMicro installed, and where there are no WSUS settings) and changed report to see what computers have no English OS to Dutch OS...