I had to face this question at a client site, How can we check if a User ran a specific rule after a data entry.
In our case all were menu driven rules (the rule will run only for the changed data co-ordinates), so I cannot opt for a business rule task list and force them to run it.
Customer was looking for a report which says, this user changed a specific data and he ran the rule (or he didn’t run it).
I was looking at different options on how to implement this and found out two ways to do so.
- Enable Auditing for Data changes and Launch Business Rules
- Look at the alternate approach.
They could just filter on the “user” and pull up a report which says they did change data and they ran the rule at this time.
You can create two SQL spreadsheets with a filter control to filter the “user”
Prerequisites
- Enable Auditing in Planning for Data changes and Launch Business Rules.
Understanding the tables
HSP_AUDIT_RECORDS keeps the audit of modified values
Key Columns
- Type – type of the modified object (this depends upon the audit options that is set for the application)
- ID_1 – mostly name of the modified object (this is null for Audit Options)
- ID_2 – mostly object_id from HSP_OBJECT table (identifies specific object that was audited, this is -1 for Audit Options)
- USER_NAME – User who performed the event
SQL Queries
Sheet 1
SELECT ID_1 AS “Form Name”, time_posted AS “Data Posted Time”, user_name AS “User” FROM HSP_AUDIT_RECORDS WHERE TYPE = ‘Data’
Sheet 2
SELECT NEW_VAL AS “Business Rule Name”, time_posted AS “Data Posted Time”, user_name AS “User” FROM HSP_AUDIT_RECORDS WHERE TYPE = ‘Business Rule’
This report will populate the information of “who” changed the data and “who” ran “which” Business Rule.
Alternate Approach (just for kicks :-))
My first approach to solve this request was to look at the Job Console and populate required information from there.
This information is stored in HSP_JOB_STATUS and I thought of sharing the information that I learned with you.
HPS_JOB_STATUS gets deleted after every Planning re-start, so you’ve to add a Property in Planning to keep the information for days/forever.
JOB_STATUS_MAX_AGE -1, this setting will make sure that the job status table doesn’t get deleted at all.
You can also specify the values in milliseconds for this property, e.g. for 3 days it is 259200000 (1000*60*60*24*3)
Understanding the tables
Understanding the tables
Key Columns
- PARENT_JOB_ID – This is the id for the parent job. If the rule is a part of a rule set, this will have the ID for the rule set job. RTPs will have the ID for the rule to which they belong.Note: If the rule (Business Rule) is a Business Rule sequence, the sequence itself is treated as a rule and the rules in it will not get populated.
- JOB_NAME – Name of the job
- JOB_TYPE – This is the type of the job being executed. Possible values:
- Calc Manager Rule Set – 5
- Rules (Calc Manager/Business Rules) – 1
- Business Rule Sequence – 6
- RTPs – 4
- USER_ID – This is the id for the user who executed the job, comes from HSP_USERS/HSP_OBJECT table
- RUN_STATUS – current status for the job, Possible values:
- 1 – Processing
- 2 – Completed
- 3 – Error
- ATTRIBUTE_1 – For rule, this stores the application against which the rule is being run. In case of RTPs, this stores the parent rule/rule set name.
- ATTRIBUTE_2 – For rule, this stores the plan type against which the rule is being run. In case of RTPs, this property stores the RTP prompt text.
SQL queries
Sheet 1
SELECT ID_1 AS “Form Name”, time_posted AS “Data Posted Time”, user_name AS “User” FROM HSP_AUDIT_RECORDS WHERE TYPE = ‘Data’
Sheet 2
SELECT a.JOB_NAME AS “Calculation Name”, a.START_TIME AS “Calculation Start Time”, a.END_TIME AS “Calculation End Time”, b.OBJECT_NAME AS “User” FROM HSP_JOB_STATUS a, HSP_OBJECT b WHERE a.USER_ID = b.OBJECT_ID AND b.OBJECT_NAME = ‘admin’ AND a.JOB_TYPE <> 4
Have an error free New Year (Happy New Year)
The second one actually kicked buddy… 🙂
How do you keep getting this stuff?????
selvin….i tried but i was not succceded ..can u eloborate briefly…..
Thanks
prasad
hi Prasad,
What didn't succeed?
Hi Calvin,
this information will be available in hbrlaunch.log in the application server folder right…for e.g if u do a application server deployment, one file will be created named hbrlaunch.log in the weblogic folder (if app server is weblogic)…..pls correct me if my understanding is wrong…….
Sunil Tharavath
Yes, HBRlaunch.log will have the information, however it is not easier to present that as a report
Hi Celvin – not sure about the ease of use of the log file, but its just a excel manipulation right? you can separate fields using the excel and do a filter on that….
Hey Sunil, if you can make that work, then yes that will do
hi! I have a question about data audit. Is it possible to register when a user change a data, from a form or when he use SmartView? Does the data audit register this changes?(i can not test than in the app by the moment)
Thankyou!!!
@Natalia, yes you can track data changes.
if you enable Auditing for Data and then you can run a query as given below.
SELECT * FROM HSP_AUDIT_RECORDS WHERE TYPE = 'Data' and USER_NAME AS "User";
Hi Celvin,
RUN_STATUS – current status for the job, possible values:
1 – Processing
2 – Completed
3 – Error
I've noticed jobs with value 4 (version 11.1.2.1), do you know what that value represents? Thanks!
Hello guys,
ahother cuestion about audit. I need to register 2 more things:
1) login, logout, successful and unsuccessful login attemps and 2) the IPadress and computer name from where the user logs into Hyperion Planning.
Do you know any way to register this info?
Thanks!
Natalia
@Peter da Graça RUN_STATUS = 4 is for Prompts, it says all Calc Manager Prompts with a status as 4
@Natalia
Login can be captured, you can read how to do that in this blog (I think you already did that.)
only way to track unsuccessful login is check whether this line "Invoking CSS Authenticate using following context:{APPLICATION_NAME=Planning, hyperion.login.hostinfo=0:0:0:0:0:0:0:1}" is not followed by "Fetching roles list for user took time"
I don't think IP is captured any where.
@natalia Seems like you can capture IP address also 🙂
Check in access.log of your EPM domain. E:OracleMiddlewareuser_projectsdomainsyourdomainserversyourservernamelogsaccess.log
that'll provide you information about all the ip address that access planning
@Celvin:
I did check access.log file. It is storing IP of server only but not user workstation IP / Host. Please advise further.