Once I had to face this question “How can I monitor the users who are not using the system?”
I modified that to “How can I monitor the last logged in time of a user?”
There are multiple ways to track user statistics in Essbase.
- You can create Log charts from EAS.
- You can use jrightlog to analyze Essbase log files http://code.google.com/p/jrightlog/
EAS method
You can generate Log Charts for Server (or Application) and select “Logged Users” as filter and refresh the chart.
This will upload the log entries into a table called SERVERLOGDETAIL in EAS schema (relational database).
You can then run a SELECT statement as given below against that table and it’ll give you the last login time of users.
SELECT username as “User Name”, MAX(entrydate) AS “Last Login Date”
FROM serverlogdetail where username is not null
If you are looking for an Application specific record then you can use the below given SELECT statement.
SELECT username as “User Name”, MAX(entrydate) AS “Last Login Date”
FROM serverlogdetail where msgtext like ‘Setting application ASOsamp %’
Jrightlog method
Download jrightlog from here. Run JRightLog.jar file and select Essbase.log and specify a target file.
You can add a filter “Logging in user” which will pull all the login records.
Sample log file
Planning Login details
There is no straight forward check if you want to check the last login time of a user in Planning.
You can make use of auditing options in Planning. You can enable Auditing for Data and Business Rules.
I’ve written something on this here https://orahyplabs.com/2012/01/how-to-check-whether-user-ran-rule.html
This was done with a different purpose, but if you enable Auditing for Data and then you can run a query as given below. (This should give the last data entry made by the user)
SELECT USER_NAME AS “User”, time_posted AS “Last Login Time” FROM HSP_AUDIT_RECORDS WHERE TYPE = ‘Data’;
Similarly you can expand the SQL to check whether a user ran a Business Rule or not and then get his Last Login details.
Just for kicks
When you perform a last login check on Planning, there can be users who logs in and just do a data check and log-off.
Unfortunately there is no means to track those users with above given methods. (they are not performing a data entry and Planning Auditing won’t be able to record those.)
So I was thinking how will I track these users.
You can enable debugging in Planning by (Won’t recommend doing this, however if you are in need) adding an entry in System Properties (DEBUG_ENABLED to true).
This will save user login information in the log file. You can use this bat file to extract the information.
Modify BASE_DIR, EPM_LOG_DIR, LOGFILE according to your environment.
I’ll post a blog on how to rotate the log files soon, so that you can archive the logs and then use that to extract the information.
HTH
Update 01/29/2016
I’ve added an option in the latest NUMSys version an option to get the last login information from Essbase.
NUMSys Web Edition + Essbase Security Viewer
I’m planning to include a command line option in coming releases which can be used to upload log files and generate the last login report.
Good Info
Regards,
Reddy
Does this work if you are using a network authentication? We are on 11.1.2.
I didn't check that with network authentication.
I had written a similar post some time back 🙂 http://my-noesis.blogspot.in/2011/01/user-logging-information-can-be.html
I am new to Essbase, so the info I post there may not be correct.
Hi Celvin, If its possible by you, Can you post something stuff on how to track hyperion works who works throught Smart view?
Hi Celvin, Thanks for the info (very useful). One little question regarding the Log Charts: is there a reason why the interface only shows me a subset of the log entries from the Essbase.log file? For example, in the dialog box where I set the options for generating the log chart, it tells me that the earliest date stamp in the log file is Feb 25, 2013, but I know for a fact that the earliest entry in the log file is Nov, 21, 2011. Is there a way around this? many thanks, mikeCC
@mike what is the duration that you are using while generating the log chart?
Hello all!
Do you knows if there is any solution to monitoring users login and logout in Hyperion Planning?
And if the IP adress could be registered?
Thanks a lot!
Natalia
@natalia I don't think IP address is captured anywhere (I can be wrong). For Logout I can check and update
Thanks Celving! Today I get an answer from Oracle regarding IP adress, they said it is not posible.
Regarding logout… i really apreciate your help!
Regards,
Natalia
@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"
@natalia
for logout this is the only entry that gets captured, where Primary Key is the USER_ID from HSP_USERS table.
Propegating external event[ FROM_ID: 204d18a4 Class: class com.hyperion.planning.sql.HspPrefs Object Type: 17 Primary Key: 50001 ]
Looking for applications for INSTANCE: []
@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
can you re-post the bat file again?
@Anon check whether a firewall is blocking your connection. The link is working fine.
To track Planning LOGIN connections you can use hbrlaunch.log, or hbr audit as describe before.
Actually, if a hbr is launched when a user OPEN a form(this hbr can be empty), you will be able to track user who are opening a form (without change any data).
It is, I think an alternative to track all users who are using Planning (for reading or writing purposes).
Hi Calvin,
Does we have any maxl statement or any hyperion utility to rotate the logs.?
As of now i am rotating the logs using shell script.
@Shiva If you are on 11.1.2.2 you can use ODL to do it, else you'll have to do it manually. Are you talking about all logs in general?
Hi Celvin,
Great post. It is very helpful.
1. Is it possible to automate log charts filter creation through Maxl,etc ?
2. Are the custom filters and their settings retained when the logs are cleared or Hyperion services are restarted ?
3. Is the refresh rate setting retained for "Logged users" filter after restart of Hyperion services ?
4. When we clear the logs or log chart , is the data also cleared from database tables (serverlogdetail and serverlogid) ? If yes, is it possible to retain it ?
Unfortunately I don't have access to dev environment to try it out. Appreciate your help.
Thanks,
Andy
Hi Celvin,
Do you have any suggestions for the above questions ? Kindly advice. Appreciate your help.
Thanks,
Andy
@Andy,
Wondering if you ever received any answers for you're questions from Feb 2014?
I'm really baffled at how rudimentary the overall Essbase logging functionality is. And that you have to conduct so many manual operations to actually extract any value from the log data. Either by:
1) executing "generate log charts" in EAS
2) or using jrightlog to parse the logfile data.
Can't Essbase be configured to automatically write all events to the SERVERLOGDETAIL table?
Such a pain!
I do appreciate all of the folks chiming in though as to how they've addressed their own particular usage tracking hurdles.
Much obliged,
Ben
Ben
There is no command which does that right now. Well that is a good question, but it is not what Essbase does now.
I've created a utility which can help you get the last login details of Essbase
https://orahyplabs.com/2016/01/numsys-web-edition-essbase-security.html
I'm planning to include a command line option which can be used to upload log files and generate the last login report.
Hi Celvin,
Is there a way to track Hyperion workspace login's and specifically web analysis report usage on 11.1.2.3?
Regards,
Pritesh
Hi
My client is asking Sign-on Audit in Planning. i am using 11.1.2.3.500 version please help me how to do
I think Shared services auditing will get you what you are looking for. Try that
Hi Celvin,
I tried to extract the login info for Essbase for a period of 3 months as per business request but I don’t get an option to change the filter and duration. It’s grayed out. By default duration is weekly and filter is set to errors.Also is this only capturing the EAS console login or it tracks excel add-in & smart view login and retrievals.
Thanks,
Prajin
I think this post deals with other options, like looking at logs. Also this is an old one. I guess Shared Services auditing now allows you to track login from Planning. Check that.
Dear Celvin,
The zip file is not working, can you please help me with that.
Regards,
K
I’ve fixed the missing link. Please check