Notification

Icon
Error

Monitor name with ScreenHeight & ScreenWidth

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

cycleheat

Member Original PosterPosts: 18
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: 18  
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: 18  
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 Duplicate Asset 1 Mac address, 1 domain\computer\1
by  jstrong71   Go to last post Go to first unread
Last post: Today at 9:16:58 PM(UTC)
Lansweeper Dell Service Tag
by  cycleheat  
Go to last post Go to first unread
Last post: Today at 5:07:22 PM(UTC)
Lansweeper VM use 2 licence asset
by  PhilippeC.   Go to last post Go to first unread
Last post: Today at 4:52:33 PM(UTC)
Lansweeper Windows Defender AV
by  Mikey!  
Go to last post Go to first unread
Last post: Yesterday at 4:32:50 PM(UTC)
Lansweeper Deployment Package Successful but Log error
by  CyberCitizen   Go to last post Go to first unread
Last post: Yesterday at 12:23:38 AM(UTC)
Lansweeper Dynamic Groups - Mass edit fields
by  confablab  
Go to last post Go to first unread
Last post: 11/8/2019 6:25:36 PM(UTC)
Lansweeper LSAgent Install switches
by  Ryan.G   Go to last post Go to first unread
Last post: 11/8/2019 3:35:31 PM(UTC)
Lansweeper Static Group status
by  Ryan.G  
Go to last post Go to first unread
Last post: 11/8/2019 3:31:59 PM(UTC)