[acs] Eportal Audit Trail query

[ChrisJ]

I'm trying to fish some information out of the audittrail table via sql.

 

Actions taken by a user in Eportal to be precise. However this table returns the ePortal username value eg. sta001aff (UserRespon in AUDITTRAIL).

 

Does anyone know of a table that might contain this username value alongside the staff ID?

 

I'm wanting to return the staff name as part of my query rather than having to look at the resource passwords list in Facility.

 

I've looked in RESPASSWORDS and can see that has an encrypted value PwdData that is linked to the staff ID. Knowing my luck the username is scrambled in that.

 

Anyone managed to find what I'm after in the maze of Facility tables?

[michael2k6]

You're right in what you said, it's encrypted. The only thing I can think of is that you populate your own table in another db manually containing the userid and the staff name so that you can then automate the dump of data from audittrail. I would expectusernames will rarely change and new ones will be created on large at certain times of year. It's not perfect but it's a workaround. May I ask why you're doing this, out of interest?

[ChrisJ]

Thought that might be the case. I found all the information I needed, just wanted to see if I could get a bit more. I'll explain my logic in a PM.