Report on servers only without software

[bawoodru]

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

[RCorbeil]

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]

You sir are a god.. Thank you for the example/answer and explanation

[RCorbeil]

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]

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]

See this thread.