cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
CTB
Engaged Sweeper
How do I build a query (which tables do I need) to find machines with indentical harddrives?
1 ACCEPTED SOLUTION
Hemoco
Lansweeper Alumni
A sample report that lists computers with a "shared" disk model can be seen below.
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblFloppy.Name As DiskName,
tblFloppy.Model As DiskModel
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblFloppy On tblAssets.AssetID = tblFloppy.AssetID
Inner Join (Select Top 1000000 Count(tblFloppy.AssetID) As Count,
tblFloppy.Model
From tblAssetCustom
Inner Join tblFloppy On tblFloppy.AssetID = tblAssetCustom.AssetID
Where tblAssetCustom.State = 1
Group By tblFloppy.Model
Having Count(tblFloppy.AssetID) > 1) SubQuery1 On SubQuery1.Model =
tblFloppy.Model
Where tblAssetCustom.State = 1
Order By DiskModel,
tblAssets.Domain,
tblAssets.AssetName

View solution in original post

4 REPLIES 4
CTB
Engaged Sweeper
This is what I needed.
Thank you very much!
Hemoco
Lansweeper Alumni
A sample report that lists computers with a "shared" disk model can be seen below.
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblFloppy.Name As DiskName,
tblFloppy.Model As DiskModel
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblFloppy On tblAssets.AssetID = tblFloppy.AssetID
Inner Join (Select Top 1000000 Count(tblFloppy.AssetID) As Count,
tblFloppy.Model
From tblAssetCustom
Inner Join tblFloppy On tblFloppy.AssetID = tblAssetCustom.AssetID
Where tblAssetCustom.State = 1
Group By tblFloppy.Model
Having Count(tblFloppy.AssetID) > 1) SubQuery1 On SubQuery1.Model =
tblFloppy.Model
Where tblAssetCustom.State = 1
Order By DiskModel,
tblAssets.Domain,
tblAssets.AssetName
CTB
Engaged Sweeper
Yes exactly.

Whern I select an Assets (in Lansweeper) and click on config -> disk -> diskdrive
I see a model description. I want a list of machines with an idential disk(model)
Hemoco
Lansweeper Alumni
Could you clarify what you mean by "identical harddrives". Do you mean you would like to list machines that share the same hard drive model?