→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
bluesymbol
Engaged Sweeper II
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 🙂
1 ACCEPTED SOLUTION
Daniel_B
Lansweeper Alumni
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

View solution in original post

5 REPLIES 5
s_subramanian
Engaged Sweeper III
Thanks Daniel. Sure, will follow the same in the future
Daniel_B
Lansweeper Alumni
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
Engaged Sweeper III
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
Engaged Sweeper II
Hi,

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

Cheers! 🙂

Daniel_B
Lansweeper Alumni
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