Asset Report Request

[TheITGuy]

I am looking for a report that I can run that gives me the following output:

Owner- whether it has one listed or not
Status- Active, broken, etc
OS Type- Linux, Windows, etc
Manufacturer
Model
Serial Number
Warranty Expiration
Asset Name
Last Seen
Domain
OU

[s_subramanian]

Thanks a lot Daniel

[Daniel_B]

That seems to be stored in tblComputersystemProduct.Version. With a case statement you could add it to this report.

Select Top 1000000 tblAssets.AssetID,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tOwner.Username As [Owned by],
  tblState.Statename As [Asset state],
  tsysAssetTypes.AssetTypename As [Asset type],
  Coalesce(tblOperatingsystem.Caption, tblLinuxSystem.OSRelease,
  tblMacOSInfo.SystemVersion, 'other/none') As OS,
  tblAssetCustom.Manufacturer,
  Case
    When tblAssetCustom.Manufacturer Like '%lenovo%' Then
    tblComputerSystemProduct.Version Else tblAssetCustom.Model End As Model,
  tblAssetCustom.Serialnumber,
  tblAssetCustom.Warrantydate As [Warranty expiration],
  tblAssets.AssetName,
  tblAssets.Lastseen,
  tblAssets.Domain,
  tblADComputers.OU
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Left Join (Select Max(tblAssetUserRelations.StartDate) As since,
    tblAssetUserRelations.AssetID
  From tblAssetUserRelations
  Where tblAssetUserRelations.Type = 1
  Group By tblAssetUserRelations.AssetID) tLastOwner On tblAssets.AssetID =
    tLastOwner.AssetID
  Left Join (Select tblAssetUserRelations.AssetID,
    tblAssetUserRelations.StartDate As since,
    tblAssetUserRelations.Username,
    tblAssetUserRelations.Userdomain
  From tblAssetUserRelations
  Where tblAssetUserRelations.Type = 1) tOwner On tLastOwner.AssetID =
    tOwner.AssetID And tLastOwner.since = tOwner.since
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Left Join tblOperatingsystem On tblOperatingsystem.AssetID = tblAssets.AssetID
  Left Join tblLinuxSystem On tblAssets.AssetID = tblLinuxSystem.AssetID
  Left Join tblMacOSInfo On tblAssets.AssetID = tblMacOSInfo.AssetID
  Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
  Left Join tblComputerSystemProduct On tblAssets.AssetID =
    tblComputerSystemProduct.AssetID
Where tblAssetCustom.State = 1

[s_subramanian]

Hi,

This is a nice report. However can you help to get the exact model name of the Lenovo Assets which is not displayed correctly from this query.

[TheITGuy]

Nice Add Dan. We currently do not keep multiple owners for devices so the original script worked for us. But now I am questioning if we should. 😉

[Daniel_B]

We recommend not deleting older asset user relations but rather building a report which lists only the last user which owns one asset. In this way you will keep the history stored in tblAssetUserRelations. The following report is a modification of the one above which lists only the last "Owned by" relation per asset:

Select Top 1000000 tblAssets.AssetID,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tOwner.Username As [Owned by],
  tblState.Statename As [Asset state],
  tsysAssetTypes.AssetTypename As [Asset type],
  Coalesce(tblOperatingsystem.Caption, tblLinuxSystem.OSRelease,
  tblMacOSInfo.SystemVersion, 'other/none') As OS,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssetCustom.Serialnumber,
  tblAssetCustom.Warrantydate As [Warranty expiration],
  tblAssets.AssetName,
  tblAssets.Lastseen,
  tblAssets.Domain,
  tblADComputers.OU
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Left Join (Select Max(tblAssetUserRelations.StartDate) As since,
    tblAssetUserRelations.AssetID
  From tblAssetUserRelations
  Where tblAssetUserRelations.Type = 1
  Group By tblAssetUserRelations.AssetID) tLastOwner On tblAssets.AssetID =
    tLastOwner.AssetID
  Left Join (Select tblAssetUserRelations.AssetID,
    tblAssetUserRelations.StartDate As since,
    tblAssetUserRelations.Username,
    tblAssetUserRelations.Userdomain
  From tblAssetUserRelations
  Where tblAssetUserRelations.Type = 1) tOwner On tLastOwner.AssetID =
    tOwner.AssetID And tLastOwner.since = tOwner.since
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Left Join tblOperatingsystem On tblOperatingsystem.AssetID = tblAssets.AssetID
  Left Join tblLinuxSystem On tblAssets.AssetID = tblLinuxSystem.AssetID
  Left Join tblMacOSInfo On tblAssets.AssetID = tblMacOSInfo.AssetID
  Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Where tblAssetCustom.State = 1

[TheITGuy]

Removing the filter at the bottom worked, Thank you so much for your assistance!!

[Daniel_B]

You can do this by removing the filter

Where tblAssetCustom.State = 1

and optionally adding tblState.Statename to your report in order to be able to see the state of listed assets.

[TheITGuy]

Daniel.B Thank You for such a quick reply and doing the scripting. The Report works great except for one thing that I cant figure out. It is only showing assets listed or reporting as "active". I know I have a few assets set to "Stock" and "Stolen" and "non-active" , etc. How can we get those to come up?

[Daniel_B · Accepted solution]

The following report lists these data for all assets in your database:

Select Top 1000000 tblAssets.AssetID,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tOwner.Username As [Owned by],
  tblState.Statename As [Asset state],
  tsysAssetTypes.AssetTypename As [Asset type],
  Coalesce(tblOperatingsystem.Caption, tblLinuxSystem.OSRelease,
  tblMacOSInfo.SystemVersion, 'other/none') As OS,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssetCustom.Serialnumber,
  tblAssetCustom.Warrantydate As [Warranty expiration],
  tblAssets.AssetName,
  tblAssets.Lastseen,
  tblAssets.Domain,
  tblADComputers.OU
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Left Join (Select tblAssetUserRelations.AssetID,
    tblAssetUserRelations.Username,
    tblAssetUserRelations.Userdomain
  From tblAssetUserRelations
  Where tblAssetUserRelations.Type = 1) tOwner On tblAssets.AssetID =
    tOwner.AssetID
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Left Join tblOperatingsystem On tblOperatingsystem.AssetID = tblAssets.AssetID
  Left Join tblLinuxSystem On tblAssets.AssetID = tblLinuxSystem.AssetID
  Left Join tblMacOSInfo On tblAssets.AssetID = tblMacOSInfo.AssetID
  Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Where tblAssetCustom.State = 1