Report showing Asset Locations

[Champ14]

Hi All,

I am trying to show the Asset Location using the script below but nothings shows on the location when I generate the report..All information are showing except for the Asset Location. I already created locations for each assets, that's why im not sure why it is showing empty details in the report.

Thanks!


Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.IPAddress,
tblAssetCustom.Location,
tsysOS.OSname,
tsysIPLocations.IPLocation,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssetCustom.Location As Location1
From tblAssets
Left Outer Join tblOperatingsystem On tblAssets.AssetID =
tblOperatingsystem.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Left Join tsysIPLocations On tsysIPLocations.StartIP <= tblAssets.IPNumeric
And tsysIPLocations.EndIP >= tblAssets.IPNumeric
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype,
tsysOS
Where tsysOS.OSname Like '%Not scanned%' And tblOperatingsystem.AssetID Is Null
And tblAssetCustom.State = 1 And tblAssets.Assettype = -1
Order By tblAssets.AssetName,
Cast(tblAssets.Lastseen - tblAssets.Firstseen As bigint) Desc

[RCorbeil]

If you're definitely filling in the asset location value, consider the possibility that something in your query is filtering out the results you want to see. Start from scratch.

First, make sure the locations exist.

Select Top 1000000
  tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetType,
  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
  tsysAssetTypes.AssetTypename = 'Location'
  And tblAssetCustom.State = 1

That should produce a list of the locations you've defined and identify them as asset type 66. If the list is empty, you have no asset locations defined.

Once you've confirmed that the asset locations are in the database, link them to the base query.

Select Top 1000000
  tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  al.AssetName As AssetLocation
From
  tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Left Join tblAssetRelations As ar On ar.ChildAssetID = tblAssets.AssetID
  Left Join tblAssets As al On ar.ParentAssetID = al.AssetID And al.Assettype = 66
Where
  tblAssetCustom.State = 1

If the asset location is correctly reported, build on that. If the asset location doesn't show up, maybe you're not recording it in the field you think you are.

[RCorbeil]

There are multiple "location" fields in the database. Since you're not seeing what I am, we're probably referring to different fields.

If you're using the Edit Asset option to fill in a location (Purchased, Warranty, Contact, Location, Building, etc.), that value is stored in tblAssetCustom.Location. That one's easy to pull; you already had that in your query.

The location I'm referring to is the one that you can pull a list of if you pull down the Assets menu and, under Asset Types, you choose Location. That location allows you to assign maps and place your assets on the maps. (Very handy, assuming people maintain the information.)

Assuming the latter, if you view the summary page on one of your computers, does the asset location show up in the second column (Scan status, Scan server, State, IP Location, Asset location, Serial, etc.)? If you've associated the asset with a location from the list above, it should show up there. If it shows "Undefined", there is no linked location. If that's the case, use the Location menu to add the computer to one of your defined locations.

If that's not what you're referring to as the location, the query additions I offered won't do anything for you.

If you're working with yet another location field, can you describe where that is, where you're filling that in?

[RCorbeil]

Depending on your needs, an INNER JOIN may be all that's required against tsysOS, but I made it a LEFT JOIN just to be sure nothing got filtered out that way.

Select Top 1000000
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.IPAddress,
  tblAssetCustom.Location,
  tsysOS.OSname,
  tsysIPLocations.IPLocation,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  --tblAssetCustom.Location As Location1,
  al.AssetName AS AssetLocation 
From
  tblAssets
  Left Outer Join tblOperatingsystem On tblAssets.AssetID = tblOperatingsystem.AssetID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Left Join tsysIPLocations On tsysIPLocations.StartIP <= tblAssets.IPNumeric And tsysIPLocations.EndIP >= tblAssets.IPNumeric
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  LEFT JOIN tsysOS On tblAssets.OScode = tsysOS.OScode
  LEFT JOIN tblAssetRelations AS ar ON ar.ChildAssetID=tblAssets.AssetID
  LEFT JOIN tblAssets AS al ON ar.ParentAssetID=al.AssetID AND al.Assettype=66
Where
  tsysOS.OSname Like '%Not scanned%'
  And tblOperatingsystem.AssetID Is Null
  And tblAssetCustom.State = 1
  And tblAssets.Assettype = -1
Order By
  tblAssets.AssetName,
  Cast(tblAssets.Lastseen - tblAssets.Firstseen As bigint) Desc

As a test on my side, I removed the first two conditions from the WHERE clause to get results from my inventory. The asset location was part of the results, as expected.

For sorting, if all you're wanting is the number of days between first and last seen, you might consider using DateDiff(d, tblAssets.Firstseen, tblAssets.Lastseen).

[Champ14]

RC62N wrote:
Depending on your needs, an INNER JOIN may be all that's required against tsysOS, but I made it a LEFT JOIN just to be sure nothing got filtered out that way.

Select Top 1000000
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.IPAddress,
  tblAssetCustom.Location,
  tsysOS.OSname,
  tsysIPLocations.IPLocation,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  --tblAssetCustom.Location As Location1,
  al.AssetName AS AssetLocation 
From
  tblAssets
  Left Outer Join tblOperatingsystem On tblAssets.AssetID = tblOperatingsystem.AssetID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Left Join tsysIPLocations On tsysIPLocations.StartIP <= tblAssets.IPNumeric And tsysIPLocations.EndIP >= tblAssets.IPNumeric
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  LEFT JOIN tsysOS On tblAssets.OScode = tsysOS.OScode
  LEFT JOIN tblAssetRelations AS ar ON ar.ChildAssetID=tblAssets.AssetID
  LEFT JOIN tblAssets AS al ON ar.ParentAssetID=al.AssetID AND al.Assettype=66
Where
  tsysOS.OSname Like '%Not scanned%'
  And tblOperatingsystem.AssetID Is Null
  And tblAssetCustom.State = 1
  And tblAssets.Assettype = -1
Order By
  tblAssets.AssetName,
  Cast(tblAssets.Lastseen - tblAssets.Firstseen As bigint) Desc

As a test on my side, I removed the first two conditions from the WHERE clause to get results from my inventory. The asset location was part of the results, as expected.

For sorting, if all you're wanting is the number of days between first and last seen, you might consider using DateDiff(d, tblAssets.Firstseen, tblAssets.Lastseen).

Hi @RC62N,

Thanks for your help, I really appreciate it specially your time giving to this, i really feel stupid now, I dont know where I am doing it wrong. I've tried to copy your script to a new report and run it but still the Location field shows empty but it shows the rest of the columns info. I already indicated the assets locations so it is impossible that it didnt get any details for it.

Thanks

[Champ14]

Hi,

Thanks for your response, I'm really new to this, and really noob in SQL, can you paste the code exactly on where I should put it in from my code? I've tried inserting it but im getting errors.

Thanks you

[RCorbeil]

I found the asset location by linking against

  LEFT JOIN tblAssetRelations AS ar ON ar.ChildAssetID=tblAssets.AssetID
  LEFT JOIN tblAssets AS al ON ar.ParentAssetID=al.AssetID AND al.Assettype=66

and adding

al.AssetName AS AssetLocation

to my selected field list.

By the way, you may want to define the link against tsysOS so that it's tied to tblAssets.