cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
cross_eur
Engaged Sweeper II
With the pandemic, we are trying to see all our monitors that were sent out to people to be able to work at home. I have created the following report, but it shows the complete history of the monitors that were connected. The issue is with Laptops, they are not always connected to the monitors (being mobile and all) and so any report which shows currently connected computers might be missing potential monitors. So if someone could help me with a report that shows only the last time any monitor was ever connected to a computer, and not everytime a monitor is changed, I would be grateful.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Username,
tblAssetCustom.Custom2,
tblAssetCustom.Custom3,
tblAssets.IPAddress,
tblAssets.Firstseen,
tblAssets.Lastseen,
tsysOS.Image As icon,
Case tblMonitorHist.Action
When 1 Then '+'
When 2 Then '-'
End As Change,
tblMonitorHist.MonitorManufacturer,
tblMonitorHist.MonitorModel,
tblMonitorHist.SerialNumber,
tblMonitorHist.LastChanged As [Last monitor change]
From tblAssets
Inner Join tblMonitorHist On tblAssets.AssetID = tblMonitorHist.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where (tblAssets.IPAddress Like '10.15.%' Or tblAssets.IPAddress Like '10.95.%')
And tblMonitorHist.LastChanged >= Cast('03-15-2020' As DATETIME) And
tblAssetCustom.State = 1
Order By tblAssets.AssetName,
[Dernière changement Écran] Desc
5 REPLIES 5
NickTot
Engaged Sweeper

Still trying to get LS to add the IPlocation  in the tblmonitorhist table so I can see where these monitors are physically based on IP. 

NickTot
Engaged Sweeper

Hi I created a new report and used these filters ( MAX ). 

NickTot_0-1708435189316.png

 

cross_eur
Engaged Sweeper II
This seems to be exactly what I needed. I will examine it more thoroughly, but I didn't want to forget the thank you.

My next step is to get the IP location recorded with each entry so I know where the device is physically. Its not currently part of the table. If I use the assets table the location is the last location of the user on record which is not what I need.

RCorbeil
Honored Sweeper II
Try this:
Select Top 1000000
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Username,
tblAssetCustom.Custom2,
tblAssetCustom.Custom3,
tblAssets.IPAddress,
tblAssets.Firstseen,
tblAssets.Lastseen,
tsysOS.Image As icon,
MaxMonitorHist.MonitorManufacturer,
MaxMonitorHist.MonitorModel,
MaxMonitorHist.SerialNumber,
MaxMonitorHist.LastAdded
From
tblAssets
INNER JOIN (SELECT
tblMonitorHist.AssetID,
tblMonitorHist.MonitorManufacturer,
tblMonitorHist.MonitorModel,
tblMonitorHist.SerialNumber,
Max(tblMonitorHist.LastChanged) AS LastAdded
FROM
tblMonitorHist
WHERE
tblMonitorHist.Action = 1 -- added
AND tblMonitorHist.LastChanged >= Cast('03-15-2020' As DATETIME)
GROUP BY
tblMonitorHist.AssetID,
tblMonitorHist.MonitorManufacturer,
tblMonitorHist.MonitorModel,
tblMonitorHist.SerialNumber ) AS MaxMonitorHist ON MaxMonitorHist.AssetID = tblAssets.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where
tblAssetCustom.State = 1
And (tblAssets.IPAddress Like '10.15.%' Or tblAssets.IPAddress Like '10.95.%')
Order By
tblAssets.AssetName

The inner SELECT is pulling a list of the last time any given monitor was connected to an asset on or after 2020-03-15. I believe I got the logic right, but always double-check.