→ Celebrate SysAdmin Day 2024 with Lansweeper Enter our Giveaway here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
harringg
Champion Sweeper
I've got this report which shows device by room:
SELECT TOP 1000000 tsysdevicetypes.itemtypeicon10 AS icon,
tblcustdevices.displayname AS [Device name],
tblcustdevices.model,
tblcustdevices.vendor,
tblcustdevices.location,
tblcustdevices.devicekey,
tblcustdevices.purchasedate,
tblcustdevices.serialnumber,
tblcustdevices.contact
FROM tblcustdevices
INNER JOIN tsysdevicetypes
ON tsysdevicetypes.itemtype = tblcustdevices.devicetype
WHERE tblcustdevices.location = 'Room 150'
ORDER BY tblcustdevices.displayname


I've got this report that shows computers by room:

SELECT TOP 1000000 tblcomputers.computername,
tblcomputers.computerunique,
tblcomputers.domain,
tblcompcustom.purchasedate,
tblcompcustom.warrantydate,
tblcompcustom.location,
tblcompcustom.building,
tblcompcustom.department,
tblcompcustom.branchoffice,
tblcompcustom.state,
tblcompcustom.lastchanged,
tblcompcustom.barcode,
tblcompcustom.custom1,
tblcompcustom.lastsaved,
tblcompcustom.firstseen,
tblcompcustom.lastseen
FROM tblcomputers
INNER JOIN tblcompcustom
ON tblcomputers.computername = tblcompcustom.computername
WHERE tblcompcustom.location LIKE 'Room 150'
ORDER BY tblcomputers.computerunique


Is there a way to merge them into one report? So I can have a report that shows all the devices and computers in the same room (Location)?
1 ACCEPTED SOLUTION
Hemoco
Lansweeper Alumni
You need to use 'UNION'

Example

Select field1,field2 from table1
UNION
Select field1,field2 from table2



See: http://msdn.microsoft.com/en-us/library/ms180026.aspx

View solution in original post

11 REPLIES 11
harringg
Champion Sweeper
Great! Thanks.
harringg
Champion Sweeper
Select 'DEV' as [Type], '' as [Computer Name], tblcustdevices.displayname as [Device Name], '' as [Domain], tblcustdevices.vendor as [Vendor]
FROM tblcustdevices
WHERE tblcustdevices.location LIKE 'Room 150'
UNION
Select 'COMP' as [Type], tblcomputers.computerunique as [Computer Name], '' as [Device Name], tblcomputers.domain as [Domain], '' as [Vendor]
FROM tblcompcustom
INNER JOIN tblcomputers
ON tblcomputers.computername = tblcompcustom.computername
WHERE tblcompcustom.location LIKE 'Room 150'


This report worked in V4. Could you assist with similar code for v5?

I've got a custom field that lists a switch port in a given room, and I'd like a report of all devices on that port, regardless if it's a PC, printer, manually added Asset.

I just need name, room number PC/printer/asset is located in and the custom field (tblAssetCustom.Custom9)showing where the physical switch port is located.

PC1, Room 99, Switch200-1
PC2, Room 98, Switch200-2
Printer1, Room 97, Switch200-3
Asset (ie. non-managed switch), Room 97, Switch200-3

Thanks
Hemoco
Lansweeper Alumni
harringg wrote:
This report worked in V4. Could you assist with similar code for v5?

Lansweeper 5.0 has a different database structure and does not require union queries to report on both computers and devices. Please refer to the sample report below.
Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.IPAddress,
tblAssetCustom.Custom9,
tblAssets.Lastseen
From tblAssets
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Order By tblAssets.IPNumeric

wcb
Engaged Sweeper III
I played around with it on my site and it seems it does not like column names with spaces. I removed the spaces in Computer Name and Device Name on my example and it now works in the browser.
harringg
Champion Sweeper
wcb wrote:
I played around with it on my site and it seems it does not like column names with spaces. I removed the spaces in Computer Name and Device Name on my example and it now works in the browser.


Great! Thanks both for your help. I can now customize this report to give me the data of all items in a given room.
harringg
Champion Sweeper
It runs great in the report builder, but when run in the web interface (on a PC on the domain) it results in the following error. Other reports run fine in this same browser on this PC.

Runtime Error 
Description: An application error occurred on the server. The current custom error settings for this application prevent the details of the application error from being viewed remotely (for security reasons). It could, however, be viewed by browsers running on the local server machine.

Details: To enable the details of this specific error message to be viewable on remote machines, please create a <customErrors> tag within a "web.config" configuration file located in the root directory of the current web application. This <customErrors> tag should then have its "mode" attribute set to "Off".


<!-- Web.Config Configuration File -->

<configuration>
<system.web>
<customErrors mode="Off"/>
</system.web>
</configuration>

Notes: The current error page you are seeing can be replaced by a custom error page by modifying the "defaultRedirect" attribute of the application's <customErrors> configuration tag to point to a custom error page URL.


<!-- Web.Config Configuration File -->

<configuration>
<system.web>
<customErrors mode="RemoteOnly" defaultRedirect="mycustompage.htm"/>
</system.web>
</configuration>
wcb
Engaged Sweeper III
Notice the data from tblcomputers.domain and tblcustdevices.vendor are unique values, but are under the same column. I'd like them each in their own column. So for a given row, tblcomputers.domain would be "DOMAIN" and tblcustdevices.vendor would be blank. And likewise, for a given row, tblcomputers.domain would be blank and tblcustdevices.vendor would be "Acer".


You can put text data in your select statement to have it return a constant value. When using a union, you only need to have the same number of columns and the corresponding column in each select statement should be of the same data type (date, text, number, etc).

Using this information, you could do something like:

