Count of audit events by user group

We are looking to build an SQL based LiveReport to measure system usage by particular user groups. Particularly, a count of the different audit events using user group and date as a parameter. We are using v 10.5.

Can anyone reccomend an existing report or provide some SQL that I can work of?

Thanks for your help.

Find more posts tagged with

Comments

Nizar Ghazal

Quoted Danielle Ridge on 09/21/2017 12:22 PM:

Can anyone reccomend an existing report or provide some SQL that I can work of?

Thanks for your help.

Hi, there should be the "Number of Auditable Transactions" LiveReport included into CS 10.5.

It looks like this, if you were to click edit:

select count( *) trans from DAuditNew where AuditDate > %1 and AuditDate <= %2

You could optionally add in a third parameter, that takes a GroupID. However, since DAuditNew records a UserID/PerformerID of an individual user, you'll likely need to add in some additional SQL to get everyone IN a given group, and then take the sum of their audited events.

Someone on the forums may have built something like that, already.

Stay tuned for other replies!

Colin_Schmidt

In Oracle (and not much differently in MS SQL), you could run this to see the counts per Audit Action. The “to_char” portion would have to be modified for the way MS SQL handles dates. “CAST”, I think. When I converted some from Oracl to SQL, I just searched the internet.

select

da.auditid,

da.auditstr,

to_char(da.auditdate, 'yyyy-mm') "Month",
count(*)
from dauditnew da
where da.auditdate >= %1 /* This would need to be changed depending on whether you’re running for a month */

/* period of for each day. */
group by da.auditid, da.auditstr, to_char(da.auditdate, 'yyyy-mm')
order by to_char(da.auditdate, 'yyyy-mm'), da.auditid

You could modify it to report per day. Over time, it would need to be run in a DB Tool.

Colin J. Schmidt

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Thursday, September 21, 2017 12:04 PM
To: eLink Recipient <devnull@elinkkc.opentext.com>
Subject: Count of audit events by user group

Count of audit events by user group

Posted by Ridge, Danielle On 09/21/2017 11:59 AM

Can anyone reccomend an existing report or provide some SQL that I can work of?

Thanks for your help.

[To post a comment, use the normal reply function]
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center CS16

The information contained in this e-mail is confidential and/or proprietary to Capital One and/or its affiliates and may only be used solely in performance of work or services for Capital One. The information transmitted herewith is intended only for use by the individual or entity to which it is addressed. If the reader of this message is not the intended recipient, you are hereby notified that any review, retransmission, dissemination, distribution, copying or other use of, or taking of any action in reliance upon this information is strictly prohibited. If you have received this communication in error, please contact the sender and delete the material from your computer.

Colin_Schmidt

You won’t be able to measure by Group from the Audit record because the Audit records the UserID.

If you want to tie the User to a Group, which one. They could be in dozens potentially.

You would have to pull in the Groups assigned in the DTREEACL and then check the membership with the User to find which one, in a sub-query, and report that way.

The attached query is a modification of one I found in the Forum several years ago. It lists all CREATES of documents by each User over a 24-hour period covering a day or a week or however long you set it up to run. You can change it to reflect what ever activity you want. I included the timezone of the User to explain something of why the activity is during the hours that it is.

Colin J. Schmidt

Count of audit events by user group

Posted by Ridge, Danielle On 09/21/2017 11:59 AM

Can anyone reccomend an existing report or provide some SQL that I can work of?

Thanks for your help.

[To post a comment, use the normal reply function]
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center CS16

UserFetchActivity.txt

Danielle_Ridge

Thank you everyone for your feedback. I am a beginner to both SQL and the CS schema so this is very helpful.

I ended up finding an example from a training manual that is pretty close to what we want, it lists the number of auditable transactions performed by each user group within a given date parameter:

select name "Group", count(auditstr) "Event Count" from kuaf, kuafchildren, dauditnew WHERE kuaf.type = 1 and kuaf.id = kuafchildren.id AND dauditnew.performerid = kuafchildren.childid AND dauditnew.auditdate >=%1 AND dauditnew.auditdate <=%2 GROUP BY kuaf.id, kuaf.name ORDER BY count(auditstr) desc

It would be awesome if I could modify this to provide a breakdown of the count for each type of auditable event (i.e. how many times a version was added, how many deletions etc.). Any advice?

John Simon

Quoted Danielle Ridge on 09/25/2017 08:09 AM:

Just fyi...your query is basically mixing time frames...the audit information is from a specific time, and the kuaf/kuafchildren information is as of right now. In other words you can add a user to a group (say "GROUP A") and your query will show that "GROUP A" made all the changes for that user (even before you added them to the group).

Cheers...