monitors connected to PCs on 2 subnets / 1 IP location

[jozin_cz]

Hey guys,

I know very very little about SQL.
Could someone help me to get together a report which would give me
all LCDs having an Asset relation "Connected To" to all computers in one of our IP Locations pls?
BTW:
1) we have two subnets there 172.20.102.* and 172.20.103.*
2) most of our HW turned into State: Non-active for some reason, I need those too pls.
Thank you very much!!

[Susan_A · Accepted solution]

Automatic asset state changes are caused by the database cleanup options found under Configuration\Server Options. For more information on how these options work, please review this knowledge base article.

The sample report below lists all monitors connected to computers in IP location "local subnet". You can insert your own location into the query. We're not sure how you intend on identifying LCD monitors specifically, but you could try filtering the monitor names within the report results. Instructions for adding the report to your installation can be found here. If you are interested in building or modifying reports, we would recommend:

• Reviewing some SQL tutorials, as the Lansweeper report builder is a standard SQL editor. If you know SQL, you know how to build Lansweeper reports as well. This seems like a good tutorial.
  
• Making use of our database dictionary, more information on which can be found here. The dictionary explains in great detail what each table and field stores.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tsysAssetRelationTypes.Name As Relation,
  tblAssets1.AssetName As Monitor,
  tblAssetRelations.StartDate As Date,
  tblAssetRelations.Comments
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tsysIPLocations On tsysIPLocations.StartIP <= tblAssets.IPNumeric
    And tsysIPLocations.EndIP >= tblAssets.IPNumeric
  Inner Join tblAssetRelations On tblAssetRelations.ParentAssetID =
    tblAssets.AssetID
  Inner Join tsysAssetRelationTypes On tsysAssetRelationTypes.RelationTypeID =
    tblAssetRelations.Type
  Inner Join tblAssets tblAssets1 On tblAssets1.AssetID =
    tblAssetRelations.ChildAssetID
  Inner Join tsysAssetTypes tsysAssetTypes1 On tsysAssetTypes1.AssetType =
    tblAssets1.Assettype
Where tsysIPLocations.IPLocation = 'local subnet' And
  tsysAssetRelationTypes.Name = 'connected to' And tsysAssetTypes1.AssetTypename
  = 'monitor'
Order By tblAssets.Domain,
  tblAssets.AssetName