List of workstations without agent installed.

List of workstations without agent installed.


For Versions 11.2 and below: 

SELECT MAX(workstation.WORKSTATIONNAME) "Workstation Name", MAX(net.IPADDRESS) "IP Address", MAX(aaov.NAME) "Site", MAX(aaaUser.FIRST_NAME) "User", MAX(workstation.LOGGEDUSER) "Last Logged In User", LONGTODATE(MAX(LASTSUCCESSAUDIT.AUDITTIME)) "Last Success scan" FROM SystemInfo workstation LEFT JOIN NetworkInfo net ON workstation.WORKSTATIONID=net.WORKSTATIONID LEFT JOIN Resources RESOURCE ON workstation.WORKSTATIONID=resource.RESOURCEID LEFT JOIN ResourceOwner rOwner ON resource.RESOURCEID=rOwner.RESOURCEID LEFT JOIN ResourceLocation resLocation ON resource.RESOURCEID=resLocation.RESOURCEID LEFT JOIN SiteDefinition siteDef ON resLocation.SITEID=siteDef.SITEID LEFT JOIN SDOrganization aaov ON siteDef.SITEID=aaov.ORG_ID LEFT JOIN SDUser sdUser ON rOwner.USERID=sdUser.USERID LEFT JOIN AaaUser aaaUser ON sdUser.USERID=aaaUser.USER_ID LEFT JOIN LastAuditInfo ON workstation.WORKSTATIONID=LastAuditInfo.WORKSTATIONID LEFT JOIN AuditHistory ON LastAuditInfo.LAST_AUDITID=AuditHistory.AUDITID LEFT JOIN AuditFailureInfo ON AuditHistory.AUDITID=AuditFailureInfo.AUDITID LEFT JOIN AuditHistory LASTSUCCESSAUDIT ON LastAuditInfo.LAST_SUCCESS_AUDITID=LASTSUCCESSAUDIT.AUDITID LEFT JOIN AgentInfo ON workstation.WORKSTATIONID=AgentInfo.WORKSTATIONID WHERE (workstation.ISAGENTINSTALLED = '0') GROUP BY workstation.WORKSTATIONID


For version 11.3 and above

SELECT MAX(SystemInfo.WORKSTATIONNAME) "Workstation",MAX(OsInfo.OSNAME) "OS", MAX(net.IPADDRESS) "Ip Address", MAX(net.MACADDRESS) "Mac Address",MAX(SystemInfo.LOGGEDUSER) "Last Logged In User",
MAX(aaaUser.FIRST_NAME) "User", 
MAX(SDOrganization.NAME) "Site",
LONGTODATE(MAX(ah.audittime)) as "Last Scanned On",
MAX(ah.auditstatus) as "Last Scan status",
MAX(ah.comments) as "Last Scan Message",
MAX(dct.typename) as "Last Scan Type",
MAX(dcm.typename) as "Last Scan Mode",
LONGTODATE( MAX(LASTSUCCESSAUDIT.audittime)) as "Last Success Scan",
( MAX(LASTSUCCESSAUDIT.comments)) as "Last Success Scan Message",
MAX(sdct.typename) as "Last Success Scan Type",
MAX(sdcm.typename) as "Last Success Scan Mode"  FROM SystemInfo 
LEFT JOIN OsInfo ON SystemInfo.WORKSTATIONID=OsInfo.WORKSTATIONID 
LEFT JOIN Resources ON SystemInfo.WORKSTATIONID=Resources.RESOURCEID 
LEFT JOIN ResourceLocation ON SystemInfo.WORKSTATIONID=ResourceLocation.RESOURCEID 
LEFT JOIN SiteDefinition ON ResourceLocation.SITEID=SiteDefinition.SITEID 
LEFT JOIN SDOrganization ON SiteDefinition.SITEID=SDOrganization.ORG_ID 
LEFT JOIN ResourceOwner rOwner ON resources.RESOURCEID=rOwner.RESOURCEID 
LEFT JOIN ResourceAssociation rToAsset ON rOwner.RESOURCEOWNERID=rToAsset.RESOURCEOWNERID 
LEFT JOIN SDUser sdUser ON rOwner.USERID=sdUser.USERID 
LEFT JOIN AaaUser aaaUser ON sdUser.USERID=aaaUser.USER_ID 
LEFT JOIN NetWorkInfo net ON SystemInfo.WORKSTATIONID=net.WORKSTATIONID 
LEFT JOIN ResourceState state ON resources.RESOURCESTATEID=state.RESOURCESTATEID 
LEFT JOIN lastauditinfo ON resources.resourceid = lastauditinfo.workstationid
LEFT JOIN audithistory ah ON lastauditinfo.last_auditid = ah.auditid
LEFT JOIN audithistory LASTSUCCESSAUDIT ON LastAuditInfo.LAST_SUCCESS_AUDITID=LASTSUCCESSAUDIT.AUDITID 
left join discoverytypeconstants dct on dct.typeid=ah.scantype
left join discoverytypeconstants dcm on dcm.typeid=ah.scanmode
left join discoverytypeconstants sdct on sdct.typeid=LASTSUCCESSAUDIT.scantype
left join discoverytypeconstants sdcm on sdcm.typeid=LASTSUCCESSAUDIT.scanmode
where SystemInfo.WORKSTATIONID not in ( select lastauditinfo2.workstationid from lastauditinfo lastauditinfo2
LEFT JOIN audithistory ah2 ON lastauditinfo2.last_auditid = ah2.auditid
left join discoverytypeconstants sdct2 on sdct2.typeid=ah2.scantype
where sdct2.typename='DC Agent' ) and SystemInfo.WORKSTATIONID not in  ( select si.workstationid from softwareinfo si
left join softwarelist sl on sl.softwareid=si.softwareid
left join systeminfo sy on sy.workstationid=si.workstationid
where ((( sl.softwarename like '%Desktop Central%' ) or ( sl.softwarename like '%DesktopCentral%' )) and ( sl.softwarename like '%Agent%'  )) )
GROUP BY SystemInfo.WORKSTATIONID