Query to show software and its version along with workstations/Servers

Query to show software and its version along with workstations/Servers

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
          • Related Articles

          • Query to show request details along with technician's department

            PGSQL & MSSQL: 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 "Group",ti.FIRST_NAME AS "Technician", dpt.DEPTNAME AS " Technician ...
          • Query to show Software license details with additional fields, allocated workstations and its users with email address

            PGSQL & MSSQL: SELECT resources.resourcename "License Name", Max(softwarelist.softwarename) "Managed Software", slt.licensetype "License Type", Max(softwarelicenses.licensekey) "License Key", softwarelicenses.installationscount "No of CALs", ...
          • Query to show workstations that has a scan status

            PGSQ & MSSQL: Below query that will show the Assets that has a scan status. select systeminfo.workstationname "Workstation Name", LONGTODATE(audithistory.audittime) "Last Scanned on", audithistory.auditstatus "Scan status" from audithistory left join ...
          • Query to show software license details

            PGSQL & MSSQL: SELECT resources.resourcename "License Name", Max(softwarelist.softwarename) "Software_Name", LONGTODATE(Max(resources.acquisitiondate)) "Acquisition Date",LONGTODATE(Max(resources.expirydate)) "Expiry Date", ...
          • Query to show workstation details. PGSQL & MSSQL

            Below query will show Only workstations and its details.  SELECT Max(workstation.workstationname) "Workstation",         Max(workstation.servicetag)      "Service Tag",        Max(workstation.model)           "Model",         Max(osinfo.osname)      ...