‎10-25-2021 09:45 PM
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
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
‎10-27-2021 12:12 PM
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
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now