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


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 account additional fields _ Details required

            We Need to know whether that is a common account additional fields or add attribute under individual account.   So please send us the following screenshots.   1. Admin-> Account additional fields list view page.   2. Accounts->Edit account and show ...
          • 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", ...