Query to show Software license details with additional fields, allocated workstations and its users with email address

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", MAX(swmfg.NAME) "Manufacturer",  sdorganization.name "Site", softwarelicenses.allocated "Allocated", slf.UDF_CHAR1 "Entity", slf.UDF_CHAR2 "Location", SystemInfo.WORKSTATIONNAME "Workstation", MAX(aaaUser.FIRST_NAME) AS "User", aaacontact.EMAILID AS "E-Mail" 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 LEFT JOIN SoftwareManufacturer swmfg ON SoftwareList.SWMANUFACTURERID=swmfg.SWMANUFACTURERID LEFT JOIN Softwarelicensetypes slt ON softwarelicenses.licensetypeid=slt.licensetypeid 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 AaaUserContactInfo aaausercontact ON aaauser.USER_ID=aaausercontact.USER_ID LEFT JOIN AaaContactInfo aaacontact ON aaausercontact.CONTACTINFO_ID=aaacontact.CONTACTINFO_ID LEFT JOIN ci ci1 ON ci1.ciid=sdUser.ciid LEFT JOIN baseelement be1 ON be1.ciid=ci1.ciid LEFT JOIN people p1 ON p1.ciid=be1.ciid
LEFT JOIN requester ON requester.ciid=p1.ciid LEFT JOIN softwarelicense_fields slf ON softwarelicenses.resourceid=slf.licenseid GROUP BY resources.resourcename,sdorganization.name, slt.licensetype, softwarelicenses.allocated, softwarelicenses.installationscount, aaacontact.EMAILID, SystemInfo.WORKSTATIONNAME, slf.UDF_CHAR1, slf.UDF_CHAR2 ORDER BY 1

Note: Additional fields have been added considering the 1st two fields with single line text fields. You may also modify the alias names present here as per your requirement.
          • Related Articles

          • 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", ...
          • Script to send multiple license expiry notification in Software License/Agreement

            We can set only 1 notification for the license expiry. Using custom schedule multiple expiry notifications like 90 days before, 60 days before and 30 days before can be achieved using script.  Prerequisites: 1. Python is a third party software that ...
          • Query to show workstation additional fields

            SELECT workstation.WORKSTATIONNAME AS "Machine Name", workstation.MANUFACTURER AS "Manufacturer", workstation.SERVICETAG AS "Service Tag", workstation.MODEL AS "Model", aao.NAME AS "Vendor Name", wof.udf_char1 "Cubical Number", wof.udf_char2 ...
          • Query to show user additional fields along with requests

            MSSQL: SELECT wo.WORKORDERID AS "Ticket Number", pd.PRIORITYNAME AS "Priority", cd.CATEGORYNAME AS "Category", qd.QUEUENAME AS "Group", ti.FIRST_NAME AS "Technician", technician.attribute_302 "Technician Business Unit", aau.FIRST_NAME AS "Requester", ...
          • How to import user additional fields using LDAP

            In order to import additional fields or map any other fields in AD to the system fields, a row needs to be updated with the appropriate values You can follow the below steps  1. Take a trimmed backup of the database. 2. Connect to the Database 4. Run ...