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",
SUM(scd.ALLOCATEDTODOWNGRADESW) AS "Allocated To Downgrades",
SUM(scd.AVAILABLE) AS "Available",
MAX(softcomp.COMPLIANCETYPE) AS "Compliance Type",
MAX(softl.DESCRIPTION) AS "Description",
SUM(scd.INSTALLED) AS "Installed", MAX(swmfg.NAME) AS "Manufacturer",
SUM(scd.TOTALSITELICENSES) AS "Purchased",
SUM(scd.CALCOUNT) AS "Purchased CAL",
MAX(softcat.SOFTWARECATEGORY) AS "Software Category",
swadnfld.UDF_CHAR1 AS "TEST ADDITIONAL FIELD",
swadnfld.UDF_CHAR3 AS "TEST ADDITIONAL FIELD 2",
swadnfld.UDF_CHAR5 AS "TEST ADDITIONAL FIELD 3",
swadnfld.UDF_CHAR7 AS "TEST ADDITIONAL FIELD 4",
swadnfld.UDF_CHAR2 AS "TEST ADDITIONAL FIELD 5",
swadnfld.UDF_CHAR4 AS "COST ADDITIONAL FIELD",
swadnfld.UDF_CHAR6 AS "Product code"
FROM SWComplianceBySite scd
LEFT JOIN SWComplianceType softcomp ON scd.SWCOMPLIANCETYPEID=softcomp.SWCOMPLIANCETYPEID
LEFT JOIN SoftwareList softl ON scd.SOFTWAREID=softl.SOFTWAREID
LEFT JOIN SoftwareCategory softcat ON softl.SOFTWARECATEGORYID=softcat.SOFTWARECATEGORYID
LEFT JOIN SoftwareManufacturer swmfg ON softl.SWMANUFACTURERID=swmfg.SWMANUFACTURERID
LEFT JOIN softwareinfo softInfo ON scd.SOFTWAREID=softInfo.SOFTWAREID
LEFT JOIN SoftwareLicense_Fields swadnfld ON softInfo.LICENSEID=swadnfld.LICENSEID GROUP BY scd.SOFTWAREID,swadnfld.UDF_CHAR1,swadnfld.UDF_CHAR3,swadnfld.UDF_CHAR5,swadnfld.UDF_CHAR7,swadnfld.UDF_CHAR2,swadnfld.UDF_CHAR4,swadnfld.UDF_CHAR6
Kindly use the below query to find the colunname and aliasname :
select columnname, aliasname from columnaliases where tablename = 'SoftwareLicense_Fields' order by columnaliasesid;
New to ADSelfService Plus?
Related Articles
Query to retrieve Account details along with additional attributes, postal address and additional fields data
TESTED IN BUILDS: 14700 (Postgres) QUERY 1 - Without Account Additional Fields: select ad.ORG_NAME As "Account", org.description AS "DESCRIPTION", ad.LOGIN_WEBURI AS "LOGIN WEB URL", ad.LOGIN_URI AS "LOGIN URI", ad.SUPPORT_EMAIL AS "SUPPORT EMAIL", ...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 ...Query to show workstation additional fields (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) SELECT workstation.WORKSTATIONNAME AS "Machine Name", workstation.MANUFACTURER AS "Manufacturer", workstation.SERVICETAG AS "Service Tag", workstation.MODEL AS "Model", aao.NAME AS "Vendor Name", ...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 report to extract Active Technician Login and IP Address (MSSQL & PGSQL)
Tested in builds PGSQL (14300) or MSSQL (14306) Go to Reports- New Query Report and execute this query. select DISTINCT ac.ACCOUNT_ID as "Login Account ID", au.FIRST_NAME as "Technician Name ( First Name )", count(acs.SESSION_ID) as "Number of ...