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.
New to ADSelfService Plus?
Resources
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", ...