One of the questions I've been asked many times is how to identify users which are sharing MicroStrategy log-ins. This is typically in an environment which allows standard authentications as opposed to LDAP. This is a tough question to answer and I have seen it somewhat solved using various techniques like logging IP addresses. Still, that requires configuration, a logging mechanism, and some analysis to figure out what's what.
A while back I took some time to try to work out a logical method to identify users who were sharing their log-ins. The easier method that came to mind was to use Enterprise Manager since it was already installed, configured, and logging user data.
So, how can we leverage MicroStrategy's Enterprise Manager to identify shared user account? My thought - check the user sessions. If a user has an active session, and another active session in connected with the time frame of the first active connection, this might be a shared user log in.
To identify these cases, I used the code below;
SELECT
t1.Day_ID
, emu.EM_User_Name
, t1.Is_session_Id
, t1.Is_Connect_TS
, t1.Is_disConnect_TS
, count(t2.Is_Session_ID) AS Session_Counts
FROM Is_Session AS t1
LEFT JOIN EM_User_View AS emu
ON t1.EM_User_ID = Emu.EM_User_ID
LEFT JOIN Is_Session AS t2
ON t1.EM_User_ID = t2.EM_User_ID
and t1.Day_ID = t2.Day_ID
and t1.Is_Connect_TS ! = t2.Is_Connect_TS
and t1.Is_session_Id ! = t2.Is_session_Id
WHERE t2.Is_Connect_TS > t1.Is_Connect_TS
and t2.Is_Connect_TS < t1.Is_Disconnect_TS
GROUP BY
t1.Day_ID
, emu.EM_User_Name
, t1.Is_session_Id
, t1.Is_Connect_TS
, t1.Is_disConnect_TS
HAVING count(t2.Is_Session_ID) > 1
ORDER BY t1.Day_ID, emu.EM_User_Name, t1.Is_Connect_TS
This code has a self join to the session table which check for the Day, User Name, Session ID, Connection Start Time, and Connection end time. Then, it counts how many sessions were connected during the time frame of an existing connection.
This is good stuff, but there are some caveats. Keep in mind that this is based on user session. Some users might legitimately create new user sessions because they opened a new web browser or for some other reason. Keep this in mind before knocking at a users door/desk to check why they're sharing a log-in.
Last thing to note is that some of the analyst reading this might not have database access to query the enterprise manager database. No worries, just open MicroStrategy and add an external data source for a report, select the Enterprise Manager database, and drop in this query. Viola! You now have a dossier with the information you're looking for.
Thanks for checking out this blog post. If you found this post helpful please consider donating. Any contribution is appreciated! Just click the PayPal icon at the bottom of this page.