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
New to ADSelfService Plus?
Related Articles
Uninstalling agent 1.0.28
There is a known issue with the uninstalling of agent version 1.0.28 The issue occurs if you try to uninstall the given version from Agent Installed list view post SDP 9420 and AE 6205. How to confirm the issue: 1. Go to Assets > Agent Installed. 2. ...Query to show workstation, agent installed status and domain name (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) PGSQL & MSSQL: select si.workstationname "Workstation",max(case when si.isagentinstalled = '0' then 'NO' else 'SI' end) "Installato", max(ai.agentversion) "VERSIONE AGENTE", di.domainname "Domain Name" ...How does Agent Scan work ?
When you install Agent for the first time, It will initiate the scan immediately and send the data via application port (8080 or 443 as you configured) to Server. But when you scan from the Application, It will first try to check whether the client ...Inventory method when another UEM product exists
When the user is using another ManageEngine Product that belongs to the UEM Family, they would have already deployed the UEM Agent, that we had now bundled with ServiceDesk Plus. With that being said, this KB deals with 3 cases - 1. The customer ...Agent scan understanding
Agent scan in ServiceDesk plus happens in two way 1) Server to target machine 2) Agent from Machine to Server MODE 1: 1) Server to target machine -This scan happens when you login to ServiceDesk plus \ Asset explorer from your workstation or ...