cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
TheITGuy
Engaged Sweeper III
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




1 ACCEPTED SOLUTION
Daniel_B
Lansweeper Alumni
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
s_subramanian
Engaged Sweeper III
Thanks a lot Daniel
Daniel_B
Lansweeper Alumni
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
Engaged Sweeper III
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
Engaged Sweeper III
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
Lansweeper Alumni
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
Engaged Sweeper III
Removing the filter at the bottom worked, Thank you so much for your assistance!!
Daniel_B
Lansweeper Alumni
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
Engaged Sweeper III
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
Lansweeper Alumni
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