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


      New to ADSelfService Plus?

        Resources

            • 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. ...
            • 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 ...
            • How to uninstall agent from the client machines

              The attached one here will uninstall the agent from the client machine. Note:- Rename it as uninstallagent.vbs
            • How to access Solutions without logging into ServiceDesk Plus

              Users can check out solutions without having to log into the ServiceDesk application.        This option can be enabled from the self-service portal settings under Admin -> Self-service portal settings -> Allow Non-login users to view solutions Now ...
            • List of mail fetching issues and solutions

              Scenario 1 : Mail fetching issue due to Invalid or No PKIX certificate .   Log traces for Mail Fetching  issues Exception when connecting to store.|javax.mail.MessagingException: sun.security.validator.ValidatorException: PKIX path building failed: ...