Software Enforcement Report

[JTRU]

Hello

This is my 1st post.

Can you take a look at my report ?
I want to create a report that tells me about all workstations that run windows 7,8,10 and if it does not have a required software installed.


Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname As OS,
tblAssets.SP,
tblSoftwareUni.SoftwarePublisher,
tblSoftwareUni.softwareName,
tblAssets.Lastseen,
tblAssets.Lasttried
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tblState On tblState.State = tblAssetCustom.State
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblSoftware On tblSoftware.AssetID = tblAssets.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tsysOS.OSname In ('Win 10', 'Win 7', 'Win 8') And
tblSoftwareUni.softwareName Not Like '%Required Software %' And
tblState.Statename = 'Active'
Order By tblAssets.Domain,
tblAssets.AssetName


This report looks to be working but when I run the report it shows me duplicates of the same workstation multiple times, I got bout 65500 hits.

How can I suppress it to only show 1 device with the missing software required ?

[JacobH]

Well, don't laugh at how i picked OS criteria... but:

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tsysIPLocations.IPLocation,
tsysOS.OSname As OS,
tblOperatingsystem.Caption,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblOperatingsystem On
tblAssets.AssetID = tblOperatingsystem.AssetID
Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Where tblAssets.AssetID Not In (Select Top 1000000 tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where
tblSoftwareUni.softwareName Like
'%Your Software Here%') And tblAssetCustom.State = 1 and tsysOS.OSname not like 'Win 2%' and tsysOS.OSname not like 'Win x%'
Order By tblAssets.Domain,
tblAssets.AssetName