→ Celebrate SysAdmin Day 2024 with Lansweeper Enter our Giveaway here

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

I have a report that I've built that pulls out PC's with windows XP and only approved software. I have some machines with Novell and local login account so need to put a custom registry entry from scanned info. At the moment the report pulls out a number of entries per asset depending if it has numerous entries in the tblRegistry. The ultimate report would be all XP machines with only approved software and then to list the NWUSERNAME and value if the asset has one. Here's the report so far, any help appreciated:

Select Distinct Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tsysAssetTypes.AssetTypename As Type,
tblAssets.IPAddress,
tblAssets.Description,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Location,
tsysIPLocations.IPLocation,
tblAssets.Firstseen,
tblAssets.Lastseen,
tsysAssetTypes.AssetTypeIcon10 As icon,
tsysOS.OSname,
tsysOS.OScode,
tblRegistry.Value,
tblRegistry.Valuename
From tblSoftware
Inner Join tblAssets On tblSoftware.AssetID = tblAssets.AssetID
Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Join tsysIPLocations On tblAssets.LocationID = tsysIPLocations.LocationID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join tblRegistry On tblAssets.AssetID = tblRegistry.AssetID
Where tblAssets.AssetID Not In (Select Top 1000000 tblAssets.AssetID
From tblSoftware Inner Join tblAssets On tblSoftware.AssetID =
tblAssets.AssetID Inner Join tblSoftwareUni On tblSoftware.softID =
tblSoftwareUni.SoftID
Where tblSoftwareUni.Approved = 2) And tsysOS.OSname = 'Win XP' And
tblSoftwareUni.Approved = 1 And tblAssetCustom.State = 1
Order By tblAssets.Domain,
tblAssets.AssetName
0 REPLIES 0