Asset Inventory Questions

[C1Hol]

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)

[Daniel_B]

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]

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 · Accepted solution]

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.