Software reports missing clients

[Anonymous]

After upgrade I found that many software installations are less than they where.
For instance I had Freemind installed in 670 computers, I've created a manual query on the database and I get 670 installations.
If I search for Freemind on the search bar I only get 577. This number is around the number of "active computers" but still is installed on the "non-active" computers. Shouldn't this report show all installed software?

[Anonymous]

I will document here for now all the changes regarding this subject.

web40getnonactive from:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[web40getnonactive]
AS SELECT DISTINCT 
                        dbo.Web40OSName.Compimage AS Icon, dbo.tblComputers.ComputerUnique, dbo.tblComputers.Computername, dbo.tblComputers.Domain, 
                        dbo.web40allcompstates.Statename AS [State Name], ISNULL(dbo.tblOperatingsystem.Description, '') AS Description, 
                        dbo.tblComputers.LastknownIP AS IP, dbo.tblComputers.Lastseen
FROM          dbo.tblComputers INNER JOIN
                        dbo.web40allcompstates ON dbo.tblComputers.Computername = dbo.web40allcompstates.Computername INNER JOIN
                        dbo.Web40OSName ON dbo.tblComputers.Computername = dbo.Web40OSName.Computername LEFT OUTER JOIN
                        dbo.tblOperatingsystem ON dbo.tblComputers.Computername = dbo.tblOperatingsystem.Computername
WHERE      (dbo.web40allcompstates.Thestate > 1)
ORDER BY dbo.tblComputers.ComputerUnique

to:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[web40getnonactive]
AS SELECT DISTINCT 
                        dbo.Web40OSName.Compimage AS Icon, dbo.tblComputers.ComputerUnique, dbo.tblComputers.Computername, dbo.tblComputers.Domain, 
                        dbo.web40allcompstates.Statename AS [State Name], ISNULL(dbo.tblOperatingsystem.Description, '') AS Description, 
                        dbo.tblComputers.LastknownIP AS IP, dbo.tblComputers.Lastseen
FROM          dbo.tblComputers INNER JOIN
                        dbo.web40allcompstates ON dbo.tblComputers.Computername = dbo.web40allcompstates.Computername INNER JOIN
                        dbo.Web40OSName ON dbo.tblComputers.Computername = dbo.Web40OSName.Computername LEFT OUTER JOIN
                        dbo.tblOperatingsystem ON dbo.tblComputers.Computername = dbo.tblOperatingsystem.Computername
WHERE      (dbo.web40allcompstates.Thestate = 2)
ORDER BY dbo.tblComputers.ComputerUnique

Web40totalnonactive from:

SELECT     TOP (1000000) dbo.tblComputers.Domain, COUNT(dbo.tblComputers.Computername) AS cnt
FROM         dbo.tblComputers INNER JOIN
                      dbo.web40allcompstates ON dbo.tblComputers.Computername = dbo.web40allcompstates.Computername
WHERE     (dbo.web40allcompstates.Thestate <> 1)
GROUP BY dbo.tblComputers.Domain

to:

SELECT     TOP (1000000) dbo.tblComputers.Domain, COUNT(dbo.tblComputers.Computername) AS cnt
FROM         dbo.tblComputers INNER JOIN
                      dbo.web40allcompstates ON dbo.tblComputers.Computername = dbo.web40allcompstates.Computername
WHERE     (dbo.web40allcompstates.Thestate = 2)
GROUP BY dbo.tblComputers.Domain

web40totalClients this should also be changed but the group by State should be changed too.

[Anonymous]

I know I can change reports, but I know if I do it I will have break downs with major changes like from LS3.x to LS4.
Maybe I wasn't very clear. I will try to explain better:
I think this logic is wrong in excluding the computers that are not "active" assuming them as "non-active". I have other custom states for computers with lots of software licenses installed, and they aren't taken in count, since they are not in "active" status. How do I say our license manager that I have 749 computers with installed software and only 577 licenses being counted, with 172 installed but not counting at all?

I think this can affect more people, and in my opinion the logic is not the best.

I would change this view from web40ActiveComputers

SELECT     dbo.tblComputers.Computername
FROM         dbo.tblComputers LEFT OUTER JOIN
                      dbo.tblCompCustom ON dbo.tblComputers.Computername = dbo.tblCompCustom.Computername
WHERE     (dbo.tblCompCustom.Computername IS NULL) OR
                      (dbo.tblCompCustom.State = 1)

To

SELECT     dbo.tblComputers.Computername
FROM         dbo.tblComputers LEFT OUTER JOIN
                      dbo.tblCompCustom ON dbo.tblComputers.Computername = dbo.tblCompCustom.Computername
WHERE     (dbo.tblCompCustom.Computername IS NULL) OR
                      (dbo.tblCompCustom.State <> 2)

Did I make my point of view clear now? Thanks

[Hemoco]

You are free to change all reports the way you want.

[Anonymous]

For me the Active and non-active status is not exact enough to get into consideration. I would prefer to show all the computers with that software (where it is really installed).

[Hemoco]

All standard reports are linked to active computers.

[Anonymous]

I saw the stored procedure is linked with the Active Computers, this is wrong in my opinion for software reports.

ALTER PROCEDURE [dbo].[Web40FindsoftwareName](@soft nvarchar(400))
AS SELECT DISTINCT
dbo.tblComputers.ComputerUnique, dbo.tblComputers.Domain, dbo.Web40OSName.Compimage AS Icon, dbo.tblComputers.Computername,
ISNULL(dbo.tblComputers.Description, N'') AS Description, dbo.tblSoftware.softwareName AS Software, dbo.tblSoftware.softwareVersion AS Version,
dbo.tblComputers.Lastseen
FROM dbo.tblComputers INNER JOIN
dbo.tblSoftware ON dbo.tblComputers.Computername = dbo.tblSoftware.ComputerName INNER JOIN
dbo.web40ActiveComputers ON dbo.tblComputers.Computername = dbo.web40ActiveComputers.Computername INNER JOIN
dbo.Web40OSName ON dbo.tblComputers.Computername = dbo.Web40OSName.Computername LEFT OUTER JOIN
dbo.tblComputersystem ON dbo.tblComputers.Computername = dbo.tblComputersystem.Computername
WHERE (dbo.tblSoftware.softwareName = @soft)
ORDER BY dbo.tblComputers.ComputerUnique

This way I get ALL installed software:

ALTER PROCEDURE [dbo].[Web40FindsoftwareName](@soft nvarchar(400))
AS SELECT DISTINCT
tblComputers.ComputerUnique, tblComputers.Domain, Web40OSName.Compimage AS Icon, tblComputers.Computername,
ISNULL(tblComputers.Description, N'') AS Description, tblSoftware.softwareName AS Software, tblSoftware.softwareVersion AS Version,
tblComputers.Lastseen
FROM tblComputers INNER JOIN
tblSoftware ON tblComputers.Computername = tblSoftware.ComputerName INNER JOIN
Web40OSName ON tblComputers.Computername = Web40OSName.Computername LEFT OUTER JOIN
tblComputersystem ON tblComputers.Computername = tblComputersystem.Computername
WHERE (tblSoftware.softwareName = @soft)
ORDER BY tblComputers.ComputerUnique