Role/Privilege Approval History - idmv_linkaudit_ext

[Matt_Marples]

After reading previous posts people have suggested that I use the above view to obtain approval history information and so far so good.

One of the things I am finding difficult, is that there doesn't appear to be a uniqueid (or this maybe my lack of understanding) to tie all of the different approval stages together from when a role was first requested by the user. For example, if a user requests the same role twice in one year and that role is made up of two privileges, then I want to be able to relate the approval history of the privileges back to the time each role request was submitted.

Can anyone suggest if this is possible either by using the above view or another?

[jared_kobe]

Matthew,

The UniqueID from the MXI_LINK table and the LINKID in the IDMV_LINKAUDIT_EXT table will match up, and is the best way to search the view for a particular role or privilege. I don't think there is a good way to search the view for a particular request as the AuditIDs will be different for each step and each assignment will have its own uniqueID.

The only way I know of to relate the approvals for the privileges back to the original request is via the AuditID, whether that is the refaudit for the approval task in the mxp_audit table or using mcvalidateaddaudit and mcauditid in the MXI_LINK table.

The way we use this view to get an audit/approval history is as follows:

Select a user to generate report on.
Search MXV_AUDIT by user's MSKEY to find AuditIDs for all tasks that can request privileges.
Search MXI_LINK for mcUniqueId where mcAuditID in (AuditIDs above) to get all requested privileges.
Search IDMV_LINKAUDIT_EXT on mcuniqueid = linkID to pull back relevant information, keying on Operation column for request, approve, decline, etc.

Hopefully, that will give you some idea of where to start to see if you can get what you need from the view.

Thanks,

Jared