→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
MD2Tech
Engaged Sweeper II
So I found this piece of code in the Report Center... but I wanted to know how it could be modified to report not only on the missing software but also on software installed? So I need it to report Assets that have SessionWorks Judge Edition installed but not Microsoft SQL Server 2014 (x64) or vise versa.

What I'm trying to determine are assets that could possibly be out of date with the latest SQL software because SessionWorks requires that SQL server be installed and I want to know which Assets have older versions of SQL.

Missing Software Code
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
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tblAssets.AssetID Not In (Select Top 1000000 tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%YourSoftware%') And
tblAssetCustom.State = 1
Order By tblAssets.Domain,
tblAssets.AssetName
1 ACCEPTED SOLUTION
Bruce_B
Lansweeper Alumni
You can use the report below to list assets that don't have software 1 but do have software 2. You can change the highlighted text to fit your requirements.

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
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 '%Software 1%') And
tblAssetCustom.State = 1 And tblSoftwareUni.softwareName Like '%Software 2%'
Order By tblAssets.Domain,
tblAssets.AssetName

View solution in original post

2 REPLIES 2
MD2Tech
Engaged Sweeper II
Awesome it works! Thanks!
Bruce_B
Lansweeper Alumni
You can use the report below to list assets that don't have software 1 but do have software 2. You can change the highlighted text to fit your requirements.

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
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 '%Software 1%') And
tblAssetCustom.State = 1 And tblSoftwareUni.softwareName Like '%Software 2%'
Order By tblAssets.Domain,
tblAssets.AssetName