→ 🚀What's New? Join Us for the Fall Product Launch! Register Now !

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
QWERTY2022
Engaged Sweeper
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
4 REPLIES 4
QWERTY2022
Engaged Sweeper
Thank you!
RCorbeil
Honored Sweeper II
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
Engaged Sweeper
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
Honored Sweeper II
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.