Creating report for Asset Location

[helpdesktrv]

Hello,
I would like to ask how can I create a report that shows me all the computers that have the item as Asset Location "Undefined"?
Thank you

[Hemoco · Accepted solution]

Please use the following report:

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 tblAssets.AssetID Not In (Select Top 1000000 tblAssets1.AssetID
From tblAssets Inner Join tblAssetRelations On tblAssets.AssetID =
tblAssetRelations.ParentAssetID Inner Join tblAssets tblAssets1
On tblAssetRelations.ChildAssetID = tblAssets1.AssetID Inner Join
tsysAssetRelationTypes On tblAssetRelations.Type =
tsysAssetRelationTypes.RelationTypeID
Where tsysAssetRelationTypes.Name Like '%located%')

[helpdesktrv]

I'm sorry, maybe I have not explained very well. Is not that the "Location" but I have to filter the field "Asset Location". Attached a screenshot and I put a circle on the item.
Thank you for your reply. 🙂

Click here for screenshot
http://www.imagesup.net/?di=12140368033315

[RCorbeil]

If by "undefined" you mean "blank", you can check for that easily enough.

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 tblAssetCustom.Location = ''

That will probably produce more than you're looking for, however. If you want to limit it to just your Windows machines, for instance, add another entry to the WHERE clause: either

  AND tsysAssetTypes.AssetType = -1

or

  AND tsysAssetTypes.AssetTypename = 'Windows'

You can examine the contents of tsysAssetTypes if you want to filter for more asset types.