Software Change report filtering

[eriqjaffe]

Greetings!

In our environment, our workstations audit (via lspush.exe) during the login script, as well as through a scheduled task at midday. The login script scan runs under the user's context, but the midday scan runs under a dedicated user's context, which is the same as the Global Windows Credential defined in Lansweeper.

This is causing some slight inaccuracies with the Software Change reports, as software that installs under a user's context will seem to be installed and uninstalled repeatedly. Google Chrome is an example of this - when scanned via the login script, it creates an "installed" record, but when the midday audit runs (or when the workstation is rescanned from the Lansweeper console), it creates a "removed" record.

What it boils down to is that I'm trying to find a way to modify the report so that it ignores software that shows an install and subsequent removal on the same day, as long as the title and version matches.

Any assistance on this would be appreciated. Thanks!

[eriqjaffe]

That looks like it got it - thanks!

[Hemoco · Accepted solution]

Try:

Select Distinct Top (1000000) tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
Case tblSoftwareHist.Action When '1' Then '+' When '2' Then '-' End As [+/-],
SubQuery.softwareName,
SubQuery.SoftwarePublisher,
SubQuery.softwareVersion,
tblSoftwareHist.Lastchanged
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblSoftwareHist On tblSoftwareHist.AssetID = tblAssets.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftwareHist.softid
Inner Join (Select Top (1000000) Count(tblSoftwareHist_1.softid) As Count,
tblSoftwareHist_1.AssetID,
tblSoftwareUni_1.softwareName,
tblSoftwareHist_1.softwareVersion,
tblSoftwareUni_1.SoftwarePublisher,
Convert(nvarchar,tblSoftwareHist_1.Lastchanged,101) As Changed
From tblSoftwareHist As tblSoftwareHist_1
Inner Join tblSoftwareUni As tblSoftwareUni_1 On tblSoftwareUni_1.SoftID =
tblSoftwareHist_1.softid
Group By tblSoftwareHist_1.AssetID,
tblSoftwareUni_1.softwareName,
tblSoftwareHist_1.softwareVersion,
tblSoftwareUni_1.SoftwarePublisher,
Convert(nvarchar,tblSoftwareHist_1.Lastchanged,101)
Having Count(tblSoftwareHist_1.softid) = 1) As SubQuery
On SubQuery.softwareName = tblSoftwareUni.softwareName And
SubQuery.SoftwarePublisher = tblSoftwareUni.SoftwarePublisher And
SubQuery.softwareVersion = tblSoftwareHist.softwareVersion And
SubQuery.AssetID = tblSoftwareHist.AssetID
Where tblSoftwareHist.Lastchanged > GetDate() - 1 And tblAssetCustom.State = 1
Order By tblAssets.Domain,
tblAssets.AssetName,
tblSoftwareHist.Lastchanged Desc

[eriqjaffe]

That's really close, but I'm getting some duplicate records with that query. I've attached a screenshot so you can see what I'm seeing.

[Hemoco]

Anyone looking for a sample report:

Select Top (1000000) tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
Case tblSoftwareHist.Action When '1' Then '+' When '2' Then '-' End As [+/-],
SubQuery.softwareName,
SubQuery.SoftwarePublisher,
SubQuery.softwareVersion,
tblSoftwareHist.Lastchanged
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblSoftwareHist On tblSoftwareHist.AssetID = tblAssets.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftwareHist.softid
Inner Join (Select Top (1000000) Count(tblSoftwareHist_1.softid) As Count,
tblSoftwareHist_1.AssetID,
tblSoftwareUni_1.softwareName,
tblSoftwareHist_1.softwareVersion,
tblSoftwareUni_1.SoftwarePublisher,
Convert(nvarchar,tblSoftwareHist_1.Lastchanged,101) As Changed
From tblSoftwareHist As tblSoftwareHist_1
Inner Join tblSoftwareUni As tblSoftwareUni_1 On tblSoftwareUni_1.SoftID =
tblSoftwareHist_1.softid
Group By tblSoftwareHist_1.AssetID,
tblSoftwareUni_1.softwareName,
tblSoftwareHist_1.softwareVersion,
tblSoftwareUni_1.SoftwarePublisher,
Convert(nvarchar,tblSoftwareHist_1.Lastchanged,101)
Having Count(tblSoftwareHist_1.softid) = 1) As SubQuery
On SubQuery.softwareName = tblSoftwareUni.softwareName And
SubQuery.SoftwarePublisher = tblSoftwareUni.SoftwarePublisher And
SubQuery.softwareVersion = tblSoftwareHist.softwareVersion And
SubQuery.AssetID = tblSoftwareHist.AssetID
Where tblSoftwareHist.Lastchanged > GetDate() - 1 And tblAssetCustom.State = 1
Order By tblAssets.Domain,
tblAssets.AssetName,
tblSoftwareHist.Lastchanged Desc