Help! Need One report trying not to have Two

[QWERTY2022]

I have hit a wall. I am trying to create one report to show:

Report of All Server OS systems, showing if they have the Flex Net Inventory agent installed on them (or not). Client OS is excluded.

Just not sure if this can be done in one report or have to have two reports.

Thank you for any help in advance.

Mel

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblSoftwareUni.softwareName As Software,
tblSoftware.softwareVersion As Version,
tblSoftwareUni.SoftwarePublisher As Publisher,
tblAssets.Username,
tblAssets.Userdomain,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssets.SP,
tblAssets.Lastseen,
tblAssets.Lasttried
From tblComputersystem
Inner Join tblAssets On tblComputersystem.AssetID = tblAssets.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tblState On tblState.State = tblAssetCustom.State
Inner Join tblDomainroles On tblDomainroles.Domainrole =
tblComputersystem.Domainrole
Where tblAssets.AssetID Not In (Select Top 1000000 tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%FlexNetInventroyAgent 1%') And
tblAssetCustom.State = 1 And tblSoftwareUni.softwareName Like '%FlexNetInventroyAgent 2%
Where tblState.Statename = 'Active' And tblDomainroles.Domainrolename In
('Stand-alone server', 'Member server', 'Primary domain controller',
'Backup domain controller') And tblComputersystem.SystemType Like 'x64%'
Order By tblAssets.Domain,
tblAssets.AssetName

[QWERTY2022]

Thank you!

[RCorbeil]

The sub-select is pulling the software name, publisher, and version. If you're seeing nothing, make sure that the match criteria is accurate. As shown, it's looking for any software where the name contains the string "FlexNetInventroyAgent 2". If that doesn't match, for example if there should be spaces in there or if it's misspelled (Inventroy?), you'll need to adjust that.

[QWERTY2022]

Thank you for this. When running this query The software and version fields are blank. How can I update this to show Felx Net and 16.5?

Thank you again

[RCorbeil]

Try this out.

Just to be clear, you're wanting to see only FlexNetInventroyAgent 2, right? Your code suggests you don't want to see FlexNetInventroyAgent 1. If you want to see both, you can adjust the WHERE clause in the software SELECT.

Select Top 1000000
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  Software.softwareName As Software,
  Software.softwareVersion As Version,
  Software.SoftwarePublisher As Publisher,
  tblAssets.Username,
  tblAssets.Userdomain,
  Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssets.SP,
  tblAssets.Lastseen,
  tblAssets.Lasttried
From
  tblComputersystem
  Inner Join tblAssets On tblComputersystem.AssetID = tblAssets.AssetID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tsysIPLocations On tsysIPLocations.LocationID = tblAssets.LocationID
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Inner Join tblDomainroles On tblDomainroles.Domainrole = tblComputersystem.Domainrole
  LEFT JOIN (SELECT
               tblSoftware.AssetID,
               tblSoftware.SoftwareVersion,
               tblSoftwareUni.SoftwareName,
               tblSoftwareUni.SoftwarePublisher
             FROM
               tblSoftware
               INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.SoftID
             WHERE
               tblSoftwareUni.SoftwareName LIKE '%FlexNetInventroyAgent 2%') AS Software ON Software.AssetID = tblAssets.AssetID
Where
  tblAssetCustom.State = 1
  --AND tblState.Statename = 'Active'  -- redundant
  And tblDomainroles.Domainrolename In ('Stand-alone server', 'Member server', 'Primary domain controller', 'Backup domain controller')
  And tblComputersystem.SystemType Like 'x64%'
Order By
  tblAssets.Domain,
  tblAssets.AssetName

The software sub-select limits the list of software to only what you want to see. Using a left join ensures that even servers without the software will be included in the results.