cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
mikesecond
Engaged Sweeper II
I want to build a report that shows me all PCs missing a specific software - similar to no anti-virus installed report.

How do I create a custom table or view that would allow me to define the software title to be searched for?

1 ACCEPTED SOLUTION
RCorbeil
Honored Sweeper II
Try this as a starting point:
SELECT Top 1000000
tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried
FROM
tblAssets
INNER JOIN tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
INNER JOIN tsysAssetTypes ON tsysAssetTypes.AssetType = tblAssets.Assettype
WHERE
tblAssetCustom.State = 1
AND tblAssets.Assettype = -1
AND tblAssets.AssetID NOT IN ( SELECT
tblSoftware.AssetID
FROM
tblSoftware
INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.softID
WHERE
tblSoftwareUni.softwareName LIKE '%your_software_title%'
)

View solution in original post

2 REPLIES 2
mikesecond
Engaged Sweeper II
Perfect, thank you
RCorbeil
Honored Sweeper II
Try this as a starting point:
SELECT Top 1000000
tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried
FROM
tblAssets
INNER JOIN tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
INNER JOIN tsysAssetTypes ON tsysAssetTypes.AssetType = tblAssets.Assettype
WHERE
tblAssetCustom.State = 1
AND tblAssets.Assettype = -1
AND tblAssets.AssetID NOT IN ( SELECT
tblSoftware.AssetID
FROM
tblSoftware
INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.softID
WHERE
tblSoftwareUni.softwareName LIKE '%your_software_title%'
)