HQL query to retrieve linked attribute value

[rexsteffen]

Which IIQ version are you inquiring about?

Version 8.3

Share all details related to your problem, including any error messages you may have received.

I am looking to set up a custom HQL report that will return several identity attributes and one or two attributes from linked applications. Can anyone point me in the right direction on what the HQL query would look like? The identity attributes are straightforward, but looking specifically for the part of the query to get the linked attribute info.

[iamksatish (Satish Kurasala)]

@rexsteffen
Can you provide more details, the attribute you are looking within Link , is it a extended attribute common to all applications or a schema attribute that applies for only a specific application and in DB going into attributes column within xml instead of separate column?

[HemantSingh]

here’s the sample report which pulls identity attribute and link attribute also based on some application … you can modify the HQL based on your requirement.

<?xml version='1.0' encoding='UTF-8'?>
<!DOCTYPE TaskDefinition PUBLIC "sailpoint.dtd" "sailpoint.dtd">
<TaskDefinition executor="sailpoint.reporting.LiveReportExecutor" name="Link and Identity Attribute Report" progressMode="Percentage" resultAction="Rename" subType="Application Reports" template="true" type="LiveReport">
  <Attributes>
    <Map>
      <entry key="report">
        <value>
          <LiveReport title="Link and Identity Attribute Report">
            <DataSource objectType="sailpoint.object.Link" type="Filter">
              <QueryParameters>
                <Parameter property="id">
                  <ValueScript>
                    <Source>
                      
                      import sailpoint.object.QueryOptions;
                      List linkIds = new ArrayList();
                      String sql = "sql:SELECT link.id FROM spt_link link INNER JOIN spt_identity idt ON idt.id = link.identity_id WHERE link.application = (select id from spt_application where name = 'provide the name of the application') AND idt.name='provide user name or you can add your additional condition'";
                      Iterator iterator = context.search(sql, new HashMap(), new QueryOptions());
                      while ( iterator.hasNext() ) linkIds.add(iterator.next());
                      return linkIds;
                      
                    </Source>
                  </ValueScript>
                </Parameter>
              </QueryParameters>
            </DataSource>
            <Columns>
              <ReportColumnConfig field="created" header="Created" property="created" sortable="true" width="110"/>
              <ReportColumnConfig field="nativeIdentity" header="Native Identity" property="nativeIdentity" sortable="true" width="110"/>              
              <ReportColumnConfig field="cn" header="cn" property="attributes.cn" sortable="true" width="110"/>              
              <ReportColumnConfig field="sn" header="sn" property="attributes.cn" sortable="true" width="110"/>              
              <ReportColumnConfig field="givenname" header="givenname" property="attributes.givenname" sortable="true" width="110"/>              
              <ReportColumnConfig field="name" header="User Name" property="identity.name" sortable="true" width="110"/>
            </Columns>
          </LiveReport>
        </value>
      </entry>
    </Map>
  </Attributes>
  <Description>Link and Identity Attribute Report</Description>
</TaskDefinition>```

[rexsteffen]

It is the latter. It is a schema attribute that only applies to a specific application in a DB and therefore goes into the attributes column with xml.

[rexsteffen]

Thank you @HemantSingh! This was very helpful! Much appreciated! Hope it helps others in building custom reports with HQL. I think it offers a lot of flexibility.