Regenerating UMF from Identity Insight Data

This Oracle query will generate UMF messages from the data already in Identity Insight.  This could be used to archive the data in an ingestable format, or to move the data to a different instance.

SELECT '<UMF_ENTITY><DSRC_ACTION>A</DSRC_ACTION><DSRC_CODE>' || DSRC_CODE || '</DSRC_CODE><DSRC_ACCT>' || DSRC_ACCT || '</DSRC_ACCT><DSRC_REF>' || DSRC_REF || '</DSRC_REF>' || NVL2(NAME_SEG,NAME_SEG,'') || NVL2(ADDR_SEG,ADDR_SEG,'') || NVL2(EMAIL_SEG,EMAIL_SEG,'') || NVL2(NUM_SEG,NUM_SEG,'') || NVL2(ATTR_SEG,ATTR_SEG,'') || '</UMF_ENTITY>' ADD_UMF
FROM DSRC_ACCT D
JOIN DSRC_CODE C ON D.DSRC_ID = C.DSRC_ID
LEFT JOIN (SELECT DSRC_ACCT_ID, LISTAGG('<NAME><NAME_TYPE>' || NAME_TYPE || '</NAME_TYPE>' || NVL2(NAME_PFX,'<NAME_PFX>' || NAME_PFX || '</NAME_PFX>','') || NVL2(FIRST_NAME,'<FIRST_NAME>' || FIRST_NAME || '</FIRST_NAME>','') || NVL2(MID_NAME,'<MID_NAME>' || MID_NAME || '</MID_NAME>','') || NVL2(LAST_NAME,'<LAST_NAME>' || LAST_NAME || '</LAST_NAME>','') || NVL2(NAME_GEN,'<NAME_GEN>' || NAME_GEN || '</NAME_GEN>','') || NVL2(NAME_SFX,'<NAME_SFX>' || NAME_SFX || '</NAME_SFX>','') || '</NAME>','') WITHIN GROUP (ORDER BY NAME_ID) NAME_SEG
           FROM NAME
           GROUP BY DSRC_ACCT_ID) N ON D.DSRC_ACCT_ID = N.DSRC_ACCT_ID
LEFT JOIN (SELECT DSRC_ACCT_ID, LISTAGG('<ADDRESS>' || NVL2(ADDR_TYPE,'<ADDR_TYPE>' || ADDR_TYPE || '</ADDR_TYPE>','') || NVL2(ADDR1,'<ADDR1>' || ADDR1 || '</ADDR1>','') || NVL2(ADDR2,'<ADDR2>' || ADDR2 || '</ADDR2>','') || NVL2(ADDR3,'<ADDR3>' || ADDR3 || '</ADDR3>','') || NVL2(CITY,'<CITY>' || CITY || '</CITY>','') || NVL2(STATE,'<STATE>' || STATE || '</STATE>','') || NVL2(POSTAL_CODE,'<POSTAL_CODE>' || POSTAL_CODE || '</POSTAL_CODE>','') || NVL2(COUNTRY,'<COUNTRY>' || COUNTRY || '</COUNTRY>','') || '</ADDRESS>','') WITHIN GROUP (ORDER BY ADDR_ID) ADDR_SEG
           FROM ADDRESS
           GROUP BY DSRC_ACCT_ID) R ON D.DSRC_ACCT_ID = R.DSRC_ACCT_ID
LEFT JOIN (SELECT DSRC_ACCT_ID, LISTAGG('<EMAIL>' || NVL2(ADDR_TYPE,'<ADDR_TYPE>' || ADDR_TYPE || '</ADDR_TYPE>','') || NVL2(EMAIL_ADDR,'<EMAIL_ADDR>' || EMAIL_ADDR || '</EMAIL_ADDR>','') || '</EMAIL>','') WITHIN GROUP (ORDER BY EMAIL_ADDR_ID) EMAIL_SEG
           FROM EMAIL_ADDR
           GROUP BY DSRC_ACCT_ID) E ON D.DSRC_ACCT_ID = E.DSRC_ACCT_ID
LEFT JOIN (SELECT DSRC_ACCT_ID, LISTAGG('<NUMBER><NUM_TYPE>' || NUM_TYPE || '</NUM_TYPE><NUM_VALUE>' || NUM_VALUE || '</NUM_VALUE>' || NVL2(NUM_LOCATION,'<NUM_LOCATION>' || NUM_LOCATION || '</NUM_LOCATION>','') || '</NUMBER>','') WITHIN GROUP (ORDER BY NUM_ID) NUM_SEG
           FROM NUMS N
           JOIN NUM_TYPE T ON N.NUM_TYPE_ID = T.NUM_TYPE_ID
           GROUP BY DSRC_ACCT_ID) U ON D.DSRC_ACCT_ID = U.DSRC_ACCT_ID
LEFT JOIN (SELECT DSRC_ACCT_ID, LISTAGG('<ATTRIBUTE><ATTR_TYPE>' || ATTR_TYPE || '</ATTR_TYPE><ATTR_VALUE>' || ATTR_VALUE || '</ATTR_VALUE>' || '</ATTRIBUTE>','') WITHIN GROUP (ORDER BY ATTR_ID) ATTR_SEG
           FROM ATTRIBUTE A
           JOIN ATTR_TYPE T ON A.ATTR_TYPE_ID = T.ATTR_TYPE_ID
           GROUP BY DSRC_ACCT_ID) A ON D.DSRC_ACCT_ID = A.DSRC_ACCT_ID
WHERE D.DSRC_ACCT_ID = <dsrc_acct_id>

This query, as written, will generate the UMF message for the selected identity.  You can change the final where clause to generate for an entity (WHERE D.ENTITY_ID = <entity_id>), a full data source (WHERE D.DSRC_ID = <dsrc_id>), or for all the data by leaving off the WHERE clause.

Get Performance Stats from Identity Insight

If you want to see how many UMF messages are being processed per minute, execute one of the following SQL queries:

Oracle
SELECT DT, C
FROM (SELECT TO_CHAR(RCV_DT, 'YYYY-MM-DD HH24:MI') DT, COUNT(*) C
      FROM UMF_LOG
      WHERE RCV_DT >= DATE '2014-12-15'
      GROUP BY TO_CHAR(RCV_DT, 'YYYY-MM-DD HH24:MI')
      ORDER BY TO_CHAR(RCV_DT, 'YYYY-MM-DD HH24:MI') DESC)
WHERE ROWNUM <= 100;

SQL Server
SELECT TOP 100 CONVERT(SMALLDATETIME, RCV_DT) DT, COUNT(*) C
FROM UMF_LOG WITH (NOLOCK)
WHERE RCV_DT >= '2014-12-15'
GROUP BY CONVERT(SMALLDATETIME, RCV_DT)
ORDER BY CONVERT(SMALLDATETIME, RCV_DT) DESC

This will return a count of records processed for the last 100 minutes.  Substitute in the current date for 2014-12-15 to limit the number of rows processed.

In the SQL Server query, the conversion to smalldatetime will round the RCV_DT to the nearest minute.  I use the NOLOCK hint so as to minimally impact any ongoing UMF load.