This website uses cookies. By clicking Accept, you consent to the use of cookies. Click Here to learn more about how we use cookies.
Accept
Reject

→ 🚀What's New? Join Us for the Fall Product Launch! Register Now !

Community FAQ
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Show  only  | Search instead for 
Did you mean: 

Days since last update, including "last seen" clause, last reboot, and some colour

Corin_Grieves
Engaged Sweeper

‎01-28-2021 05:57 PM

I've created this report and maybe others might find it useful- we needed a "Quick glance" "Days since last update" report, suitable for consumption by our technical management and compliance teams. I've found that the tblQuickFixEngineering query often reports correctly on update installation, but this isn't much use where a reboot is required, so I've included a "Days since reboot" column to make it easier to check for these cases.

Our platform goes through regular changes, so any servers that haven't been seen in 2 days are excluded.

It also colours the output depending on the duration since the last update (red = bad)

(TODO- highlight where the last reboot time is significantly longer than the last update time. For the moment we just manually sort by that column to check)

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tsysOS.OSname,
  tblAssets.Username As LastUserLogin,
  tblAssets.IPAddress,
  tblAssets.Description,
  DateDiff(day, Max(Convert(datetime,tblAssets.Lastseen)), GetDate()) As
  DaysSinceSeen,
  DateDiff(day, Max(Convert(datetime,tblQuickFixEngineering.InstalledOn)),
  GetDate()) As DaysSincePatched,
  Convert(Decimal(12,0),tblAssets.Uptime / 86400) As UptimeDays,
  Case
    When
      DateDiff(day, Max(Convert(datetime,tblQuickFixEngineering.InstalledOn)),
      GetDate()) Between 0 And 7 Then '#6efa72'
    When
      DateDiff(day, Max(Convert(datetime,tblQuickFixEngineering.InstalledOn)),
      GetDate()) Between 7 And 12 Then '#77ea6b'
    When
      DateDiff(day, Max(Convert(datetime,tblQuickFixEngineering.InstalledOn)),
      GetDate()) Between 12 And 16 Then '#80d965'
    When
      DateDiff(day, Max(Convert(datetime,tblQuickFixEngineering.InstalledOn)),
      GetDate()) Between 16 And 20 Then '#89c95e'
    When
      DateDiff(day, Max(Convert(datetime,tblQuickFixEngineering.InstalledOn)),
      GetDate()) Between 20 And 24 Then '#93b958'
    When
      DateDiff(day, Max(Convert(datetime,tblQuickFixEngineering.InstalledOn)),
      GetDate()) Between 24 And 28 Then '#9ca951'
    When
      DateDiff(day, Max(Convert(datetime,tblQuickFixEngineering.InstalledOn)),
      GetDate()) Between 28 And 32 Then '#a5984b'
    When
      DateDiff(day, Max(Convert(datetime,tblQuickFixEngineering.InstalledOn)),
      GetDate()) Between 32 And 36 Then '#ae8844'
    When
      DateDiff(day, Max(Convert(datetime,tblQuickFixEngineering.InstalledOn)),
      GetDate()) Between 36 And 40 Then '#b7783d'
    When
      DateDiff(day, Max(Convert(datetime,tblQuickFixEngineering.InstalledOn)),
      GetDate()) Between 40 And 44 Then '#c06737'
    When
      DateDiff(day, Max(Convert(datetime,tblQuickFixEngineering.InstalledOn)),
      GetDate()) Between 44 And 48 Then '#c95730'
    When
      DateDiff(day, Max(Convert(datetime,tblQuickFixEngineering.InstalledOn)),
      GetDate()) Between 48 And 52 Then '#d3472a'
    When
      DateDiff(day, Max(Convert(datetime,tblQuickFixEngineering.InstalledOn)),
      GetDate()) Between 52 And 56 Then '#dc3723'
    When
      DateDiff(day, Max(Convert(datetime,tblQuickFixEngineering.InstalledOn)),
      GetDate()) Between 56 And 60 Then '#e5261d'
    Else '#f7caca'
  End As backgroundcolor
From tblQuickFixEngineering
  Join tblAssets On tblAssets.AssetID = tblQuickFixEngineering.AssetID
  Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblAssets.Lastseen > GetDate() - 2
Group By tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tsysOS.OSname,
  tblAssets.Username,
  tblAssets.IPAddress,
  tblAssets.Description,
  tblAssets.Lastseen,
  tblAssets.Uptime
