cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
dgomez
Engaged Sweeper
Hi,

I need help with a report that will filter PCs that are missing certain software that are nor servers e.g. Win 2008, Win 2012, Win 2012R2 etc. So far I've been able to get all the machines that are missing the software but I cant figure out how to exclude servers. Please help! Thank you! This is what I've got so far:

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 '%Citrix Receiver%'
)
2 REPLIES 2
ddarlage
Engaged Sweeper II
This is what I am using it seems to be working very well.


Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.IPAddress,
tblAssets.Domain,
tblAssetCustom.Manufacturer,
tblAssetCustom.Location,
tsysIPLocations.IPLocation,
tsysOS.OSname As OS,
tblAssets.Lastseen
From tblAssets
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblDomainroles On tblDomainroles.Domainrole =
tblComputersystem.Domainrole
Left Join tsysIPLocations On tsysIPLocations.StartIP <= tblAssets.IPNumeric
And tsysIPLocations.EndIP >= tblAssets.IPNumeric
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where tblAssets.AssetID Not In (Select Top 1000000 tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftwareUni.softwareName Like 'YOURSOFTWARE HERE') And
tblComputersystem.Domainrole <= 1 And tblAssetCustom.State = 1
Order By tblAssets.AssetName
dgomez
Engaged Sweeper
I figured it out I think, by adding

tblComputersystem1.Domainrole = 3

where 3 = Servers.

But any more input would be appreciated!


Thanks