→ Celebrate SysAdmin Day 2024 with Lansweeper Enter our Giveaway here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
mpriess
Engaged Sweeper
Hi guys,

I need to generate a report that shows all Devices and Windows Workstations and Windows Servers with the following info:

Hostname
IP Address
Barcode


I know these details, including the barcode, come from different tables if they are devices or windows machines.

When I join the tables using the report builder I cannot get unique entries for each device or windows host that includes all systems. As soon as I join the tables my total system count goes from ~1360 entries to ~560.

I am obviously doing something wrong. Could you assist with this report creation?

Very much appreciated.

Regards,
Mark
3 REPLIES 3
Hemoco
Lansweeper Alumni
It is currently extremely difficult to create reports that include both computer and device info. This issue will be addressed in one of our future updates, where computers and devices will be merged into one “asset” category.

In regards to your post about the discrepancy between the two reports: when linking tables to tblComputers, make sure to right-click the link and choose “Select all rows from tblComputers”. This ensures that your results include empty entries present in the selected fields.


The following two queries will yield more complete results:

Query 1

Select Top 1000000 tblComputers.Computername, tblComputers.ComputerUnique,
tblComputers.Domain, Web40OSName.OSname, Web40OSName.Compimage As icon,
web40ProcessorCapacity.CPU, web40ProcessorCapacity.Name,
web40ProcessorCapacity.MaxClockSpeed As ClockSpeed,
web40ProcessorCapacity.NrOfProcessors As #CPU
From tblComputers Left Join
tblComputerSystemProduct On tblComputers.Computername =
tblComputerSystemProduct.Computername Left Join
web40ActiveComputers On web40ActiveComputers.Computername =
tblComputers.Computername Left Join
Web40OSName On Web40OSName.Computername = tblComputers.Computername Left Join
web40ProcessorCapacity On web40ProcessorCapacity.Computername =
tblComputers.Computername
Order By web40ProcessorCapacity.NrOfProcessors Desc, tblComputers.Computer


Query 2

Select Top 1000000 tblComputers.LastknownIP As IP, tblComputers.ComputerUnique,
Web40OSName.OSname, tblCompCustom.BarCode
From tblComputers Left Join
web40ActiveComputers On tblComputers.Computername =
web40ActiveComputers.Computername Left Join
Web40OSName On Web40OSName.Computername = tblComputers.Computername Left Join
web40AllIPLocations On web40AllIPLocations.Computername =
tblComputers.Computername Left Join
tblCompCustom On tblComputers.Computername = tblCompCustom.Computername
Where tblComputers.LastknownIP <> ''
Order By tblComputers.IPNumeric
mpriess
Engaged Sweeper
Thank you... the "make sure to right-click the link and choose, "Select all rows from tblComputers." This ensures that your results include empty entries present in the selected fields."...did the trick.












Lansweeper wrote:
It is currently extremely difficult to create reports that include both computer and device info. This issue will be addressed in one of our future updates, where computers and devices will be merged into one “asset” category.

In regards to your post about the discrepancy between the two reports: when linking tables to tblComputers, make sure to right-click the link and choose “Select all rows from tblComputers”. This ensures that your results include empty entries present in the selected fields.


The following two queries will yield more complete results:

Query 1

Select Top 1000000 tblComputers.Computername, tblComputers.ComputerUnique,
tblComputers.Domain, Web40OSName.OSname, Web40OSName.Compimage As icon,
web40ProcessorCapacity.CPU, web40ProcessorCapacity.Name,
web40ProcessorCapacity.MaxClockSpeed As ClockSpeed,
web40ProcessorCapacity.NrOfProcessors As #CPU
From tblComputers Left Join
tblComputerSystemProduct On tblComputers.Computername =
tblComputerSystemProduct.Computername Left Join
web40ActiveComputers On web40ActiveComputers.Computername =
tblComputers.Computername Left Join
Web40OSName On Web40OSName.Computername = tblComputers.Computername Left Join
web40ProcessorCapacity On web40ProcessorCapacity.Computername =
tblComputers.Computername
Order By web40ProcessorCapacity.NrOfProcessors Desc, tblComputers.Computer


Query 2

Select Top 1000000 tblComputers.LastknownIP As IP, tblComputers.ComputerUnique,
Web40OSName.OSname, tblCompCustom.BarCode
From tblComputers Left Join
web40ActiveComputers On tblComputers.Computername =
web40ActiveComputers.Computername Left Join
Web40OSName On Web40OSName.Computername = tblComputers.Computername Left Join
web40AllIPLocations On web40AllIPLocations.Computername =
tblComputers.Computername Left Join
tblCompCustom On tblComputers.Computername = tblCompCustom.Computername
Where tblComputers.LastknownIP <> ''
Order By tblComputers.IPNumeric

mpriess
Engaged Sweeper
Let me provide another example...

When the following is run I get 1300+ machines...

Select Top 1000000 tblComputers.Computername, tblComputers.ComputerUnique, tblComputers.Domain, Web40OSName.OSname, Web40OSName.Compimage As icon, web40ProcessorCapacity.CPU, web40ProcessorCapacity.Name, web40ProcessorCapacity.MaxClockSpeed As ClockSpeed, web40ProcessorCapacity.NrOfProcessors As #CPU From tblComputers Inner Join tblComputerSystemProduct On tblComputers.Computername = tblComputerSystemProduct.Computername Inner Join web40ActiveComputers On web40ActiveComputers.Computername = tblComputers.Computername Inner Join Web40OSName On Web40OSName.Computername = tblComputers.Computername Inner Join web40ProcessorCapacity On web40ProcessorCapacity.Computername = tblComputers.Computername Order By web40ProcessorCapacity.NrOfProcessors Desc, tblComputers.Computer

However, when this is run I only get around 600...

Select Top 1000000 dbo.tblComputers.LastknownIP As IP, dbo.tblComputers.ComputerUnique, Web40OSName.OSname, tblCompCustom.BarCode From dbo.tblComputers Inner Join dbo.web40ActiveComputers On dbo.tblComputers.Computername = dbo.web40ActiveComputers.Computername Inner Join Web40OSName On Web40OSName.Computername = dbo.tblComputers.Computername Inner Join web40AllIPLocations On web40AllIPLocations.Computername = dbo.tblComputers.Computername Inner Join tblCompCustom On dbo.tblComputers.Computername = tblCompCustom.Computername Where dbo.tblComputers.LastknownIP <> '' Order By dbo.tblComputers.IPNumeric


So, again the goal is just to get a list of Hostname, IP and Barcode for all devices and windows machines in one report.

Thank you.