Query to show software license details (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306)
PGSQL & MSSQL:
SELECT resources.resourcename "License Name", Max(softwarelist.softwarename) "Software_Name", LONGTODATE(Max(resources.acquisitiondate)) "Acquisition Date",LONGTODATE(Max(resources.expirydate)) "Expiry Date", Max(swinstallationkeyinfo.productid) "Software_ID", Max(swinstallationkeyinfo.productkey) "Software_Key",sdorganization.name "Site" FROM softwarelist LEFT JOIN softwareinfo ON softwarelist.softwareid = softwareinfo.softwareid LEFT JOIN swinstallationkeyinfo ON softwareinfo.softwareinfoid = swinstallationkeyinfo.softwareinfoid LEFT JOIN componentdefinitionsoftware ON softwarelist.softwareid = componentdefinitionsoftware.softwareid LEFT JOIN resources ON componentdefinitionsoftware.componentid = resources.componentid LEFT JOIN softwarelicenses ON resources.resourceid = softwarelicenses.resourceid LEFT join RESOURCELOCATION on softwarelicenses.RESOURCEID=RESOURCELOCATION.RESOURCEID LEFT JOIN sitedefinition on RESOURCELOCATION.siteid=sitedefinition.siteid left join sdorganization on sitedefinition.siteid=sdorganization.org_id GROUP BY resources.resourcename,sdorganization.name
ORDER BY 1
Below query shows "Expires in" Column but works only in MSSQL:
SELECT resources.resourcename "License Name", Max(softwarelist.softwarename) "Software_Name", LONGTODATE(Max(resources.acquisitiondate)) "Acquisition Date",LONGTODATE(Max(resources.expirydate)) "Expiry Date", Max(swinstallationkeyinfo.productid) "Software_ID", Max(swinstallationkeyinfo.productkey) "Software_Key",sdorganization.name "Site",((max(resources.expirydate)/1000)-DATEDIFF(s,'19700101',GETDATE()))/60/60/24 "Expires in days" FROM softwarelist LEFT JOIN softwareinfo ON softwarelist.softwareid = softwareinfo.softwareid LEFT JOIN swinstallationkeyinfo ON softwareinfo.softwareinfoid = swinstallationkeyinfo.softwareinfoid LEFT JOIN componentdefinitionsoftware ON softwarelist.softwareid = componentdefinitionsoftware.softwareid LEFT JOIN resources ON componentdefinitionsoftware.componentid = resources.componentid LEFT JOIN softwarelicenses ON resources.resourceid = softwarelicenses.resourceid LEFT join RESOURCELOCATION on softwarelicenses.RESOURCEID=RESOURCELOCATION.RESOURCEID LEFT JOIN sitedefinition on RESOURCELOCATION.siteid=sitedefinition.siteid left join sdorganization on sitedefinition.siteid=sdorganization.org_id GROUP BY resources.resourcename,sdorganization.name
ORDER BY 4
New to ADSelfService Plus?
Related Articles
Query to show Average response time for Category (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) PGSQL: SELECT accountdefinition.org_name "Account",cd.categoryname "Category", TO_CHAR(((avg(wo.respondedtime)-avg(wo.createdtime))/1000 || ' second')::interval, 'HH24:MI:SS') "Avg Response Time" FROM ...Query to show list of softwares with last scanned date and license key (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) select systeminfo.workstationname "Workstation Name", SoftwareList.SOFTWARENAME "Software Name", LONGTODATE(SoftwareInfo.filecreatedtime) "Software Installed Date", LONGTODATE(audithistory.audittime) ...Query to extract the “Software-License Additional Fields” along with the existing additional fields (PGSQL)
(Applicable for builds 14300 & above) Below query has been modified to add a LEFT JOIN between the additional fields table and the table from which you extracted the softwares: QUERY: SELECT SUM(scd.ALLOCATED) AS "Allocated", ...Query for request attachment details (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) Requests with Attachment, its name and path SELECT ad.ORG_NAME AS "Account", wo.WORKORDERID AS "Request ID", wo.TITLE AS "Subject", ti.FIRST_NAME AS "Technician", sa.ATTACHMENTNAME "Attachment ...Query to show Request and associated task details (PGSQL & MSSQL)
Tested in build PGSQL (14300) and MSSQL (14306) PGSQL & MSSQL: SELECT wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester", wo.TITLE AS "Subject", pd.PRIORITYNAME AS "Priority", cd.CATEGORYNAME AS "Category", scd.NAME AS "Subcategory", ...