SQL Report

SQL Report

Can someone assist me with a report?  I have a base report that I got from these forums (thank you!), but I need to add a column for 'Asset State' (because AE scans items still in a state of 'In Store' and I need to see at a glance which ones I need to look at and which simply aren't on the network to scan). 

SELECT MAX(SystemInfo.WORKSTATIONNAME) "Workstation",MAX(OsInfo.OSNAME) "OS", MAX(net.IPADDRESS) "Ip Address", MAX(net.MACADDRESS) "Mac Address",LONGTODATE(MAX(AuditHistory.AUDITTIME)) "AuditTime",(MAX(AuditHistory.AUDITSTATUS)) "Audit Status",MAX(SystemInfo.LOGGEDUSER) "Last Logged In User",MAX(aaaUser.FIRST_NAME) "User", MAX(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 Resources ON SystemInfo.WORKSTATIONID=Resources.RESOURCEID  LEFT JOIN AuditHistory LASTSUCCESSAUDIT ON LastAuditInfo.LAST_SUCCESS_AUDITID=LASTSUCCESSAUDIT.AUDITID 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 WHERE  ((((AuditHistory.AUDITSTATUS) = ('FAILED')) OR ((AuditHistory.AUDITSTATUS) IS NULL)) AND (Resources.RESOURCESTATEID NOT IN (4,5)))  GROUP BY SystemInfo.WORKSTATIONID

I need the 'Asset State' added (In Store, In Use, etc.)  I think this will require another JOIN statement, but that's a bit over my current SQL knowledge and familiarity with this database.

      New to ADSelfService Plus?


                Related Products