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",
ad.SENDERNAME AS "SENDER NAME",
imdef.NAME AS "Business Impact",
inattr.ATTRIBUTENAME AS "ATTRIBUTENAME",
inattr.ATTRIBUTEVALUE AS "ATTRIBUTEVALUE",
post.COUNTRY AS "Country/Region",
post.DOOR_NO AS "Door",
post.STREET AS "STREET" from PortalAccounts pa
LEFT JOIN AccountDefinition ad on pa.ACCOUNTID = ad.ORG_ID
LEFT JOIN SDOrganization org ON ad.ORG_ID = org.ORG_ID
LEFT JOIN ci ci on pa.ciid = ci.ciid
LEFT JOIN instanceattributes inattr on ci.ciid = inattr.ciid
LEFT JOIN BaseElement be on ci.ciid = be.ciid
LEFT JOIN ImpactDefinition imdef on be.IMPACTID = imdef.IMPACTID
LEFT JOIN SDOrgPostalAddr org_post ON org.ORG_ID=org_post.ORG_ID
LEFT JOIN AaaPostalAddress post ON org_post.POSTALADDR_ID=post.POSTALADDR_ID
OUTPUT:
QUERY 2 - With Accounts Additional Fields (Admin > Account Additional Fields):
There might be multiple account additional fields based on the configuration. To list all these additional fields in the report, please follow the below steps,
>> Use the below query under Reports > New Query Reports. You can find the Account additional fields column names using the below query,
select attributes.attributeid,attributes.attribute,attributes.typeoffield,attributes.columnname from citype left join citypeattribute on citype.typeid = citypeattribute.citypeid left join attributes on attributes.attributeid = citypeattribute.attributeid where citype.typename = 'Account'
>> Make a note of the Attribute (which is the additional field name) and coresponding columnname. Add all the entries in following query like aci.columnname as "attribute". For example: aci.ATTRIBUTE_301 as "additional field", (Please refer to the highlighted area)
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",
ad.SENDERNAME AS "SENDER NAME",
imdef.NAME AS "Business Impact",
inattr.ATTRIBUTENAME AS "ATTRIBUTENAME",
inattr.ATTRIBUTEVALUE AS "ATTRIBUTEVALUE",
aci.ATTRIBUTE_301 as "additonal filed",
post.COUNTRY AS "Country/Region",
post.DOOR_NO AS "Door",
post.STREET AS "STREET" from PortalAccounts pa
LEFT JOIN accountci aci on pa.ciid=aci.ciid
LEFT JOIN AccountDefinition ad on pa.ACCOUNTID = ad.ORG_ID
LEFT JOIN SDOrganization org ON ad.ORG_ID = org.ORG_ID
LEFT JOIN ci ci on pa.ciid = ci.ciid
LEFT JOIN instanceattributes inattr on ci.ciid = inattr.ciid
LEFT JOIN BaseElement be on ci.ciid = be.ciid
LEFT JOIN ImpactDefinition imdef on be.IMPACTID = imdef.IMPACTID
LEFT JOIN SDOrgPostalAddr org_post ON org.ORG_ID=org_post.ORG_ID
LEFT JOIN AaaPostalAddress post ON org_post.POSTALADDR_ID=post.POSTALADDR_ID;
OUTPUT:
In this way, multiple Additional Fields can be added.
New to ADSelfService Plus?
Related Articles
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 to retrieve the requests details
Tested in: 14610, 14301 QUERY: SELECT wo.WORKORDERID AS "Request ID", LONGTODATE(wo.CREATEDTIME) AS "Created Time", LONGTODATE(wo.RESPONDEDTIME) AS "Responded Date and Time", LONGTODATE(wo.RESOLVEDTIME) AS "Resolved Date and Time", ...Query to retrieve the active and expired CPH contracts details
Tested in: 14620, 14610 and 14306 Query 1: To return the Active CPH contracts details: select ad.org_name "Account", ad.org_name "Account", sp.serviceplanname "Service Plan", ac.CONTRACTNO "Contract No", longtodate(ac.startdate) "Contract Start ...Query to display additional field associations with templates (MSSQL)
Tested in build MSSQL (14306) Use case The reports shows in which templates the created additional fields are associated Query select sd.name "Service Catalog Name", rt.templatename "Template name", fc.field_name "Fields" from requesttemplate_list rt ...Query to retrieve the Change Details
Tested in: 14504, 14610 QUERY: SELECT chdt.CHANGEID AS "Change ID", cmDef.FIRST_NAME AS "Change Manager", ownaaa.FIRST_NAME AS "Change Owner", approvaldef.STATUSNAME AS "Approval Status", qd.QUEUENAME AS "Group", statusDef.STATUSDISPLAYNAME AS ...