Contributor Audit Reports
December 23rd, 2007A common question of ep admins is: "How can I see all users currently logged into planning wrt applications they're using?"
Something like: "Steve is now editing Capex and Sales Planning, and Mary is the only one in HR application now".
The task relatively simple for 1 application (yet required view publish for version prior to 8.2, now there's a special table with users). You just join nodestate with users and states tables and enjoy nice pie chart of submitted,in progress and not started. Even a direct drill-through in application, if you'd like.
But it becomes messy as more and more applications are added.
I've seen variants in Cognos ReportNet, PHP, IIS log parsing and liked none.
Yet all applications are listed in pad datastore and you just have to iterate the "1 application solution".
So I just dream of audit package for EP that would automatically add all applications and give admins some rest of programming and framework changing,
Did anyone out there do any of audit reporting on EP? What were your problems & solutions?
Due to heavy concurrent usage our system page (on server adminstration) frozen and I have to come up with this to report my manager about user sessions.
SELECT
— [COGIPF_HOST_IPADDR]
— ,[COGIPF_HOST_PORT]
— ,[COGIPF_PROC_ID]
— [COGIPF_LOCALTIMESTAMP]
— ,[COGIPF_TIMEZONE_OFFSET]
count([COGIPF_SESSIONID])
— ,[COGIPF_REQUESTID]
— ,[COGIPF_STEPID]
— ,[COGIPF_SUBREQUESTID]
— ,[COGIPF_THREADID]
— ,[COGIPF_COMPONENTID]
— ,[COGIPF_BUILDNUMBER]
— ,[COGIPF_LOG_LEVEL]
— ,[COGIPF_STATUS]
— ,[COGIPF_ERRORDETAILS]
— ,[COGIPF_LOGON_OPERATION]
— ,[COGIPF_USERNAME]
,[COGIPF_USERID]
— ,[COGIPF_NAMESPACE]
FROM [MYAUDITDB].[dbo].[COGIPF_USERLOGON]
where [COGIPF_LOCALTIMESTAMP] between ’2009-01-01 10:00:00.000′ and ’2009-01-01 11:00:00.000′
and [COGIPF_LOGON_OPERATION] = ‘Logon’
– and [COGIPF_USERID] = ‘MYCOGNOSID’
group by [COGIPF_USERID]
And more refined by hourly usage
SELECT COUNT([COGIPF_PROC_ID]) as count
, datepart(hh,[COGIPF_LOCALTIMESTAMP]) as hr
,convert(char(10), [COGIPF_LOCALTIMESTAMP], 101) as dt
FROM [MYAUDITDB].[dbo].[COGIPF_USERLOGON]
where [COGIPF_LOCALTIMESTAMP] between ’2009-01-01 6:00:00.000′ and ’2009-01-01 15:00:00.000′
–and [COGIPF_LOGON_OPERATION] = ‘Logon’
group by convert(char(10), [COGIPF_LOCALTIMESTAMP], 101),datepart(hh,[COGIPF_LOCALTIMESTAMP])
order by dt, hr