Query to list the workstations which were not scanned in the last 60 days

Query to list the workstations which were not scanned in the last 60 days

The below MySQL query can used to get the list of workstations and servers which were not scanned successfully in the last 60 days.


SELECT SystemInfo.WORKSTATIONNAME "Device Name", OsInfo.OSNAME "Operating System",LONGTODATE(AuditHistory.AUDITTIME) "AuditTime",AdventNetErrorCode.ERRORMESSAGE "ErrorMessage", SDOrganization.NAME "Site" FROM SystemInfo LEFT JOIN OsInfo ON SystemInfo.WORKSTATIONID=OsInfo.WORKSTATIONID LEFT JOIN LastAuditInfo ON SystemInfo.WORKSTATIONID=LastAuditInfo.WORKSTATIONID LEFT JOIN AuditHistory ON LastAuditInfo.LAST_AUDITID=AuditHistory.AUDITID LEFT JOIN AuditFailureInfo ON AuditHistory.AUDITID=AuditFailureInfo.AUDITID LEFT JOIN AdventNetErrorCode ON AuditFailureInfo.ERRORCODE=AdventNetErrorCode.ERRORCODE LEFT JOIN Resources ON SystemInfo.WORKSTATIONID=Resources.RESOURCEID LEFT JOIN ComponentDefinitionLaptop ON Resources.COMPONENTID=ComponentDefinitionLaptop.COMPONENTID LEFT JOIN AuditHistory LASTSUCCESSAUDIT ON LastAuditInfo.LAST_SUCCESS_AUDITID=LASTSUCCESSAUDIT.AUDITID LEFT JOIN ResourceLocation ON Resources.RESOURCEID=ResourceLocation.RESOURCEID LEFT JOIN SiteDefinition ON ResourceLocation.SITEID=SiteDefinition.SITEID LEFT JOIN SDOrganization ON SiteDefinition.SITEID=SDOrganization.ORG_ID WHERE  (((LastAuditInfo.LAST_SUCCESS_AUDITID IS NULL) OR (TO_DAYS(CURDATE())-TO_DAYS(FROM_UNIXTIME(LASTSUCCESSAUDIT.AUDITTIME/1000))>60)) AND (Resources.RESOURCESTATEID NOT IN (4,5)))

Regards,
Balaguru.


              New to ADManager Plus?

                New to ADSelfService Plus?