Notification

Icon
Error

Report Request - Patching with uptime and object group membership

Posted: Monday, October 25, 2021 8:45:53 PM(UTC)
Brian G

Brian G

Member Original PosterPosts: 4
0
Like
Hey All,
I'm back begging for some more help please, I need to combine two working reports I have into one.
1st report is: Last Patch Installed (which is from the LS Library)
2nd report is: Computer uptime of computers in specific AD groups. (From the LS library with the AD groups added).

I'd like the resulting report to have (All these exist in the two reports I'm including)
Computer name, OSName, IP address, Description, AD group Membership, uptime, last patched, last seen by LS. SORT by last Patched.

I would appreciate very much if I could get help with this, I'm no good at SQL queries, and we dont really have anyone here who is good either unfortunately...


1st Query: Last Patch Install

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,
Case
When Max(Convert(date,tblQuickFixEngineering.InstalledOn)) >= DateAdd(day,
-7, GetDate()) Then '#d4f4be'
When Max(Convert(date,tblQuickFixEngineering.InstalledOn)) < DateAdd(day,
-7, GetDate()) And Max(Convert(date,tblQuickFixEngineering.InstalledOn))
>= DateAdd(day, -30, GetDate()) Then '#f7f0ca'
When Max(Convert(date,tblQuickFixEngineering.InstalledOn)) < DateAdd(day,
-31, GetDate()) And Max(Convert(date,tblQuickFixEngineering.InstalledOn))
>= DateAdd(day, -90, GetDate()) Then '#f2d59f'
Else '#f7caca'
End As backgroundcolor
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 tblAssetCustom.State = 1
Group By tblAssets.AssetID,
tblAssets.AssetName,
tsysOS.OSname,
tblAssets.Username,
tblAssets.IPAddress,
tblAssets.Description,
tblAssets.Lastseen
Order By lastPatchDate




2nd query: Uptime of computers in a specific group

Code:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.IPAddress,
tsysOS.OSname As OS,
tblAssets.Lastseen,
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) / 3600 /
24))) + ' days ' +
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) / 3600 %
24))) + ' hours ' +
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) % 3600 /
60))) + ' minutes' As UptimeSinceLastReboot,
tblADGroups.Name As ADGroupName,
Case tblADGroups.GroupType
When -2147483646 Then 'Security - Global'
When -2147483644 Then 'Security - Local'
When -2147483643 Then 'Built-in'
When -2147483640 Then 'Security - Universal'
When 2 Then 'Distribution - Global'
When 4 Then 'Distribution - Local'
When 8 Then 'Distribution - Universal'
End As ADGroupType
From tblAssets
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Left Join tblADMembership On tblADMembership.ChildAdObjectID =
tblADComputers.ADObjectID
Left Join tblADGroups On tblADMembership.ParentAdObjectID =
tblADGroups.ADObjectID
Where tblADGroups.Name = 'PATCH_Prod_Green_Friday' And tblState.Statename =
'Active' And tblComputersystem.Domainrole > 1
Order By tblAssets.Domain,
tblAssets.AssetName
Hendrik.VE
#1Hendrik.VE Member Posts: 93  
posted: 10/27/2021 11:12:50 AM(UTC)
I use this one, which combines the last KB and the uptime for my systems.

Code:
Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysOS.OSname,
  Replace(Replace(tblAssets.OScode, 'S', ''), 'R', '') + '.' + tRegUBR.Value As
  Build,
  tblAssets.IPAddress,
  tblAssetCustom.Custom12 As Beschrijving,
  Max(Convert(date,tblQuickFixEngineering.InstalledOn)) As lastPatchDate,
  Max(tblQuickFixEngineeringUni.HotFixID) As lastPatch,
  tblAssets.Lastseen,
  Case
    When Max(Convert(date,tblQuickFixEngineering.InstalledOn)) >= DateAdd(day,
      -31, GetDate()) Then '#d4f4be'
    When Max(Convert(date,tblQuickFixEngineering.InstalledOn)) < DateAdd(day,
      -31, GetDate()) And Max(Convert(date,tblQuickFixEngineering.InstalledOn))
      >= DateAdd(day, -90, GetDate()) Then '#f2d59f'
    Else '#f7caca'
  End As backgroundcolor,
  Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) / 3600 /
  24))) + ' days ' +
  Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) / 3600 %
  24))) + ' hours ' +
  Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) % 3600 /
  60))) + ' minutes' As UptimeSinceLastReboot,
  tblAssetCustom.Custom20 As 'SCCM Update Window'
From tblQuickFixEngineering
  Join tblAssets On tblAssets.AssetID = tblQuickFixEngineering.AssetID
  Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID =
    tblQuickFixEngineering.QFEID
  Left Join (Select tblRegistry.AssetID,
        tblRegistry.Value,
        tblRegistry.Valuename,
        tblRegistry.Lastchanged
      From tblRegistry
      Where tblRegistry.Valuename Like '%UBR%' And
        tblRegistry.Regkey Like '%CurrentVersion%') As tRegUBR On
    tblAssets.AssetID = tRegUBR.AssetID
Where tblState.Statename = 'Active'
Group By tblAssets.AssetID,
  tblAssets.AssetName,
  tsysOS.OSname,
  tblAssets.IPAddress,
  tblAssetCustom.Custom12,
  tblAssets.Lastseen,
  tblAssetCustom.Custom20,
  tRegUBR.Value,
  tblAssets.OScode,
  tblAssets.Username,
  tblAssets.Uptime,
  tblAssets.Description
Order By lastPatchDate

Active Discussions

Lansweeper ManageEngine ADSelfService Plus Vulnerability
by  TheGift73   Go to last post Go to first unread
Last post: 11/30/2021 10:25:01 AM(UTC)
Lansweeper OS: Not latest Build of Windows 10 not working
by  Luke Maslany  
Go to last post Go to first unread
Last post: 11/29/2021 2:01:49 PM(UTC)
Lansweeper Cannot find right class
by  FabioB   Go to last post Go to first unread
Last post: 11/29/2021 8:16:33 AM(UTC)
Lansweeper vCenter vSphere Web Client Vulnerabilities
by  Esben.D  
Go to last post Go to first unread
Last post: 11/25/2021 12:24:24 PM(UTC)
Lansweeper Report for Hardware Maintenance
by  PBjelly   Go to last post Go to first unread
Last post: 11/23/2021 2:53:33 PM(UTC)
Lansweeper Need a little help here
by  AMcCarron  
Go to last post Go to first unread
Last post: 11/18/2021 3:07:25 PM(UTC)
Lansweeper Solftware Installed by member of AD Group
by  RPZ   Go to last post Go to first unread
Last post: 11/17/2021 9:57:42 PM(UTC)
Lansweeper Uptime Report duplicate powerstates
by  INNO-IT  
Go to last post Go to first unread
Last post: 11/16/2021 5:10:23 PM(UTC)