Notification

Icon
Error

Monitor name with ScreenHeight & ScreenWidth

Posted: Thursday, May 2, 2019 11:57:44 PM(UTC)
cycleheat

cycleheat

Member Original PosterPosts: 17
0
Like
Hello,

I'm trying to write a report that gives information about the monitors, not so much about the computers they are attached to.

So far this is what I have:

Code:
Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename As AssetType,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblState.Statename
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblState On tblState.State = tblAssetCustom.State
Where tblState.Statename = 'Active' And tblAssets.Assettype = 208
Order By tblAssets.AssetName


I'm running into difficulty adding the tblMonitor for ManufacturedDate and tblDesktopMonitor for tblDesktopMonitor for ScreenWidth and ScreenHeight. My efforts to change the above have caused the Monitor's AssetName, serial, model, etc to instead be that of the computer.

Does anyone know what the relationships are between these tables?
endyk
#1endyk Member Posts: 24  
posted: 5/14/2019 1:01:29 PM(UTC)
Hello,

Not sure what issue you are running into. I've modified your query to return the manufactured Data and the screen width and height.
Feel free to try this and let me know what issue you are having.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename As AssetType,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblState.Statename,
tblMonitor.ManufacturedDate,
tblDesktopMonitor.ScreenWidth,
tblDesktopMonitor.ScreenHeight
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblState On tblState.State = tblAssetCustom.State
inner join tblMonitor on tblMonitor.AssetID = tblAssets.AssetID
inner join tblDesktopMonitor on tblDesktopMonitor.AssetID = tblAssets.AssetID
--Where tblState.Statename = 'Active' And tblAssets.Assettype = 208
Order By tblAssets.AssetName
cycleheat
#2cycleheat Member Original PosterPosts: 17  
posted: 5/14/2019 4:31:13 PM(UTC)
Thanks! I appreciate that report. The information I am wanting to display is the AssetName, Model, etc of the monitor. When I modified my initial report, and also using the one that endyk helpfully wrote, the fields of the computer asset are being returned, rather than the monitor asset.

For example, it's returning Computer-123, Dell instead of Monitor-456, Viewsonic.
endyk
#3endyk Member Posts: 24  
posted: 5/14/2019 4:42:02 PM(UTC)
You can add the monitor model and monitor manufacturer fields in the query and it should get you what you want. I modified the script.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblMonitor.MonitorModel,
tblMonitor.MonitorManufacturer,
tsysAssetTypes.AssetTypename As AssetType,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblState.Statename,
tblMonitor.ManufacturedDate,
tblDesktopMonitor.ScreenWidth,
tblDesktopMonitor.ScreenHeight
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblState On tblState.State = tblAssetCustom.State
inner join tblMonitor on tblMonitor.AssetID = tblAssets.AssetID
inner join tblDesktopMonitor on tblDesktopMonitor.AssetID = tblAssets.AssetID
--Where tblState.Statename = 'Active' And tblAssets.Assettype = 208
Order By tblAssets.AssetName


update: I now realize you want to scan only monitor assets. It seems like the query above may return only monitors attached to a computer. I'll look into this some more and see if I can find a better solution.
cycleheat
#4cycleheat Member Original PosterPosts: 17  
posted: 5/14/2019 11:55:08 PM(UTC)
Originally Posted by: endyk Go to Quoted Post

update: I now realize you want to scan only monitor assets. It seems like the query above may return only monitors attached to a computer. I'll look into this some more and see if I can find a better solution.


Thanks for the modification, I'll give that a go.

Regarding the update, it doesn't necessarily have to be only monitors attached to a computer (which I suppose would island some monitors taken out of service), it's just for this report that I don't care about what they are attached to and would delete those PC serial, PC Model, etc columns from the final output. Although... including offline monitors or monitors that were moved to a Linux machine would be skipped now that I think more about it... :)
endyk
#5endyk Member Posts: 24  
posted: 5/15/2019 11:16:03 AM(UTC)
OK. Give it a try and see how it looks to you. I think the last query I provided will should provide what you need.

Active Discussions

Lansweeper Remove all users from old domain
by  cmuter   Go to last post Go to first unread
Last post: 9/20/2019 8:03:58 PM(UTC)
Lansweeper Static IP Address
by  cycleheat  
Go to last post Go to first unread
Last post: 9/20/2019 4:07:16 PM(UTC)
Lansweeper Bitlocker Encryption Recovery Key no information found
by  Stephane   Go to last post Go to first unread
Last post: 9/20/2019 2:26:19 PM(UTC)
Lansweeper InTune Scanning Issues
by  Esben.D  
Go to last post Go to first unread
Last post: 9/20/2019 12:34:59 PM(UTC)
Lansweeper Office 365 scanning issues
by  Esben.D   Go to last post Go to first unread
Last post: 9/20/2019 12:23:30 PM(UTC)
Lansweeper Hard Drive Tracking - Start to Finish
by  Lone Jedi  
Go to last post Go to first unread
Last post: 9/19/2019 8:11:56 PM(UTC)
Lansweeper Lansweeper Reporting Old Assets as New
by  Jpatterson   Go to last post Go to first unread
Last post: 9/19/2019 12:52:14 PM(UTC)
Lansweeper Cannot edit a ticket anymore
by  Esben.D  
Go to last post Go to first unread
Last post: 9/19/2019 12:32:42 PM(UTC)