Query to show software and its version along with workstations/Servers (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306)
SELECT SystemInfo.WORKSTATIONNAME "Workstation",MAX(aaaUser.FIRST_NAME) AS "User", MAX(Systeminfo.MODEL) AS "Model",
(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 Resources resource ON Systeminfo.WORKSTATIONID=resource.RESOURCEID
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 SoftwareManufacturer swmfg ON SoftwareList.SWMANUFACTURERID=swmfg.SWMANUFACTURERID
WHERE SoftwareList.SOFTWARENAME like '%microsoft%' GROUP BY SystemInfo.WORKSTATIONNAME,SoftwareList.SOFTWARENAME
ORDER BY 1
SELECT SystemInfo.WORKSTATIONNAME "Asset Name",SystemInfo.WORKSTATIONNAME "Asset Name",
(SoftwareList.SOFTWARENAME) "Software Name",
SoftwareInfo.fileversion "Version",
LONGTODATE(SoftwareInfo.filecreatedtime) "Installed On",
MAX(softcat.softwarecategory) "Software Category" 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 softwarecategory softcat on softcat.softwarecategoryid=softwarelist.softwarecategoryid
LEFT JOIN SoftwareManufacturer swmfg ON SoftwareList.SWMANUFACTURERID=swmfg.SWMANUFACTURERID
GROUP BY SystemInfo.WORKSTATIONNAME,SoftwareList.SOFTWARENAME, softwareinfo.fileversion, softwareinfo.filecreatedtime ORDER BY 1
New to ADSelfService Plus?
Related Articles
Query to show workstations that has a scan status (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) PGSQL & MSSQL: Below query that will show the Assets that has a scan status. select systeminfo.workstationname "Workstation Name", LONGTODATE(audithistory.audittime) "Last Scanned on", ...Query to show workstation additional fields (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) SELECT workstation.WORKSTATIONNAME AS "Machine Name", workstation.MANUFACTURER AS "Manufacturer", workstation.SERVICETAG AS "Service Tag", workstation.MODEL AS "Model", aao.NAME AS "Vendor Name", ...Query to show workstation details.(MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) Below query will show Only workstations and its details. SELECT Max(workstation.workstationname) "Workstation", Max(workstation.servicetag) "Service Tag", Max(workstation.model) "Model", ...Query to show workstation's hard disk details (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) SELECT Max(workstation.workstationname) "Workstation", Max(workstation.servicetag) "Service Tag", Max(workstation.model) "Model", Max(osinfo.osname) "OS", MAX(memInfo.TOTALMEMORY/(1024*1024*1024)) AS ...Query to show request details along with technician's and requester's department (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) Along with technician's department: SELECT wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester", wo.TITLE AS "Subject", cd.CATEGORYNAME AS "Category", scd.NAME AS "Subcategory", qd.QUEUENAME AS ...