Order By DaysSincePatched
4 REPLIES 4
Elvar_Ólafsson
Engaged Sweeper

‎07-15-2021 05:14 PM

Thanks , works great
0 Kudos
ldockery
Engaged Sweeper II

‎03-30-2021 08:56 PM

Generates this error:

The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.
0 Kudos
Madrilleno
Engaged Sweeper II
In response to ldockery

‎07-27-2021 03:24 PM

ldockery wrote:
Generates this error:

The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.


Same error for me.

0 Kudos
bcalluy
Engaged Sweeper II
In response to Madrilleno

‎10-06-2021 01:54 PM

Madrilleno wrote:
ldockery wrote:
Generates this error:

The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.


Same error for me.



Found the solution in another topic, try replacing "datetime" everywhere in the query to "datetime2".
This fixed it for me.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tsysOS.OSname,
  tblAssets.Username As LastUserLogin,
  tblAssets.IPAddress,
  tblAssets.Description,
  DateDiff(day, Max(Convert(datetime2,tblAssets.Lastseen)), GetDate()) As
  DaysSinceSeen,
  DateDiff(day, Max(Convert(datetime2,tblQuickFixEngineering.InstalledOn)),
  GetDate()) As DaysSincePatched,
  Convert(Decimal(12,0),tblAssets.Uptime / 86400) As UptimeDays,
  Case
    When
      DateDiff(day, Max(Convert(datetime2,tblQuickFixEngineering.InstalledOn)),
      GetDate()) Between 0 And 7 Then '#6efa72'
    When
      DateDiff(day, Max(Convert(datetime2,tblQuickFixEngineering.InstalledOn)),
      GetDate()) Between 7 And 12 Then '#77ea6b'
    When
      DateDiff(day, Max(Convert(datetime2,tblQuickFixEngineering.InstalledOn)),
      GetDate()) Between 12 And 16 Then '#80d965'
    When
      DateDiff(day, Max(Convert(datetime2,tblQuickFixEngineering.InstalledOn)),
      GetDate()) Between 16 And 20 Then '#89c95e'
    When
      DateDiff(day, Max(Convert(datetime2,tblQuickFixEngineering.InstalledOn)),
      GetDate()) Between 20 And 24 Then '#93b958'
    When
      DateDiff(day, Max(Convert(datetime2,tblQuickFixEngineering.InstalledOn)),
      GetDate()) Between 24 And 28 Then '#9ca951'
    When
      DateDiff(day, Max(Convert(datetime2,tblQuickFixEngineering.InstalledOn)),
      GetDate()) Between 28 And 32 Then '#a5984b'
    When
      DateDiff(day, Max(Convert(datetime2,tblQuickFixEngineering.InstalledOn)),
      GetDate()) Between 32 And 36 Then '#ae8844'
    When
      DateDiff(day, Max(Convert(datetime2,tblQuickFixEngineering.InstalledOn)),
      GetDate()) Between 36 And 40 Then '#b7783d'
    When
      DateDiff(day, Max(Convert(datetime2,tblQuickFixEngineering.InstalledOn)),
      GetDate()) Between 40 And 44 Then '#c06737'
    When
      DateDiff(day, Max(Convert(datetime2,tblQuickFixEngineering.InstalledOn)),
      GetDate()) Between 44 And 48 Then '#c95730'
    When
      DateDiff(day, Max(Convert(datetime2,tblQuickFixEngineering.InstalledOn)),
      GetDate()) Between 48 And 52 Then '#d3472a'
    When
      DateDiff(day, Max(Convert(datetime2,tblQuickFixEngineering.InstalledOn)),
      GetDate()) Between 52 And 56 Then '#dc3723'
    When
      DateDiff(day, Max(Convert(datetime2,tblQuickFixEngineering.InstalledOn)),
      GetDate()) Between 56 And 60 Then '#e5261d'
    Else '#f7caca'
  End As backgroundcolor
From tblQuickFixEngineering
  Join tblAssets On tblAssets.AssetID = tblQuickFixEngineering.AssetID
  Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblAssets.Lastseen > GetDate() - 2
Group By tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tsysOS.OSname,
  tblAssets.Username,
  tblAssets.IPAddress,
  tblAssets.Description,
  tblAssets.Lastseen,
  tblAssets.Uptime
Order By DaysSincePatched

New to Lansweeper?

Try Lansweeper For Free

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

Try Now