cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Champ14
Engaged Sweeper II
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
6 REPLIES 6
RCorbeil
Honored Sweeper II
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
Honored Sweeper II
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
Honored Sweeper II
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
Engaged Sweeper II
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
Engaged Sweeper II
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
Honored Sweeper II
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.