SAP Identity Management HTML Reporting: How to create HTML Reports.
HTML Reporting
- What are the attributes of a given user?
- What are the business roles assigned to a given user?
- What systems does a given user has access to?
- Which business roles are available in the system?
- How many users/business roles, etc. are available in
the system?
MX_REPORT
- MSKEYVALUE
- DISPLAYNAME– display name of the report.
- MX_OWNER
- MX_REPORT_ENTRY
- MX_REPORT_DATE– date on which the report was requested.
- MX_REPORT_FORMAT– format of the report (this could be PDF, HTML, DOC, etc.)
- MX_REPORT_RESULT– this attribute holds the full report result. It is saved as a binary in the database.
- MX_REPORT_RESULT_REF– this attribute holds a reference to the report result, in case it is stored in a separate file server.
- Entry – This corresponds to the value of the MX_REPORT_ENTRY attribute.
- Status – Status of the report task (pending, OK or Error). This status is calculated based on the status of the task execution, taken from the MCMV_AUDIT view.
- Report Date – This corresponds to the value of the MX_REPORT_DATE attribute.
- Report Name – The name of the report. If DISPLAYNAME has a value, this value is stored, otherwise the value of MSKEYVALUE is stored here.
- Report Result – This corresponds either to the MX_REPORT_RESULT or to the MX_REPORT_RESULT_REF attribute.
HTML Reporting Task
HTML Reporting Script
-
- We create a Header and Footer, which will be just static CSS and HTML code in the beginning and in the end of the HTML file. We do this to delimitate the static parts of the HTML code, so that they won’t interfere with our main logic. The header contains the opening HTML tags and the CSS used, and the footer just contains the closing HTML tags. For the sake of simplicity in this article, I won’t add the real CSS code I used in this example. Your header should look like this:
-
<html><head><style type=”text/css”><…………CSS here……………..></style></head><body>And your footer should be like this:</table></div></body></html>
2. Now, let’s create the script itself. For now, we have the HTML opening tags and the CSS, and the HTML closing tags, along with the table closing tag. Now, let’s populate the table itself. We will create 2 rows and 3 columns. The 1st row will contain the table headings: User Name, Assigned Privileges and Assigned Roles. The second row will contain the username, privileges and roles, all extracted from the Identity Center.Privileges are extracted with the following query:select mcothermskeyvalue from idmv_link_ext2 where mcAttrName='MXREF_MX_PRIVILEGE' and
mcthismskey='"+Par+"'
For roles, the query is:select mcothermskeyvalue from idmv_link_ext2 where mcAttrName='MXREF_MX_ROLE' and mcthismskey="+Par+"
Since Par(which is the value of MX_REPORT_ENTRY) , contains the mskey of the entry, we have to extract the mskeyvalue of that user to be shown under User Name. This is done via the following query:select mcmskeyvalue from idmv_entry_simple where mcmskey="+Par+"
Getting the results from those queries as values is done via the uSelect() function.We store the results of those queries in variables and add them to the table elements and store this into a variable (oHTML), which represents the table body.The last thing left to do is to return the binary representation (hex code). This is done via the uToHex() function. We also need to add “{HEX}” as prefix.This is how our example script looks like in the end:// Main function: HTMLReport function HTMLReport(Par){ var oHeader = uFromFile("C:\\Reporting\\Template\\header.html","-1","false"); var oFooter =uFromFile("C:\\Reporting\\Template\\footer.html","-1","false"); var AssignedPrivileges=uSelect("select mcothermskeyvalue from idmv_link_ext2 where mcAttrName='MXREF_MX_PRIVILEGE' and mcthismskey='"+Par+"'"); var oList=""; var oArray=AssignedPrivileges.split("!!"); for(var i=0; i<oArray.length; i++){ oList=oList+oArray[i]+'<br>'; } var AssignedRoles = uSelect("select mcothermskeyvalue from idmv_link_ext2 where mcAttrName='MXREF_MX_ROLE' and mcthismskey='"+Par+"' "); var oList2=""; var oArray2 = AssignedRoles.split("!!"); for(var i=0; i<oArray2.length; i++){ oList2 = oList2 + oArray2[i] + '<br>'; } var userName = uSelect("select mcmskeyvalue from idmv_entry_simple where mcmskey="+Par+" "); var oHTML='<div class="HTMLReport"><table><tr><td>UserName:</td><td>Assigned Privileges</td><td>Assigned Roles</td></tr>'; oHTML+='<tr><td>'+userName+'</td><td>'+oList+'</td><td>'+oList2+'</td></tr>'; var oHex="{HEX}"+uToHex(oHeader+oHTML+oFooter); return oHex; }
We save and go back to the To Identity Store pass definition.
3. As a final step, we add the value of MX_REPORT_RESULT to be calculated via the HTMLReport script, with MX_REPORT_ENTRY as input parameter. The Pass definition should look like this:
SAP Identity Management 7.2
SAP Identity Management 8.0
4. Save the task and log on to the User Interface with the “Administrator” user.
Starting the task via the User Interface
Let’s execute the task on an entry. We go to Manage tab and search for a person. We select the person, go to “Choose task” and select the “Create Report” task(in version 8.0, Create Report form).
The “Create Report” task is opened and we fill the attributes needed, then click “Save”.
This will save a new entry of type MX_REPORT, with MSKEYVALUE and Display Name – Superman Report, MX_REPORT_DATE – 25.04.2014, and values for DESCRIPTION and MX_REPORT_ERROR. Saving this will execute the action task and the To Identity Store pass, which will set HTML as value to the MX_REPORT_FORMAT attribute, and will calculate the value of MX_REPORT_RESULT, using the Script we created. To see our result, we will go to the View Reports tab:
We can see our newly generated report. We can see that it is for the Entry Clark Kent, the report task is successfully executed (Status – “OK”), the report name and the report result. If we click on the Result, we will see our report in the browser:
Pretty, isn’t it? It contains the UserName of the user and all the assigned privileges and business roles.
You can find the header and footer files, which I used with the abovementioned script to create this example report, attached to the article. If you like them, they are available for free usage 🙂
Yours truly,
Emanuil
This is excellent content!!!!!!
Just one note, while this technique will work for 7.1 and 7.2, it should be pointed out that your example should be based on mxiv_sentries rather than the idmv views.
Matt
Good stuff Emanuil 🙂
Thank you. It's always good to see a working example.
Hi,
thanks for your effort.
I want to report which priviliges and users are assigned to a role. I cannot get the HTML code correct. Can anyone help?
BTW: Report Date does not display.
Hi Volker,
Firstly, you have to create a new report task for the MX_ROLE entry type, where you follow the same pattern as for MX_PERSON.
For getting the privileges assigned to a role, here is an example query:
uSelect ("Select mcthismskeyvalue from idmv_link_ext_active where mcthisocname='MX_PRIVILEGE' and mcothermskey="+Par)
For getting the users assigned to a role, here is an example query:
uSelect ("Select mcthismskeyvalue from idmv_link_ext_active where mcthisocname='MX_PERSON' and mcothermskey="+Par)
Hi all,
very nice blog. We also use technologic like that in our RDS.
Please check our RDS content there you have a lot of html reports. You can use these as templates.
gr
rene
Dear Rene,
can you please post the link to the RDS HTML reporting downloads?
I might be too stupid to find them.
Thanks in advance
Volker
Hi Volker,
you have to import the whole RDS content. Because our implementation are Jscripts like Emanuil used.
You can find the RDS package at SCN.
http://service.sap.com/rds-idm
Navigate to Solution Deployment and download the package Identity Management RDS Content V1 (link will opened).
Please check the documentaion (D04_IDM720_BB_ConfigGuide_EN_XX.doc) how to import the related files. You need 0256_IDM72_Identity_Store_Schema.mcc and 0256_IDM72_Provisioning_Folder.mcc.
You can find the docu at same link like the content package; Identity Management RDS Service Enabl. SBS Guide.
Unzip: RDS_NW_IDM_IDM720_SERV.zip\SAP_BP\BBLibrary\Documentation
br
rene
Hi,
it is me again 🙂
Is there any way to sort the output alphabetically? How would I do this?
I have a request from my manager to create a report that shows by company (entry type BC_Conmpany) the roles with assigned users per role.
I have set up the task, but the script (select statement) won´t fly.
-------------
// Main function: HTMLReportCompany
function HTMLReportCompany(Par){
var oHeader = uFromFile("D:\\usr\\sap\\IdM\\Identity Center\\Templates\\header.html","-1","false");
var oFooter =uFromFile("D:\\usr\\sap\\IdM\\Identity Center\\Templates\\footer.html","-1","false");
var AssignedRoles = uSelect("select mcothermskeyvalue from idmv_link_ext where mcAttrName='MX_ROLE' and mcthismskey='"+Par+"' ");
var oList="";
var oArray=AssignedRoles.split("!!");
for(var i=0; i<oArray.length; i++){
oList=oList+oArray[i]+'<br>';
}
var AssignedUsers = uSelect("select mcthismskeyvalue from idmv_link_ext_active where mcthisocname='MX_PERSON' and mcothermskey='"+Par+"' ");
var oList2="";
var oArray2 = AssignedUsers.split("!!");
for(var i=0; i<oArray2.length; i++){
oList2 = oList2 + oArray2[i] + '<br>';
}
var compName = uSelect("select mcmskeyvalue from idmv_entry_simple where mcmskey="+Par+" ");
var oHTML='<div class="HTMLReport"><table><tr><td>Location:</td><td>Business Role</td><td>Assigned Users</td></tr>';
oHTML+='<tr><td>'+compName+'</td><td>'+oList+'</td><td>'+oList2+'</td></tr>';
var oHex="{HEX}"+uToHex(oHeader+oHTML+oFooter);
return oHex;
}
------------
Sofar I was successfully listing the users belonging to the specified company, but some are listed twice.
Any help is highly appreciated.
Volker
Easiest way would be to do it in SQL like:
var AssignedUsers = uSelect("select mcthismskeyvalue from idmv_link_ext_active where mcthisocname='MX_PERSON' and mcothermskey='"+Par+"' order by mcthismskeyvalue");
regards, Tero
Dear Tero,
works like a charm. Thank you.
Volker
Hi Volker,
Could you clarify your scenario, by answering the following questions?
Best Regards,
Emanuil
Dear Emanuil,
thanks for your great artikel. It opend the reporting world in IDM for me.
BTW: I cannot see the report date (which I set manually) on the 'View Reports' tab, although the column 'Report Date' is shown.
Any help is really appreciated.
Regards
Volker
Hi Volker,
Try with this query in the uSelect:
uSelect("select mcothermskeyvalue, mcthismskeyvalue from idmv_link_ext2
where mcOtherOcName='MX_ROLE' and mcThisOcName='MX_PERSON'
and mcLinkState=0 and mcCtxMSKEYVALUE='"+Par+"' order by mcothermskeyvalue");
This is if you want to order the roles alphabetically. If you want to order the privileges, replace mcothermskeyvalue with mcthismskeyvalue in the order by clause.
Have in mind that this query will return 2 columns, one contains role name and the other contains an assigned privilege to that role, so you will have to change your HTML code and create more appropriate tables to store them 🙂
Best Regards,
Emanuil Ivanov
Dear Emanuil,
with below SQL statement I can see the data I want in SQL:
uSelect("select e1.mcOtherMSKEYVALUE, e1.mcthismskeyvalue from idmv_link_ext e1 with (nolock) inner join idmv_link_ext e2 with (nolock) on e1.mcThisMSKEY = e2.mcThisMSKEY where e1.mcThisOcName = 'MX_PERSON' and e1.mcOtherOcName = 'MX_ROLE' and e1.mcAttrName = 'MXREF_MX_ROLE' and e2.mcOtherOcName = 'BC_COMPANY' and e2.mcOtherMSKEYVALUE='"+Par+"'order by e1.mcOtherMSKEYVALUE, mcThisMSKEYVALUE");
Unfortunatly I cannot get any output from the script (even when I only select e1.mcOtherMSKEYVALUE (role name) and adjust the script I get nothing in the HTML file).
What do I do wrong?
Regards
Volker
Hi Emanuil,
Thanks for your blog. It is quite useful.
I have created a PDF report with a job (not a UI task). I wonder if you could share me some light on how to display this report on UI?
I used a toIdentityStore pass to create a MX_REPORT record in IDM. But I guess it needs a link to a MX_PERSON entry. But how can I do it in jobs?
Thanks & Regards
Chenyang Xiong
In order to make it visible you need to set the MX_OWNER-attribute, owners will see the report in Report-tab.
regards, Tero
Thank you Tero,
It works. After MX_OWNER is set, I am able to see the report from UI, though the entry and status column are empty.
I guess the report tab should have two categories, one for entry and one for system level report.
Best Regards
Chenyang
I don't have access to the customer system where I developed some mx_reports, so cannot verify, but I would assume that the columns you mentioned are just attributes of the mx_report.
Looking at the mx_report entry type from IdM schema there's a column mx_report_entry which looks like a reference to the entry (try filling it if your report can be matched 1:1 to an entry/mskey).
regards, Tero
Hello Emanuil,
this was my first contact with those reports in IDM and thanks to your blog (and after pushing through a little confusion with the template location, because I didn't read closely enough) it was a successful experience! That you provided the templates as a starting point was really helpful, too.
Like Volker, the report date isn't shown in my UI either. But I'll look into that later to see, if I can fix this myself. ^^
Thanks again for this blog! 🙂
Hi,
A nice Blog.. Any idea how do we delete just created reports visible in the view Report tab ?
Regards
Rimesh
You can delete them from Id Store like any other IdM-entry with toIdentityStore-pass.
It's probably easiest to create a job that deletes them based on criteria..
regards, Tero
I havent been able to see Report date in my UI .. but I can see in database that MX_REPORT_DATE has the value which I entered through UI.. Any idea how to bring report date up in the UI?
Can this please be updated for IdM 8.0 since it is directly linked from the IdM 8.0 Configuration Guide? I am having difficulty in translating this to 8.0. Can I do this entire process in a single job or do I create a process with an action task?
Hi Casey,
I'm also having same issue. Do you have update.