Notification

Icon
Error

Assets Missing Quickfix Data - Last 60 Days

Posted: Friday, November 8, 2019 10:16:48 PM(UTC)
Mike6436

Mike6436

Member Original PosterPosts: 34
1
Like
The code below displays assets that haven't received patches (quickfixes) in the last 60 days. However, assets that haven't received any patches are missing. How can I include the logic 'tblQuickFixEngineering.InstalledOn = NULL' to the query below?

Code:
Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysOS.OSname,
  tblAssets.Username,
  tblAssets.IPAddress,
  tblAssets.Description,
  Max(Convert(date,tblQuickFixEngineering.InstalledOn)) As lastPatchDate,
  tblAssets.Lastseen
From tblQuickFixEngineering
  Join tblAssets On tblAssets.AssetID = tblQuickFixEngineering.AssetID
  Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tsysOS.OSname Not In ('') And IsDate(tblQuickFixEngineering.InstalledOn) =
  1
Group By tblAssets.AssetID,
  tblAssets.AssetName,
  tsysOS.OSname,
  tblAssets.Username,
  tblAssets.IPAddress,
  tblAssets.Description,
  tblAssets.Lastseen,
  IsDate(tblQuickFixEngineering.InstalledOn)
Having Max(Convert(date,tblQuickFixEngineering.InstalledOn)) < GetDate() - 60
Order By lastPatchDate Desc
Mike6436
#1Mike6436 Member Original PosterPosts: 34  
posted: 11/11/2019 6:11:30 PM(UTC)
I'm also having a hell of a time trying to build a chart off of OSname.

I tried the following but the data is definitely wrong. Suggestions?

Code:
Select Top 1000000 tsysOS.OSname,
  Count(tblAssets.AssetID) As Total
From tblQuickFixEngineering
  Join tblAssets On tblAssets.AssetID = tblQuickFixEngineering.AssetID
  Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Where tsysOS.OSname Not In ('') And tblAssetCustom.State = 1 And
  IsDate(tblQuickFixEngineering.InstalledOn) = 1
Group By tsysOS.OSname
Having Max(Convert(date,tblQuickFixEngineering.InstalledOn)) < GetDate() - 60
Order By Total Desc

Active Discussions

Report Center Calculate your network's computing power
by  GBInnovation   Go to last post Go to first unread
Last post: Yesterday at 12:11:18 PM(UTC)
Lansweeper Custom All Assets Report Help
by  Matt Fuhrman  
Go to last post Go to first unread
Last post: 11/12/2019 11:13:54 PM(UTC)
Lansweeper Patch Tuesday report, last 3 months
by  JacobH   Go to last post Go to first unread
Last post: 11/12/2019 5:28:37 PM(UTC)
Lansweeper Report ALL office (ms officce,libreoffice,openoffice
by  AlexRus227  
Go to last post Go to first unread
Last post: 11/12/2019 8:37:07 AM(UTC)
Lansweeper Assets Missing Quickfix Data - Last 60 Days
by  Mike6436   Go to last post Go to first unread
Last post: 11/11/2019 6:11:30 PM(UTC)
Lansweeper BlueKeep Vulnerability
by  jwood.mls  
Go to last post Go to first unread
Last post: 11/11/2019 4:58:59 PM(UTC)
Lansweeper tblCPlogoninfo - None interactive logons?
by  Millslansweeper   Go to last post Go to first unread
Last post: 11/8/2019 1:32:12 PM(UTC)
Lansweeper Software : changes last 24 hours Enhancement
by  RC62N  
Go to last post Go to first unread
Last post: 11/7/2019 10:05:53 PM(UTC)