This website uses cookies. By clicking Accept, you consent to the use of cookies. Click Here to learn more about how we use cookies.
Accept
Reject
Community FAQ
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Show  only  | Search instead for 
Did you mean: 

Asset Report Request

Go to solution
TheITGuy
Engaged Sweeper III

‎04-23-2015 11:14 PM

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




Labels:
1 ACCEPTED SOLUTION
Go to solution
Daniel_B
Lansweeper Alumni

‎04-24-2015 01:24 PM

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

10 REPLIES 10
Go to solution
dmitry
Engaged Sweeper
In response to Daniel_B

‎05-26-2015 10:58 AM

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?
0 Kudos

New to Lansweeper?

Try Lansweeper For Free

Experience Lansweeper with your own data.
Sign up now for a 14-day free trial.

Try Now