Query of all Devices and Windows Machines with Barcode

[mpriess]

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

[Hemoco]

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]

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]

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.