
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
10-05-2011 10:45 PM
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
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
Labels:
- Labels:
-
Archive
3 REPLIES 3

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
10-07-2011 11:53 AM
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
Query 2
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

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
10-08-2011 03:12 AM
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 1Select 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 2Select 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

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
10-06-2011 12:33 AM
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.
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.
