OnBase Query to retrive User audit details

[Sunderanand_Ba1]

Dear All,

I am looking for a single query which will retrieve the user details from OnBase with below columns in the report. Any help on this will be highly helpful. Thank you.

Columns required in the report :  User ID, User Name ,creation date, deletion date, last login date

Regards,

Sunder Balan

[Ryan_Wakefield]

This isn't exactly the prettiest of all queries (since you need to pull data from a completely separate table and you can't really join them to do what you need), so hopefully it works for you. (NOTE: This is for MSSQL, not Oracle.)

Declare @userid intSET @userid = 4838select usernum AS 'User ID',	username AS 'Username',	(select logdate from hsi.securitylog where messagetext LIKE '%'+CAST(@userid AS varchar)+'%' and actionnum=4 and subactionnum=6) AS 'Creation Date',	(select logdate from hsi.securitylog where messagetext LIKE '%'+CAST(@userid AS varchar)+'%' and actionnum=4 and subactionnum=7) AS 'Deletion Date',	lastlogon AS 'Last Logon Date' from hsi.useraccountwhere usernum=@userid

[Melissa_Foster]

Thanks for the Query @Ryan Wakefield 

I like to keep things like this in my notes, so I love when the community shares them.

[Ryan_Wakefield]

Sure thing. Again, isn't pretty but it is what it is. The nice thing here is if you want to pull other values, then you can easily add in additional columns the same way I did above. So you could potentially look to create a pretty interesting query results. 🙂