Asset Report Request

TheITGuy · ‎04-23-2015

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

Daniel_B · ‎04-24-2015

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

View solution in original post

dmitry · ‎05-26-2015

Daniel.B wrote:
The following report lists these data for all assets in your database:

Hi Daniel!

It looks like the report doesn't consider ownership changes.

If
User1 owned Asset1 starting from Jan 01, 2015 and then
User2 owned Asset1 starting from May 01, 2015

The report shows 2 entries for Asset1.

The solution is to delete ownership record (1).
But any idea how to keep the ownership history?

Asset Report Request

New to Lansweeper?

Try Lansweeper For Free