cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
bawoodru
Engaged Sweeper
I'm trying to create a report that displays the servers that DO NOT have a specific software installed. This is what I have created, But it doesn't correctly filter out the records that DO have the software installed and just displays all the servers in the environment whether they have the software package installed or not.

Select Distinct Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Userdomain,
tblAssets.IPAddress,
tsysOS.OSname,
tblAssets.Description,
tblAssetCustom.State
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 (tsysOS.OSname Like '%Win 2012%' Or tsysOS.OSname Like '%Win 2016%' Or
tsysOS.OSname Like '%Win 2008%' Or tsysOS.OSname Like '%Win 2000 S%' Or
tsysOS.OSname Like '%Win 2019%') And tblAssetCustom.State = 1 And
tblSoftwareUni.softwareName Not Like 'SoftwarePackageToLookFor'
Order By tblAssets.Domain,
tblAssets.AssetName
5 REPLIES 5
RCorbeil
Honored Sweeper II
The way you're currently checking for Windows servers involves a moving target: you have to manually manage your OS list when a new version is released. You might consider checking for asset type of Windows and domain role of server instead.
Select Top 1000000
tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Userdomain,
tblAssets.IPAddress,
tsysOS.OSname,
tblAssets.Description,
tblAssetCustom.State
From
tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
INNER JOIN tblComputerSystem ON tblComputerSystem.AssetID = tblAssets.AssetID
Where
tblAssets.AssetType = -1 -- Windows
AND tblComputerSystem.DomainRole > 1 -- server
And tblAssetCustom.State = 1
AND tblAssets.AssetID Not In (Select Top 1000000
tblSoftware.AssetID
From
tblSoftware
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where
tblSoftwareUni.softwareName Like '%Software not installed%')
Order By
tblAssets.Domain,
tblAssets.AssetName
bawoodru
Engaged Sweeper
You sir are a god.. Thank you for the example/answer and explanation
RCorbeil
Honored Sweeper II
Start with the query selecting all the relevant servers.
Select Top 1000000
tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Userdomain,
tblAssets.IPAddress,
tsysOS.OSname,
tblAssets.Description,
tblAssetCustom.State
From
tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where
(tsysOS.OSname Like '%Win 2012%'
Or tsysOS.OSname Like '%Win 2016%'
Or tsysOS.OSname Like '%Win 2008%'
Or tsysOS.OSname Like '%Win 2000 S%'
Or tsysOS.OSname Like '%Win 2019%')
And tblAssetCustom.State = 1
Order By
tblAssets.Domain,
tblAssets.AssetName

Confirm that works, then add one more condition to the WHERE clause:
Where
(tsysOS.OSname Like '%Win 2012%'
Or tsysOS.OSname Like '%Win 2016%'
Or tsysOS.OSname Like '%Win 2008%'
Or tsysOS.OSname Like '%Win 2000 S%'
Or tsysOS.OSname Like '%Win 2019%')
And tblAssetCustom.State = 1
AND tblAssets.AssetID Not In (Select Top 1000000
tblSoftware.AssetID
From
tblSoftware
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where
tblSoftwareUni.softwareName Like '%Software not installed%')
bawoodru
Engaged Sweeper
I have no problem getting it to report all machines with the software not installed, or having it report all the machines with an OS that has a name of:

'%Win 2012%'
'%Win 2008%'
'%Win 2000 S%'
'%Win 2019%'
'%Win 2016%'

My issue is getting these two reports combined to get a list of machines matching both sets of criteria. I know it's a syntax issue... Just cant find it difficult to locate how it should be.
RCorbeil
Honored Sweeper II