Device and computers by room

[harringg]

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)?

[harringg]

Great! Thanks.

[harringg]

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]

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]

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]

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]

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]

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]

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.

[jgross]

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.