Notification

Icon
Error

Monitor name with ScreenHeight & ScreenWidth

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

cycleheat

Member Original PosterPosts: 35
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: 35  
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: 35  
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 Understanding current setup
by  ggarcia   Go to last post Go to first unread
Last post: Today at 12:24:42 AM(UTC)
Lansweeper Remote desktop custom port
by  sefaucher  
Go to last post Go to first unread
Last post: Yesterday at 10:30:49 PM(UTC)
Lansweeper Unable to transfer user's data prior to removal
by  RickW99456   Go to last post Go to first unread
Last post: Yesterday at 9:06:14 PM(UTC)
Lansweeper Unable to access the lansweeper consol for other computer
by  Kudnan Ingle  
Go to last post Go to first unread
Last post: Yesterday at 7:49:32 PM(UTC)
Lansweeper Closing and re-opening of tickets
by  NWHiker   Go to last post Go to first unread
Last post: Yesterday at 3:42:56 PM(UTC)
Lansweeper Wake on Lan Issues
by  woldummy  
Go to last post Go to first unread
Last post: Yesterday at 3:42:07 PM(UTC)
Lansweeper Deploy and start Software
by  EDV_OHZ   Go to last post Go to first unread
Last post: Yesterday at 12:49:08 PM(UTC)
Lansweeper Helpdek Call Re-Opened
by  mouaad  
Go to last post Go to first unread
Last post: Yesterday at 7:30:59 AM(UTC)