Select 'DEV' as [Type], '' as [Computer Name], tblcustdevices.displayname as [Device Name], '' as [Domain], tblcustdevices.vendor as [Vendor]
FROM tblcustdevices
WHERE tblcustdevices.location LIKE 'Room 150'
UNION
Select 'COMP' as [Type], tblcomputers.computerunique as [Computer Name], '' as [Device Name], tblcomputers.domain as [Domain], '' as [Vendor]
FROM tblcompcustom
INNER JOIN tblcomputers
ON tblcomputers.computername = tblcompcustom.computername
WHERE tblcompcustom.location LIKE 'Room 150'


harringg
Champion Sweeper
Select Top 20 tblCustDevices.Location
From tblCustDevices
Where tblCustDevices.Location Like 'Room 150'
Union
Select tblCompCustom.Location
From tblCompCustom


Results in 20 room numbers, including Room 150

SELECT TOP 20 tblcustdevices.location
FROM tblcustdevices
WHERE tblcustdevices.location LIKE 'Room 150'
UNION
SELECT tblcompcustom.location
FROM tblcompcustom
WHERE tblcompcustom.location LIKE 'Room 150'


Results in a single result, Room 150.

Expanding the SQL Query to:
SELECT TOP 100 tblcustdevices.location,
tblcustdevices.displayname,
tblcustdevices.model,
tblcustdevices.vendor AS [Device Vendor]
FROM tblcustdevices
WHERE tblcustdevices.location LIKE 'Room 150'
UNION
SELECT tblcompcustom.location,
tblcomputers.computerunique,
tblcomputers.DESCRIPTION,
tblcomputers.domain AS [My Domain]
FROM tblcompcustom
INNER JOIN tblcomputers
ON tblcomputers.computername = tblcompcustom.computername
WHERE tblcompcustom.location LIKE 'Room 150'


Results in: (getting closer)


Location	Displayname	Model	Device Vendor
Room 150 Acer LCD Monitor V193W Acer
Room 150 Brother HL-4150CDN HL-4150 Brother
Room 150 Domain\COMP2\1 DOMAIN
Room 150 Domain\COMP3\1 DOMAIN
Room 150 Domain\COMP4\1 COMP4 DOMAIN
Room 150 Domain\COMP5\1 DOMAIN
Room 150 Gateway LCD Monitor FPD1730 Gateway
Room 150 Viewsonic VA2448 VA2448 Viewsonic
Room 150 Viewsonic VA2448 VA2448 SERIES Viewsonic
Room 150 WORKGROUP\NCOMP1\1 COMP1 WORKGROUP
Room 150 Zyxel 4 port Wireless Router NBG-419N Zxyel


Is it possible to have the fields from my original device by room report and my computers by room report in one single report? Or are there not enough common fields/field types in the SQL database?

Notice the data from tblcomputers.domain and tblcustdevices.vendor are unique values, but are under the same column. I'd like them each in their own column. So for a given row, tblcomputers.domain would be "DOMAIN" and tblcustdevices.vendor would be blank. And likewise, for a given row, tblcomputers.domain would be blank and tblcustdevices.vendor would be "Acer".

Thanks for getting me this far. If I can get this data in one report, that will be powerful reporting information.
Is there an update for this for version 5?


harringg wrote:
Select Top 20 tblCustDevices.Location
From tblCustDevices
Where tblCustDevices.Location Like 'Room 150'
Union
Select tblCompCustom.Location
From tblCompCustom


Results in 20 room numbers, including Room 150

SELECT TOP 20 tblcustdevices.location
FROM tblcustdevices
WHERE tblcustdevices.location LIKE 'Room 150'
UNION
SELECT tblcompcustom.location
FROM tblcompcustom
WHERE tblcompcustom.location LIKE 'Room 150'


Results in a single result, Room 150.

Expanding the SQL Query to:
SELECT TOP 100 tblcustdevices.location,
tblcustdevices.displayname,
tblcustdevices.model,
tblcustdevices.vendor AS [Device Vendor]
FROM tblcustdevices
WHERE tblcustdevices.location LIKE 'Room 150'
UNION
SELECT tblcompcustom.location,
tblcomputers.computerunique,
tblcomputers.DESCRIPTION,
tblcomputers.domain AS [My Domain]
FROM tblcompcustom
INNER JOIN tblcomputers
ON tblcomputers.computername = tblcompcustom.computername
WHERE tblcompcustom.location LIKE 'Room 150'


Results in: (getting closer)


Location	Displayname	Model	Device Vendor
Room 150 Acer LCD Monitor V193W Acer
Room 150 Brother HL-4150CDN HL-4150 Brother
Room 150 Domain\COMP2\1 DOMAIN
Room 150 Domain\COMP3\1 DOMAIN
Room 150 Domain\COMP4\1 COMP4 DOMAIN
Room 150 Domain\COMP5\1 DOMAIN
Room 150 Gateway LCD Monitor FPD1730 Gateway
Room 150 Viewsonic VA2448 VA2448 Viewsonic
Room 150 Viewsonic VA2448 VA2448 SERIES Viewsonic
Room 150 WORKGROUP\NCOMP1\1 COMP1 WORKGROUP
Room 150 Zyxel 4 port Wireless Router NBG-419N Zxyel


Is it possible to have the fields from my original device by room report and my computers by room report in one single report? Or are there not enough common fields/field types in the SQL database?

Notice the data from tblcomputers.domain and tblcustdevices.vendor are unique values, but are under the same column. I'd like them each in their own column. So for a given row, tblcomputers.domain would be "DOMAIN" and tblcustdevices.vendor would be blank. And likewise, for a given row, tblcomputers.domain would be blank and tblcustdevices.vendor would be "Acer".

Thanks for getting me this far. If I can get this data in one report, that will be powerful reporting information.