Notification

Icon
Error

Monitor history showing only last monitors - Monitor history showing only last monitors not all history of monitors

Posted: Friday, July 23, 2021 4:17:00 PM(UTC)
cross_eur

cross_eur

Member Original PosterPosts: 11
0
Like
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
RC62N
#1RC62N Member Posts: 575  
posted: 7/23/2021 5:59:50 PM(UTC)
Try this:
Code:
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. Angel
cross_eur
#2cross_eur Member Original PosterPosts: 11  
posted: 7/23/2021 6:06:51 PM(UTC)
This seems to be exactly what I needed. I will examine it more thoroughly, but I didn't want to forget the thank you.

Active Discussions

Lansweeper Scan User Exclusion / Define User OU for active scanning
by  Almada   Go to last post Go to first unread
Last post: Yesterday at 6:43:34 PM(UTC)
Lansweeper sqlServerId is changing
by  Ciro Bizelli  
Go to last post Go to first unread
Last post: Yesterday at 3:40:34 PM(UTC)
Lansweeper Slow Loading Lansweeper
by  Maikel Vanroelen   Go to last post Go to first unread
Last post: Yesterday at 9:25:31 AM(UTC)
Lansweeper Sync information
by  Jay-IT  
Go to last post Go to first unread
Last post: 9/16/2021 9:20:11 PM(UTC)
Lansweeper "Unknown" exclusion does not exclude Unknown assets
by  Almada   Go to last post Go to first unread
Last post: 9/16/2021 7:18:43 PM(UTC)
Lansweeper multiple scanning servers with granular permission
by  FrankSc  
Go to last post Go to first unread
Last post: 9/16/2021 6:29:54 PM(UTC)
Lansweeper Separate helpdesk websites?
by  JCochran   Go to last post Go to first unread
Last post: 9/15/2021 2:51:03 PM(UTC)
Lansweeper Remove Ads
by  FrankSc  
Go to last post Go to first unread
Last post: 9/15/2021 12:30:18 PM(UTC)