Have you considered writing an EPDM report? This is a query against the vault database that will give results that can be exported to a CSV file which Excel can open. The query can return the user and the locked file count. Search the forum on EPDM reports and you will find there is information on how to do this.
I hadn't until you mentioned it, my only hang-up with the report generator is I know nothing about writing the SQL scripts. I've looked through the forum, and hve read the knowledgebase article about adding queries, but Is there any resource that can tell me the variables that I can query?
I found one I wrote that may work for you.
@[Files Checked Out]
§Name [Files checked out count by user]
§Company [My company]
[This query will list a count of files checked out by user.]
SELECT COUNT(D.DocumentID) AS 'File Count', U.Email, U.Username AS 'User ID', U.Fullname AS 'User Name'
FROM Users U INNER JOIN Documents D ON U.UserID = D.UserID
WHERE D.LockPath != '' AND D.Deleted = 0 AND D.ObjectTypeId = 1
GROUP BY U.Email, U.Username, U.Fullname
Thanks! This seems to work pretty well. One question, though; why do the values for number of files checked out by a user sometimes vary from what the report generates, to what is seen when you hover over a users name?
Good catch Paul. This was written before the user information was added to EPDM. I've modified the query to only look for files. It was looking at all object types before, so it would also pick up internal references (and I'm not sure what else).
I changed the script to what you updated, and only one user came up with a very incorrect number of checked out files (showed 1, when actual was 1270). On a whim I decided to change ...D.ObjectTypeId = 2 to ...D.ObjectTypeId = 1, and all signs look like its now showing the correct numbers.
Yes, I typed 2 (external refs) instead of 1. Updated the code in my previous post.
FYI - you can get a list of users by using the EPDM API.
Dim poFind As IEdmFindUser
poFind = vault.CreateUtility(EdmUtility.EdmUtil_FindUser)
Dim poResult As IEdmEnum
poResult = poFind.Result
Dim str As String = "Found " & poResult.Count.ToString & " users:"
Dim poUser As IEdmUser10
Dim UserInfo As EdmUserDataEx = New EdmUserDataEx()
UserInfo.mlEdmUserDataExFlags = EdmUserDataExFlag.Edmudex_All
For Each element In poResult
poUser = element
str = str & vbLf & "User Name: " & UserInfo.mbsCompleteName
The report generator has a function that can prompt the user to select a user or users to run a report against. Also, if you just want a list of users, do a "User" search and check to include both logged in and out.