→ Celebrate SysAdmin Day 2024 with Lansweeper Enter our Giveaway here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
johanpe
Engaged Sweeper
Dear

We have the following report created.
From this report, we would only want to see the assets where the values of Subquery.Value has duplicates.
I tried this with a method with declaring 'Dup' but can't seem to get it working.

Can you please advise?

Thx in advance
Johan


Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
SubQuery.Value As BatchParameters,
SubQuery1.Value As StationType,
SubQuery2.Value As InstallRoot,
SubQuery3.Value As InstallDate,
tblFileVersions.FileVersion As [Batch Version],
tblAssets.IPAddress,
tblSoftwareUni.softwareName As [.NET version],
tsysOS.OSname,
tblAssets.SP,
tblAssets.Lastseen
From tblAssets
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Left Join (Select tblRegistry.AssetID,
tblRegistry.Regkey,
tblRegistry.Valuename,
tblRegistry.Value,
tblRegistry.Lastchanged
From tblRegistry
Where tblRegistry.Valuename = 'BatchParameters') SubQuery
On SubQuery.AssetID = tblAssets.AssetID
Left Join (Select tblRegistry.AssetID,
tblRegistry.Regkey,
tblRegistry.Valuename,
tblRegistry.Value,
tblRegistry.Lastchanged
From tblRegistry
Where tblRegistry.Valuename = 'StationType') SubQuery1 On SubQuery1.AssetID =
tblAssets.AssetID
Left Join (Select tblRegistry.AssetID,
tblRegistry.Regkey,
tblRegistry.Valuename,
tblRegistry.Value,
tblRegistry.Lastchanged
From tblRegistry
Where tblRegistry.Valuename = 'InstallRoot') SubQuery2 On SubQuery2.AssetID =
tblAssets.AssetID
Left Join (Select tblRegistry.AssetID,
tblRegistry.Regkey,
tblRegistry.Valuename,
tblRegistry.Value,
tblRegistry.Lastchanged
From tblRegistry
Where tblRegistry.Valuename = 'InstallDate') SubQuery3 On SubQuery3.AssetID =
tblAssets.AssetID
Inner Join tblFileVersions On tblAssets.AssetID = tblFileVersions.AssetID
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%.net%' And
tblFileVersions.FilePathfull Like 'c:\crews\exe\batch.exe'
Order By tblAssets.AssetName
0 REPLIES 0