All Assets - Report Request

[bluesymbol]

Hi All,

I'm quite new with Lansweeper so apologies if this is something I've missed/already been answered.

In a nutshell our SD need the full list of Assets from Lansweeper so they can compare to their CMDB. They've agreed that they would like ALL info held on ALL assets until they can slim down what they need (which could be months).

Is there a way to produce a report using the below...

• Name
  
• Type
  
• Domain
  
• OS
  
• Model
  
• Manufacturer
  
• IP Address
  
• IP Location
  
• MAC Address
  
• OU
  
• State
  
• Firstseen
  
• Lastseen
  
• LastTried
  
• Description
  
• Purchase Date
  
• Warranty Date
  
• FQDN
  
• DNS Name
  
• Last Patched
  
• Last Full Backup
  
• Last Full Image Location
  
• Building
  
• Department
  
• Branchoffice
  
• Barcode Contact Serialnumber
  
• Asset Number
  
• Custom2
  
• Custom3
  
• Custom4
  
• Custom5
  
• PreventCleanup
  
• Scanserver

Thanks 🙂

[s_subramanian]

Thanks Daniel. Sure, will follow the same in the future

[Daniel_B]

Likely you added tblComputerSystemProduct to your report, but didn't change the join to a Left Join (Select all rows from tblAssets). This will cause only assets to be listed which are having an entry in tblComputerSystemProduct, and therefore only Windows computers. The report below lists all assets.

BTW: Please be sure to post the same question only once in the forum, otherwise the forum topics will become difficult to read.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename As [Asset Type],
  tblAssets.Domain,
  tsysOS.OSname As [OS name],
  Case
    When tblAssetCustom.Manufacturer Like '%lenovo%' Then
    tblComputerSystemProduct.Version Else tblAssetCustom.Model End As Model,
  tblAssetCustom.Manufacturer,
  tblAssets.IPAddress As [IP address],
  tsysIPLocations.IPLocation As [IP location],
  tblAssets.Mac As [MAC address],
  tblADComputers.OU,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblAssets.Description,
  tblAssetCustom.PurchaseDate As [Purchase Date],
  tblAssetCustom.Warrantydate As [Warranty Date],
  tblAssets.FQDN,
  tblAssetCustom.DNSName As [DNS name],
  tblAssetCustom.LastPatched As [Last Patched],
  tblAssetCustom.LastFullbackup As [Last Full Backup],
  tblAssetCustom.LastFullimage As [Last Full Image],
  tblAssetCustom.Location,
  tblAssetCustom.Building,
  tblAssetCustom.Department,
  tblAssetCustom.Branchoffice,
  tblAssetCustom.BarCode,
  tblAssetCustom.Contact,
  tblAssetCustom.Serialnumber,
  tblAssetCustom.OrderNumber,
  tblAssetCustom.Custom1,
  tblAssetCustom.Custom2,
  tblAssetCustom.Custom3,
  tblAssetCustom.Custom4,
  tblAssetCustom.Custom5,
  tblAssetCustom.Custom6,
  tblAssetCustom.Custom7,
  tblAssetCustom.Custom8,
  tblAssetCustom.Custom9,
  tblAssetCustom.Custom10,
  tblAssetCustom.Custom11,
  tblAssetCustom.Custom12,
  tblAssetCustom.Custom13,
  tblAssetCustom.Custom14,
  tblAssetCustom.Custom15,
  tblAssetCustom.Custom16,
  tblAssetCustom.Custom17,
  tblAssetCustom.Custom18,
  tblAssetCustom.Custom19,
  tblAssetCustom.Custom20,
  Case When tblAssetCustom.PreventCleanup = 1 Then 'yes' Else 'no'
  End As [not affected by cleanup options]
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Left Join tsysIPLocations
    On tblAssets.IPNumeric Between tsysIPLocations.StartIP And
    tsysIPLocations.EndIP
  Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
  Left Join tblComputerSystemProduct On tblComputerSystemProduct.AssetID =
    tblAssets.AssetID
Where tblAssetCustom.State = 1
Order By tblAssets.AssetName

[s_subramanian]

Hi,

The report was great. When i added the below query for showing more meaningful Lenovo Model, the query throws the results with only Windows Asset Type. Please help


Case
When tblAssetCustom.Manufacturer Like '%lenovo%' Then
tblComputerSystemProduct.Version Else tblAssetCustom.Model End As Model,

[bluesymbol]

Hi,

That's awesome, thank you! I'll take a look at the Database Dictionary!

Cheers! 🙂

[Daniel_B · Accepted solution]

The following reports approximately matches the information displayed after hitting Assets on the web console. You are able to export this as XLS (both the Assets list and a custom report like this one). This report contains only the most important fields. If you would like to add further data, we recommend having a look at the Database Dictionary which is accessible at the top of the report editor.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes1.AssetTypename As [Asset Type],
  tblAssets.Domain,
  tsysOS.OSname As [OS name],
  tblAssetCustom.Model,
  tblAssetCustom.Manufacturer,
  tblAssets.IPAddress As [IP address],
  tsysIPLocations.IPLocation As [IP location],
  tblAssets.Mac As [MAC address],
  tblADComputers.OU,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblAssets.Description,
  tblAssetCustom.PurchaseDate As [Purchase Date],
  tblAssetCustom.Warrantydate As [Warranty Date],
  tblAssets.FQDN,
  tblAssetCustom.DNSName As [DNS name],
  tblAssetCustom.LastPatched As [Last Patched],
  tblAssetCustom.LastFullbackup As [Last Full Backup],
  tblAssetCustom.LastFullimage As [Last Full Image],
  tblAssetCustom.Location,
  tblAssetCustom.Building,
  tblAssetCustom.Department,
  tblAssetCustom.Branchoffice,
  tblAssetCustom.BarCode,
  tblAssetCustom.Contact,
  tblAssetCustom.Serialnumber,
  tblAssetCustom.OrderNumber,
  tblAssetCustom.Custom1,
  tblAssetCustom.Custom2,
  tblAssetCustom.Custom3,
  tblAssetCustom.Custom4,
  tblAssetCustom.Custom5,
  tblAssetCustom.Custom6,
  tblAssetCustom.Custom7,
  tblAssetCustom.Custom8,
  tblAssetCustom.Custom9,
  tblAssetCustom.Custom10,
  tblAssetCustom.Custom11,
  tblAssetCustom.Custom12,
  tblAssetCustom.Custom13,
  tblAssetCustom.Custom14,
  tblAssetCustom.Custom15,
  tblAssetCustom.Custom16,
  tblAssetCustom.Custom17,
  tblAssetCustom.Custom18,
  tblAssetCustom.Custom19,
  tblAssetCustom.Custom20,
  Case When tblAssetCustom.PreventCleanup = 1 Then 'yes' Else 'no'
  End As [not affected by cleanup options]
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tsysAssetTypes tsysAssetTypes1 On tsysAssetTypes1.AssetType =
    tblAssets.Assettype
  Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Left Join tsysIPLocations
    On tblAssets.IPNumeric Between tsysIPLocations.StartIP And
    tsysIPLocations.EndIP
  Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Where tblAssetCustom.State = 1
Order By tblAssets.AssetName