Monitor history showing only last monitors

[cross_eur]

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

[NickTot]

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]

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

[cross_eur]

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

[NickTot]

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]

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.