Query - Search for instances of software that includes job title
Hi,
I need help improving the query I use to search for users with a particular software. I am need of it to include their job title and department.
Thank you for the help!
Here is what I currently use, I would like to add a "Department" and "Job Title" line as well:
SELECT SystemInfo.WORKSTATIONNAME "Workstation",
(SoftwareList.SOFTWARENAME) "Software_Name",
MAX(swmfg.NAME) "Manufacturer",
MAX(SoftwareInfo.FILEVERSION) "Version" FROM SoftwareInfo
LEFT JOIN SoftwareList ON SoftwareInfo.SOFTWAREID=SoftwareList.SOFTWAREID
LEFT JOIN SoftwareType ON SoftwareList.SOFTWARETYPEID=SoftwareType.SOFTWARETYPEID
LEFT JOIN Systeminfo ON SoftwareInfo.WORKSTATIONID=SystemInfo.WORKSTATIONID
LEFT JOIN SoftwareManufacturer swmfg ON SoftwareList.SWMANUFACTURERID=swmfg.SWMANUFACTURERID
WHERE SoftwareList.SOFTWARENAME like '%webex%' GROUP BY SystemInfo.WORKSTATIONNAME,SoftwareList.SOFTWARENAME ORDER BY 2
The below script has department, but I have tried for hours and cannot add "Job Title":
SELECT SystemInfo.WORKSTATIONNAME "Workstation",MAX(aaaUser.FIRST_NAME) "User",MAX(deptDef.DEPTNAME) "Department",max(state.DISPLAYSTATE) "Asset State",max(systeminfo.servicetag) "Service Tag", (SoftwareList.SOFTWARENAME) "Software_Name",MAX(SoftwareInfo.FILEVERSION) "File Version",MAX(SWInstallationKeyInfo.PRODUCTKEY) "Software_Key" FROM SoftwareInfo
LEFT JOIN SoftwareList ON SoftwareInfo.SOFTWAREID=SoftwareList.SOFTWAREID
LEFT JOIN SoftwareType ON SoftwareList.SOFTWARETYPEID=SoftwareType.SOFTWARETYPEID
LEFT JOIN SWInstallationKeyInfo ON SoftwareInfo.SOFTWAREINFOID=SWInstallationKeyInfo.SOFTWAREINFOID
LEFT JOIN Systeminfo ON SoftwareInfo.WORKSTATIONID=SystemInfo.WORKSTATIONID
LEFT JOIN Resources resource ON systeminfo.WORKSTATIONID=resource.RESOURCEID
LEFT JOIN ResourceState state ON resource.RESOURCESTATEID=state.RESOURCESTATEID
LEFT JOIN ResourceOwner rOwner ON resource.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 DepartmentDefinition deptDef ON rOwner.DEPTID=deptDef.DEPTID WHERE (SoftwareList.SOFTWARETYPEID = 2) AND SoftwareList.SOFTWARENAME like '%MicroSoft Office%' and state.DISPLAYSTATE in ('In Use','In Store') GROUP BY SystemInfo.WORKSTATIONNAME,SoftwareList.SOFTWARENAME ORDER BY 3
I need help improving the query I use to search for users with a particular software. I am need of it to include their job title and department.
Thank you for the help!
Here is what I currently use, I would like to add a "Department" and "Job Title" line as well:
SELECT SystemInfo.WORKSTATIONNAME "Workstation",
(SoftwareList.SOFTWARENAME) "Software_Name",
MAX(swmfg.NAME) "Manufacturer",
MAX(SoftwareInfo.FILEVERSION) "Version" FROM SoftwareInfo
LEFT JOIN SoftwareList ON SoftwareInfo.SOFTWAREID=SoftwareList.SOFTWAREID
LEFT JOIN SoftwareType ON SoftwareList.SOFTWARETYPEID=SoftwareType.SOFTWARETYPEID
LEFT JOIN Systeminfo ON SoftwareInfo.WORKSTATIONID=SystemInfo.WORKSTATIONID
LEFT JOIN SoftwareManufacturer swmfg ON SoftwareList.SWMANUFACTURERID=swmfg.SWMANUFACTURERID
WHERE SoftwareList.SOFTWARENAME like '%webex%' GROUP BY SystemInfo.WORKSTATIONNAME,SoftwareList.SOFTWARENAME ORDER BY 2
The below script has department, but I have tried for hours and cannot add "Job Title":
SELECT SystemInfo.WORKSTATIONNAME "Workstation",MAX(aaaUser.FIRST_NAME) "User",MAX(deptDef.DEPTNAME) "Department",max(state.DISPLAYSTATE) "Asset State",max(systeminfo.servicetag) "Service Tag", (SoftwareList.SOFTWARENAME) "Software_Name",MAX(SoftwareInfo.FILEVERSION) "File Version",MAX(SWInstallationKeyInfo.PRODUCTKEY) "Software_Key" FROM SoftwareInfo
LEFT JOIN SoftwareList ON SoftwareInfo.SOFTWAREID=SoftwareList.SOFTWAREID
LEFT JOIN SoftwareType ON SoftwareList.SOFTWARETYPEID=SoftwareType.SOFTWARETYPEID
LEFT JOIN SWInstallationKeyInfo ON SoftwareInfo.SOFTWAREINFOID=SWInstallationKeyInfo.SOFTWAREINFOID
LEFT JOIN Systeminfo ON SoftwareInfo.WORKSTATIONID=SystemInfo.WORKSTATIONID
LEFT JOIN Resources resource ON systeminfo.WORKSTATIONID=resource.RESOURCEID
LEFT JOIN ResourceState state ON resource.RESOURCESTATEID=state.RESOURCESTATEID
LEFT JOIN ResourceOwner rOwner ON resource.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 DepartmentDefinition deptDef ON rOwner.DEPTID=deptDef.DEPTID WHERE (SoftwareList.SOFTWARETYPEID = 2) AND SoftwareList.SOFTWARENAME like '%MicroSoft Office%' and state.DISPLAYSTATE in ('In Use','In Store') GROUP BY SystemInfo.WORKSTATIONNAME,SoftwareList.SOFTWARENAME ORDER BY 3