Getting the list of IS users along with last logged-in time

AmeyJoshi14 · Post by **AmeyJoshi14** » Mon Jan 13, 2014 2:12 pm

Hi Experts,

Is there anyway to get the list of all the Infosphere users along with last access time? I have checked XMETA.USERS_2 but this table has only 2 records - admin and guest

Currently we are having LDAP configured in our Linux servers. We have to send the list of all the users who have not logged in from last 45 days.

Thanks in Advance!!

ray.wurlod · Post by **ray.wurlod** » Mon Jan 13, 2014 4:06 pm

Nothing I know of. There may be something like this in the new operational quality monitoring tool for 9.1.2, but I haven't played with that yet.

stuartjvnorton · Post by **stuartjvnorton** » Mon Jan 13, 2014 5:57 pm

You can refer to your equivalent of /opt/IBM/WebSphere/AppServer/profiles/InfoSphere/logs/ISauditLog.log (may differ a bit according to audit settings) to get all login/logout activity and the client used.

Shouldn't be too hard to write a job for it.
It would be nicer in XMETA somewhere though.

AmeyJoshi14 · Post by **AmeyJoshi14** » Tue Jan 14, 2014 10:54 am

Thanks for the information.
I have opened PMR with IBM, will update this post once I receive information from IBM.

Thanks again.

ray.wurlod · Post by **ray.wurlod** » Tue Jan 14, 2014 3:36 pm

Good catch from Stuart. I'm working in an environment where most users have no access at all to the services tier.

stuartjvnorton · Post by **stuartjvnorton** » Tue Jan 14, 2014 6:52 pm

ray.wurlod wrote:There may be something like this in the new operational quality monitoring tool for 9.1.2, but I haven't played with that yet. ...

Hey Ray,

If you're referring to the new Information Governance Dashboard, then no they don't.
It covers BG, IA in relation to data rules and how they link to BG, and the underlying views also have a little bit of common metadata stuff.

AmeyJoshi14 · Post by **AmeyJoshi14** » Thu Jan 16, 2014 2:00 pm

Hi Experts,

As per IBM support we can get job level information from DS_AUDIT table and from the ISauditLog_0.log file which Stuart suggested.
But we are looking to get the list of all users logged in to particular project, list of the users last logged in, list of the users based on access level, inactive users etc.
Is this level of information avaialble in Infosphere suite may be in some XMETA table?

So far I have found one table 'XMETA.ASCLModel_xmetagen_Userb1c498ce' which contains information about the user id, but the creation timestamp column has values which are difficult to debug...

For E.g:-

Code: Select all

SELECT distinct 
A0.XMETA_CREATED_BY_USER_XMETA
,A0.FIRSTNAME_XMETA
,A0.LASTNAME_XMETA  
,A0.PRINCIPALID_XMETA
,A0.XMETA_CREATION_TIMESTAMP_XMETA
,A0.XMETAMODIFICATIONTIMESTAMPXMET FROM 
ASCLModel_xmetagen_Userb1c498ce A0


XMETA_CREATION_TIMESTAMP_XMETA=1889220711147
XMETAMODIFICATIONTIMESTAMPXMET=1389220682002

ray.wurlod · Post by **ray.wurlod** » Thu Jan 16, 2014 3:46 pm

DS_AUDIT does not record logins. I'm pretty certain that login information is not stored anywhere in XMETA either other than when sessions are active.

stuartjvnorton · Post by **stuartjvnorton** » Thu Jan 16, 2014 4:48 pm

All of the create and mod "date" columns in xmeta are milliseconds since 1970-01-01 00:00:00 UTC.

There are reports under Administration/Security you can do to get currently logged in users or user lists based on roles etc.
It can't just give a complete user dump with groups/roles etc.

You don't have a way to see inactive users (other than coming up with a rule based on last login etc).
Users either don't get used or you delete them out of InfoSphere. There is no active/inactive user flag you set in the admin page, and doing a Delete removes the user record from xmeta.

AmeyJoshi14 · Post by **AmeyJoshi14** » Fri Jan 17, 2014 10:45 am

Thanks all for the help!!
So for now we will just load the data from ISauditLog.log into table.

Appreciate your help!!

shershahkhan · Post by **shershahkhan** » Tue Jan 13, 2015 9:48 pm

Hi, from this topic its seems like there is no way we can accurately identify active users, but do we have anyway just to list all users, their role and which project they have access too.

We do audit of all users, their roles and access to project. Currently we are retriving the userlist manually but don't know if there is any command to retirve it?

stuartjvnorton · Post by **stuartjvnorton** » Wed Jan 14, 2015 5:11 pm

You can write some SQL to pick it out of XMETA, but XMETA is undocumented and subject to change (and it looks like there have been some changes in 11.3).