Find Computers without certain Software

[ricksmith2121]

I am trying to create a report that tells me what computers do not have "SolarWinds Log and Event Manager Reports" installed. The report returns the computers, but it shows multiple lines for the same computer, each line showing a different software package installed. This is what my code looks like

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblSoftwareUni.softwareName
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblAssets.AssetID Not In (Select Top 1000000 tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where
tblSoftwareUni.softwareName Like '%SolarWinds Log & Event Manager Agent%')
And tblAssetCustom.State = 1
Order By tblAssets.Domain,
tblAssets.AssetName

[NicholeKaligian]

The query you ran will generate a line for all software installed on every asset. To avoid this, try leaving out the software column in the query, making all lines for each asset identical. You could then display only one line per asset by selecting only distinct lines. This way you could also use 'Not Like' instead of 'Like', leaving out the subquery altogether.

Select Distinct Top 1000000 tsysOS.Image As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  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 tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
  Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where
  tblSoftwareUni.softwareName Not Like '%SolarWinds Log & Event Manager Agent%'
  And tblAssetCustom.State = 1
Order By tblAssets.Domain,
  tblAssets.AssetName