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 Exchange Mailbox Usage
by  nhouse24   Go to last post Go to first unread
Last post: Today at 10:31:09 PM(UTC)
Lansweeper Personalize interface user ticket
by  Brighton  
Go to last post Go to first unread
Last post: Today at 9:31:34 PM(UTC)
Lansweeper Update dashboard defaulting to Helpdesk
by  nriddick   Go to last post Go to first unread
Last post: Today at 9:22:18 PM(UTC)
Lansweeper Change Management : Creating new Events type
by  Hugo Lynch  
Go to last post Go to first unread
Last post: Today at 8:05:34 PM(UTC)
Lansweeper Linked Tickets - Helpdesk Workflow/Subordinate Tickets
by  Argon0   Go to last post Go to first unread
Last post: Today at 5:22:27 PM(UTC)
Lansweeper SCCM Scanning Failure
by  wanduster  
Go to last post Go to first unread
Last post: Today at 4:18:47 PM(UTC)
Lansweeper Notification ticket in pending after 1 week
by  Vasile Ciuban   Go to last post Go to first unread
Last post: Today at 3:18:04 PM(UTC)
Lansweeper Assistance on Building a Report on Java Installations
by  Rob-CD  
Go to last post Go to first unread
Last post: Today at 10:20:11 AM(UTC)