cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
ricksmith2121
Engaged Sweeper
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
1 REPLY 1
NicholeKaligian
Lansweeper Employee
Lansweeper Employee
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