List Vault users per usage activities

[Tri]

I would like to list all Vault users and be able to save the results in a text file.

List 1:
- Having most activities first (do more check in / out)
- Inactive users must be also listed but displayed last in the list

List 2:
- user name and status (Active or Inactive)

I would prefer a SQL Query to run in Query Analyzer. Can you please provide me with the SELECTs. Whether it's one queries or a sequence of several queries, it's OK. All I need is the results.

Thanks very much in advance.

[jclausius]

How are your SQL Skills?

Off the top of my head, something like this may do the trick for #1 -

Code: Select all

SELECT r.name, u.login, count(t.txid) txcount from sgvault.dbo.tbltransactions t LEFT OUTER JOIN 
sgvault.dbo.tblusers u ON (u.userid = t.userid) INNER JOIN sgvault.dbo.tblrepositories r ON (t.repid = r.repid) 
GROUP BY r.name, u.login ORDER BY r.name, txcount DESC

As for #2,

Code: Select all

SELECT userid, name, login, active FROM sgvault.dbo.tblusers

HTH

[Tri]

Thanks for the queries. I know well SQL queries.

What does count(tbltransactions.txid) include? Check in/out, connection, get latest, undo, delete, etc?

When a user get latest of a folder (or check in/out a set of files), does that count for 1 transaction or 1 per file?

[jclausius]

"tbltransactions" contains each change to the tree. Every checkin, delete, branch, rename, etc is wrapped in a transaction. This table represents that change to the repository.

For each individual change within a transaction, you can use tbltransactiondetails. This has every sub-item contained within the single transaction. Also, this information can be used to join to tblfsobjecthistoryitems (which will join to tblfsobjecthistoryitemmisc) for additinal information.


In regards to GET, there is no table which contains information about a user's GET activity, so nothing can help there.

I guess you could place the server's log into DEBUG mode, and parse the server log for GET information, but it would be tedious work and the log files may fill up your disk drive over time.