cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
cycleheat
Engaged Sweeper III
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:

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?
5 REPLIES 5
endyk
Engaged Sweeper III
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.
endyk
Engaged Sweeper III
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
Engaged Sweeper III
endyk wrote:

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
Engaged Sweeper III
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
Engaged Sweeper III
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.

New to Lansweeper?

Try Lansweeper For Free

Experience Lansweeper with your own data.
Sign up now for a 14-day free trial.

Try Now