User Activity

[Michelle_Troxel]

I need to do a report on a user's activity in OnBase for an HR case. They mostly work in Workflow so I was able to get some information from the wflog table. But they also import documents. I need a report of all the login/out times and any documents viewed/created. 

I tried running a report out of transactionxlog but it timed out. Same for hsi.itemdata. Even when I put it down to a 1 day date range, they both timed out. Yes, I did the "set transaction isolation level read uncommitted".

Reporting dashboards don't give me any of this information or they timeout as well.

Ideas?

Thanks! 

Michelle

[Kevin_Perron]

@Michelle Troxell I was thinking something along the lines of what @Jim Perry was saying where you could create, just with SQL, a copy of the DB to run your queries against.  Also, even at 5 TB, I feel like statistics and or optimization is off for the DB.  I have worked with some big OnBase databases, and not had a timeout when running a reasonably constrained query against transactionxlog.  A one day limit on the date should not run timeout.  Do you have visibility into the DB maintenance plans?  It might be worth investigating. 

@Jeanne Flynn if it times out in SQL I would expect it to crash that process too.  I am sure based on the criteria above, you cannot refine that report enough to get close to the information needed, and that report is not useful for actual audit reporting where people need to analyze the data. 

Good luck,

KP 

[Michelle_Troxel]

I have been over and over the db maintenance (indexing and stats updates) with the DBA. We had a lot of app performance issues before the optimization. 

That's a good thought on the copy. I wonder how much work that would be. We do so much db querying.

I'll take a look at that option in the thick client. I don't use the thick client much so I'm not aware of what is in there.

thank you for the input!

[Jay_Wegener]

@Michelle Troxell you mention that you're on v18, is this a fresh install of v18 or have you upgraded from a previous version? The reason why I ask, is that in later versions of v18+ we implemented an index on logdate for the hsi.transactionxlog table that gets created on new installs (not upgrades).  So I am wondering if you don't have this index) and that's why your queries are timing out by passing through a date. This index does not get added on upgrades from previous version because it would increase the already lengthy upgrade process.

If you do not have the logdate index, here is an article (while old, it's still relevant) that speaks to it. If you have further questions on this and would like the index added, please submit a support case and Hyland Technical Support will be glad to assist.

[Michelle_Troxel]

@Jay Wegener  we are on an upgraded version of 18. We went live on 16 I believe. I'll keep this in mind for our upgrade project and see if we can't get it put in with that.

I actually got the transaction log report to run in the thick client. Thank you @Jeanne Flynn 

Michelle