Pro-Tips 57 Consolidated Compliance Report
Miscellaneous Scanning- This is an example Pro-Tip report and therefore might not apply to your specific environment. However, you can adapt the query to your own needs.
Use this On-Premise Report Query to keep on top of the Pro-Tips #57 Consolidated Custom Compliance Report, which checks for Internet Explorer (File), LSAgent (Software), and the Windows Defender (Service), for Windows Servers.
Run the LS Sites Windows Compliance Pro-Tip Report
Pro Tips 57 Custom Consolidated Compliance Lansweeper On-Prem Query
Select Top 1000000 Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As
icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.IPAddress,
tbladcomputers.OU,
Case
When hasinternetexplorer.Found = 1 Then 'Yes'
Else 'No'
End As hasinternetexplorer,
Case
When lsagentcheck.AssetID Is Null Then 'No'
Else 'Yes'
End As hasLsAgent,
Case
When checkfordefender.AssetID Is Null Then 'No'
Else 'Yes'
End As [Has Defender Service],
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried,
Case
When (tblErrors.ErrorText Is Not Null Or
tblErrors.ErrorText != '') And tsysasseterrortypes.ErrorMsg Not Like
'%PrinterSpoolDisabledError%' Then 'Scanning Error: ' +
tsysasseterrortypes.ErrorMsg
Else ''
End As ScanningErrors,
Case
When (tblErrors.ErrorText Is Not Null Or
tblErrors.ErrorText != '') And tsysasseterrortypes.ErrorMsg Not Like
'%PrinterSpoolDisabledError%' Then '#ffadad'
Else '#ffffff'
End As backgroundcolor,
tblAssetCustom.Comments
From tblComputersystem
Inner Join tblAssets On tblComputersystem.AssetID = tblAssets.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Left Join tbladcomputers On tblAssets.AssetID = tbladcomputers.AssetID
Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
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 (Select Distinct Top 1000000 tblErrors.AssetID As ID,
Max(tblErrors.Teller) As ErrorID
From tblErrors
Group By tblErrors.AssetID) As ScanningError On tblAssets.AssetID =
ScanningError.ID
Left Join tblErrors On ScanningError.ErrorID = tblErrors.Teller
Left Join tsysasseterrortypes On tsysasseterrortypes.Errortype =
tblErrors.ErrorType
Left Join (Select Distinct tblAssets.AssetID
From tblAssets
Inner Join tblVmwareGuest On tblAssets.AssetID = tblVmwareGuest.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType =
tblAssets.Assettype
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Left Join tblVmwareInfo On tblVmwareGuest.HostID = tblVmwareInfo.VmwareID
Left Join tblAssets tblAssets1 On tblAssets1.AssetID =
tblVmwareInfo.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Inner Join tblVmwareGuestSnapShots On tblVmwareGuest.GuestID =
tblVmwareGuestSnapShots.GuestID) As hassnapshot On tblAssets.AssetID =
hassnapshot.AssetID
Left Join (Select Top 1000000 tblServices.AssetID
From tblServices
Inner Join tblServicesUni On tblServicesUni.ServiceuniqueID =
tblServices.ServiceuniqueID
Where tblServicesUni.Caption = 'Windows Defender Antivirus Service') As
checkfordefender On tblAssets.AssetID = checkfordefender.AssetID
Left Join (Select Distinct tblSoftware.AssetID,
tblSoftware.softwareVersion
From tblSoftware
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.SoftID
Where tblSoftwareUni.softwareName Like '%LsAgent%') As lsagentcheck On
tblAssets.AssetID = lsagentcheck.AssetID
Left Join (Select tblFileVersions.AssetID,
tblFileVersions.Found,
tblFileVersions.FilePathfull,
tblFileVersions.FileVersion,
tblFileVersions.CompanyName,
tblFileVersions.Filesize,
tblFileVersions.Lastchanged
From tblFileVersions
Where tblFileVersions.FilePathfull Like '%iexplore.exe%')
hasinternetexplorer On tblAssets.AssetID = hasinternetexplorer.AssetID
Where tsysOS.OSname Like '%win 2%' And tblAssetCustom.State = 1
Order By tblAssets.Domain,
tblAssets.AssetName