Report all devices

[Forzaola]

I just start using Lansweeper recently and i would like to create a report with all the devices in the Lansweeper database. In this report i would like to add field like serial number and hw devices. This report i would like to export to our CMDB.

How do i start creating this report?

[Hemoco]

We removed some unnecessary tables from the report and added a "Distinct" to the "Select" statement to ensure that only distinct lines are included.

Select Distinct Top 1000000 tblComputers.Computername,
tblComputers.ComputerUnique, tblComputers.Domain

[Forzaola]

Looks like this works.
Could you tell me what you changed so i can learn something... 😉

[Hemoco]

Could you run the report as shown below instead.

Select Distinct Top 1000000 tblComputers.Computername,
tblComputers.ComputerUnique, tblComputers.Domain,
TsysChassisTypes.ChassisName, tblComputersystem.Manufacturer,
tblComputersystem.Model, web40AllIPLocations.IPLocation, Web40OSName.OSname,
Web40OSName.OScode, tblOperatingsystem.InstallDate, Web40OSName.SP,
tblComputers.Username, tblSystemEnclosure.SerialNumber,
tblComputers.LastknownIP
From tblComputers Left Join
tblSystemEnclosure On tblComputers.Computername =
tblSystemEnclosure.Computername Inner Join
TsysChassisTypes On TsysChassisTypes.Chassistype =
tblSystemEnclosure.ChassisTypes Left Join
tblComputersystem On tblComputers.Computername =
tblComputersystem.Computername Left Join
web40AllIPLocations On web40AllIPLocations.Computername =
tblComputers.Computername Left Join
Web40OSName On Web40OSName.Computername = tblComputers.Computername Left Join
tblOperatingsystem On tblComputers.Computername =
tblOperatingsystem.Computername Left Join
web40CorrectMemory On web40CorrectMemory.Computername =
tblComputers.Computername Left Join
web40ProcessorCapacity On web40ProcessorCapacity.Computername =
tblComputers.Computername
Order By tblComputers.ComputerUnique

[Forzaola]

I Customized the report so we can use it beter for the import.
But for some reason it shows some computers twice. See image.
How can i change this?

Select Top 1000000 tblComputers.Computername, tblComputers.ComputerUnique, tblComputers.Domain, TsysChassisTypes.ChassisName, tblComputersystem.Manufacturer, tblComputersystem.Model, web40AllIPLocations.IPLocation, Web40OSName.OSname, Web40OSName.OScode, tblOperatingsystem.InstallDate, Web40OSName.SP, tblComputers.Username, tblSystemEnclosure1.SerialNumber As SerialNumber, tblComputers.LastknownIP From tblComputers Left Join tblSystemEnclosure On tblComputers.Computername = tblSystemEnclosure.Computername Inner Join TsysChassisTypes On TsysChassisTypes.Chassistype = tblSystemEnclosure.ChassisTypes Left Join tblComputersystem On tblComputers.Computername = tblComputersystem.Computername Left Join web40AllIPLocations On web40AllIPLocations.Computername = tblComputers.Computername Left Join Web40OSName On Web40OSName.Computername = tblComputers.Computername Left Join tblOperatingsystem On tblComputers.Computername = tblOperatingsystem.Computername Left Join tblSystemEnclosure tblSystemEnclosure1 On tblComputers.Computername = tblSystemEnclosure1.Computername Left Join web40CorrectMemory On web40CorrectMemory.Computername = tblComputers.Computername Left Join web40ProcessorCapacity On web40ProcessorCapacity.Computername = tblComputers.Computername Inner Join tblDiskdrives On tblComputers.Computername = tblDiskdrives.Computername Where tblDiskdrives.DriveType = 3 Order By tblComputers.ComputerUnique

[Forzaola]

Thanks! 🙂

[Hemoco · Accepted solution]

We recommend using a separate report for computers and devices, as it is currently difficult to create a report that includes both. In Lansweeper 5.0, which is currently in development, computers and devices will be merged into a single "asset" category.

Software and product key info should go in separate reports as well, as these two scan items are unrelated. For product keys you can use the built-in report "License: Software licensekey overview".


Computer report for the rest of the requested data:

