cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
C1Hol
Engaged Sweeper II
Hi all,

I need a little help with a Report for Client Inventory (Desktop and Laptop)
Atm I'm filtering with Assetgroups but it's not showing every Asset.

This is what I need:

Manufacturer
Model
Hostname
Serial
Assettag (Custom5)
Keyboardlayout (if possible)
Contact
AD Username
Status (Active, Stock, etc)
Purchse Date
Warranty Enddate
Invoice Number (Custom2)
Order Number (Custom3)
Comment

This is what i got so far:

Select Top 1000000 tblAssetCustom.Manufacturer As Vendor,
tblAssetCustom.Model,
tblAssets.AssetUnique,
tblAssetCustom.Serialnumber As [Service Tag Number],
tblAssetCustom.Custom5 As AssetTag,
tblAssetCustom.Contact As [Allocated ID],
tblADusers.Displayname,
tblAssetCustom.Custom3 As [PO Number],
tblAssetCustom.PurchaseDate,
tblAssetCustom.Warrantydate As [Warranty End Date],
tblOperatingsystem.Caption As Operatingsystem,
tblAssets.Domain,
tblAssetCustom.AssetID,
tblAssets.Lastseen,
tblAssetCustom.Comments
From tblAssetCustom
Inner Join tblAssets On tblAssetCustom.AssetID = tblAssets.AssetID
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID =
tblAssetGroupLink.AssetGroupID
Inner Join web40repIPLocationlist On web40repIPLocationlist.AssetID =
tblAssets.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join tblADusers On tblADusers.Username = tblAssets.Username
Where tblAssetGroups.AssetGroup Like '%Laptop%' And tblAssetCustom.State = 1
Order By tblAssets.AssetUnique


My Problems are the following:
  • Lot of Laptops are two times in the List

  • Inactive devices are not in the Report

  • Is there a way do get the Hardware Keyboard Layout?

  • How to display the Status ( Active, Stock, etc)

1 ACCEPTED SOLUTION
Daniel_B
Lansweeper Alumni
The default report query lists only active assets. You can change this by removing the filter criterion on tblAssetCustom.State (1 stands for active assets). The text of the asset state is stored in tblState.Statename.
In order to filter on laptops and desktops, you can use table tblPortableBattery. If an asset has an entry in this table, it will be a laptop. Please find a modified report below. This report lists servers as well. If you would like to remove servers from the list, you need to select tblComputersystem.Domainrole and add "< 2" as filter criterion.

Select Top 1000000 Case When tblComputersystem.Domainrole > 1 Then 'Server'
Else Case When Coalesce(tblPortableBattery.AssetID, 0) = 0 Then 'Desktop'
Else 'Laptop' End End As Type,
tblAssetCustom.Manufacturer As Vendor,
tblAssetCustom.Model,
tblAssets.AssetUnique,
tblAssetCustom.Serialnumber As [Service Tag Number],
tblAssetCustom.Custom5 As AssetTag,
tblAssetCustom.Contact As [Allocated ID],
tblADusers.Displayname,
tblState.Statename As [Asset state],
tblAssetCustom.Custom3 As [PO Number],
tblAssetCustom.PurchaseDate,
tblAssetCustom.Warrantydate As [Warranty End Date],
tblAssetCustom.Custom2 As [Invoice number],
tblOperatingsystem.Caption As Operatingsystem,
tblAssets.Domain,
tblAssetCustom.AssetID,
tblAssets.Lastseen,
tblAssetCustom.Comments
From tblAssetCustom
Inner Join tblAssets On tblAssetCustom.AssetID = tblAssets.AssetID
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID =
tblAssetGroupLink.AssetGroupID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join tblADusers On tblADusers.Username = tblAssets.Username And
tblADusers.Userdomain = tblAssets.Userdomain
Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Left Join tblPortableBattery On tblAssets.AssetID = tblPortableBattery.AssetID
Inner Join tblState On tblAssetCustom.State = tblState.State
Order By tblAssets.Assetname


EDIT: The keyboard layout is stored in a numeric string in tblKeyboard.Layout. However, adding this table to your report could cause it to have duplicate records in case several keyboards have been scanned on a computer, so I wouldn't recommend it.

View solution in original post

3 REPLIES 3
Daniel_B
Lansweeper Alumni
You might need to remove tables tblAssetGroupLink and tblAssetGroups from your report. These list static asset groups which the assets are member of. If an asset is member of two groups, it will have two entries in the report.
C1Hol
Engaged Sweeper II
Thanks a lot for the help.

I still have almost every asset twice in this report. No i've not added the Keyboard Layout. It just a copy of your Report
Daniel_B
Lansweeper Alumni
The default report query lists only active assets. You can change this by removing the filter criterion on tblAssetCustom.State (1 stands for active assets). The text of the asset state is stored in tblState.Statename.
In order to filter on laptops and desktops, you can use table tblPortableBattery. If an asset has an entry in this table, it will be a laptop. Please find a modified report below. This report lists servers as well. If you would like to remove servers from the list, you need to select tblComputersystem.Domainrole and add "< 2" as filter criterion.

Select Top 1000000 Case When tblComputersystem.Domainrole > 1 Then 'Server'
Else Case When Coalesce(tblPortableBattery.AssetID, 0) = 0 Then 'Desktop'
Else 'Laptop' End End As Type,
tblAssetCustom.Manufacturer As Vendor,
tblAssetCustom.Model,
tblAssets.AssetUnique,
tblAssetCustom.Serialnumber As [Service Tag Number],
tblAssetCustom.Custom5 As AssetTag,
tblAssetCustom.Contact As [Allocated ID],
tblADusers.Displayname,
tblState.Statename As [Asset state],
tblAssetCustom.Custom3 As [PO Number],
tblAssetCustom.PurchaseDate,
tblAssetCustom.Warrantydate As [Warranty End Date],
tblAssetCustom.Custom2 As [Invoice number],
tblOperatingsystem.Caption As Operatingsystem,
tblAssets.Domain,
tblAssetCustom.AssetID,
tblAssets.Lastseen,
tblAssetCustom.Comments
From tblAssetCustom
Inner Join tblAssets On tblAssetCustom.AssetID = tblAssets.AssetID
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID =
tblAssetGroupLink.AssetGroupID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join tblADusers On tblADusers.Username = tblAssets.Username And
tblADusers.Userdomain = tblAssets.Userdomain
Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Left Join tblPortableBattery On tblAssets.AssetID = tblPortableBattery.AssetID
Inner Join tblState On tblAssetCustom.State = tblState.State
Order By tblAssets.Assetname


EDIT: The keyboard layout is stored in a numeric string in tblKeyboard.Layout. However, adding this table to your report could cause it to have duplicate records in case several keyboards have been scanned on a computer, so I wouldn't recommend it.