Select tblComputers.Computername, tblComputers.ComputerUnique,
tblComputers.Domain, TsysChassisTypes.ChassisName,
tblComputersystem.Manufacturer, tblComputersystem.Model,
web40AllIPLocations.IPLocation, Web40OSName.OSname, Web40OSName.OScode,
tblOperatingsystem.InstallDate, Web40OSName.SP,
tblOperatingsystem.SerialNumber, tblComputers.Username,
tblComputers.Userdomain, tblSystemEnclosure1.SerialNumber As SerialNumber1,
tblDiskdrives.Caption, Cast(Cast(tblDiskdrives.Freespace As bigint) / 1024 /
1024 As numeric) As Free, Cast(Cast(tblDiskdrives.Size As bigint) / 1024 /
1024 As numeric) As Total, web40CorrectMemory.Memory,
web40ProcessorCapacity.NrOfProcessors, web40ProcessorCapacity.CPU,
tblComputers.LastknownIP, tblSoftware.softwareName,
tblSoftware.softwareVersion, tblSoftware.SoftwarePublisher
From tblComputers Left Join
tblSystemEnclosure On tblComputers.Computername =
tblSystemEnclosure.Computername Inner Join
TsysChassisTypes On TsysChassisTypes.Chassistype =
tblSystemEnclosure.ChassisTypes Left Join
tblComputersystem On tblComputers.Computername =
tblComputersystem.Computername Left Join
web40AllIPLocations On web40AllIPLocations.Computername =
tblComputers.Computername Left Join
Web40OSName On Web40OSName.Computername = tblComputers.Computername Left Join
tblOperatingsystem On tblComputers.Computername =
tblOperatingsystem.Computername Left Join
tblSystemEnclosure tblSystemEnclosure1 On tblComputers.Computername =
tblSystemEnclosure1.Computername Left Join
tblSoftware On tblComputers.Computername = tblSoftware.ComputerName Left Join
web40CorrectMemory On web40CorrectMemory.Computername =
tblComputers.Computername Left Join
web40ProcessorCapacity On web40ProcessorCapacity.Computername =
tblComputers.Computername Inner Join
tblDiskdrives On tblComputers.Computername = tblDiskdrives.Computername
Where tblDiskdrives.DriveType = 3
Order By tblComputers.ComputerUnique, tblSoftware.softwareName,
tblSoftware.softwareVersion

Device report:

Select tblCustDevices.Displayname, tsysDevicetypes.ItemTypename,
tblCustDevices.Vendor, tblCustDevices.Model,
web40AllIPLocationsDev.IPLocation, tblCustDevices.Serialnumber,
tblCustDevHD.Filesystem, tblCustDevHD.Size, tblCustDevHD.Used,
tblCustDevHD.Available, tblCustDevHD.UsePCT, tblCustDevices.Ipaddress
From tblCustDevices Left Join
tblCustDevHD On tblCustDevices.DeviceKey = tblCustDevHD.DeviceKey Inner Join
tsysDevicetypes On tsysDevicetypes.ItemType = tblCustDevices.Devicetype
Left Join
web40AllIPLocationsDev On web40AllIPLocationsDev.DeviceKey =
tblCustDevices.DeviceKey
Order By tblCustDevices.IPNumeric, tblCustDevHD.Size

[Hemoco]

Could you clarify whether you want to create a computer or a device report. Devices are printers, routers, etc. In case of a computer report, please let us know as well which specific information besides serial you would like to include.

[Forzaola]

Lansweeper wrote:
Could you clarify whether you want to create a computer or a device report. Devices are printers, routers, etc. In case of a computer report, please let us know as well which specific information besides serial you would like to include.

Thanks for the quick response.

Actually i'm looking for a report to get both devices and computers. 2 report also will do.
The information i would like to include in my report are the following:
Device Name
type(desktop, laptop)
Manufacturer
Model
IP location
OS + version
Install Date
servicepack
OS serial
User(lastlogin)
Domain
Serialnumber
Disk volumes
Memory
Processor
Network(+ip info)
Other Hardware info
Software installed(+licences)

I would like to export it to a CSV file so i can import it in to out CMDB software.