Relational Database Tables in HFM/FDM and queries which help in information required

HFM/FDM stores login or date information in gregorian calendar format.  To Convert gregorian calender to normal calendar format we use to_date function in oracle.

FDM Query to check user activity at given time

--------------------------------------------------------------

select username,to_char(starttime,'DD/MM/YYYY HH24:MI:SS') starttime,to_char(endtime,'DD/MM/YYYY HH24:MI:SS') endtime,eventinfo,errorinfo,iosource,status,partitionkey,periodkey,categorykey from tlogactivity

where starttime like '%10-JAN-15%'

To get user level of access in FDM

------------------------------------------

select a.userid,b.appsecleveldesc from tsecuser a,tsecuserlevel b

where a.appseclevel=b.appseclevel

Activity carried out & consolidation timings in HFM for a specific time

----------------------------------------------------------------------------------------

select activityuserid,servername,strdescription,to_char(to_date('01/1900','MM/YYYY')+Starttime-2,'DD/MM/YYYY HH24:MI:SS') "start",to_char(to_date('01/1900','MM/YYYY')+endtime-2,'DD/MM/YYYY HH24:MI:SS') "end" ,to_char(to_date('01/1900','MM/YYYY')+(endtime-starttime),'HH24:MI:SS') "Duration"

from AppName_task_audit

where strdescription is not null and to_char(to_date('01/1900','MM/YYYY')+Starttime-2,'DD/MM/YYYY HH24:MI:SS') > '10/01/2015'

order by 4 asc

Activity carried out by users & consolidation timings in HFM during or after a specific time

----------------------------------------------------------------------------------------------------------------------

select h.susername,g.servername,g.strdescription,to_char(to_date('01/1900','MM/YYYY')+g.Starttime-2,'DD/MM/YYYY HH24:MI:SS') "start",to_char(to_date('01/1900','MM/YYYY')+g.endtime-2,'DD/MM/YYYY HH24:MI:SS') "end" ,to_char(to_date('01/1900','MM/YYYY')+(g.endtime-g.starttime),'HH24:MI:SS') "Duration"

from AppName_task_audit g,hsv_activity_users h

where strdescription is not null and to_char(to_date('01/1900','MM/YYYY')+Starttime-2,'DD/MM/YYYY HH24:MI:SS') > '10/01/2015' and h.luserid=g.activityuserid

order by 4 asc

select g.servername,g.appname,to_char((to_date('01/1900','MM/YYYY')+g.starttime-2),'DD/MM/YYYY hh24:mi:ss'),to_char((to_date('01/1900','MM/YYYY')+g.endtime-2),'DD/MM/YYYY hh24:mi:ss'),g.strdescription,s.susername

from AppName_task_audit g,hsv_activity_users s

where g.activityuserid in s.luserid and s.susername not like '%admin%'

Consolidation Timings taken to complete in HFM

----------------------------------------------------------------

select g.servername,g.appname,to_char((to_date('01/1900','MM/YYYY')+g.starttime-2),'DD/MM/YYYY hh24:mi:ss') starttime,to_char((to_date('01/1900','MM/YYYY')+g.endtime-2),'DD/MM/YYYY hh24:mi:ss') endtime,g.strdescription,s.susername,(endtime-starttime)*24*60 TimeTaken

from AppName_task_audit g,hsv_activity_users s

where g.activityuserid in s.luserid and g.strdescription is not null

order by 3 desc

HFM Error Log Analysis converting the gregorian date to normal date format

---------------------------------------------------------------------------

select * from hfm_errorlog

where TO_CHAR(TO_DATE('01/1900','MM/YYYY')+DTIMESTAMP,'DD/MM/YYYY')='10/01/2015'

what is the activity being carried out by users currently-- the same can be obtained through users on system feature in workspace in HFM (Activity Codes are mentioned at the end of this post)

--------------------------------------------------------------------------------------------------------------------------------

SELECT H.SSERVERNAME,s.susername,TO_CHAR((TO_DATE('01/1900','MM/YYYY')+H.DSTARTTIME-2),'DD/MM/YYYY hh24:mi:ss'),H.LACTIVITYCODE,H.SACTIVITYDESC FROM HSV_USERS_ON_SYSTEM H,HSV_ACTIVITY_USERS S

WHERE H.LUSERID IN S.LUSERID and H.LACTIVITYCODE<>0

order by sservername

To Check # of clusters & servers in application

------------------------------------------------------------

select * from HSX_CLUSTER_INFO

select * from HSX_CLUSTER_SERVERS

---------------to get no.of sessions created by each user----------------

select s.susername,count(1) from HSV_USERS_ON_SYSTEM h,HSV_ACTIVITY_USERS s

where h.LUSERID IN s.LUSERID and sservername='XXXXXXXXXX'

group by s.susername

Activity Carried out by user

------------------------------------

SELECT H.SSERVERNAME,s.susername,TO_CHAR((TO_DATE('01/1900','MM/YYYY')+H.DSTARTTIME-2),'DD/MM/YYYY hh24:mi:ss'),H.LACTIVITYCODE,H.SACTIVITYDESC FROM HSV_USERS_ON_SYSTEM H,HSV_ACTIVITY_USERS S

WHERE H.LUSERID IN S.LUSERID and H.LACTIVITYCODE<>0

order by sservername

HFM Errorlog total records

-------------------------------------

select count(*)  from hfm_errorlog;

HFM_ERRORLOG records per month

-------------------------------------------------

select to_char(to_date('01/1900', 'MM/YYYY')+DTIMESTAMP-2,'MM/YYYY') as "MMYYY", count(*) as "Number of entries"

from hfm_errorlog

group by to_char(to_date('01/1900', 'MM/YYYY')+DTIMESTAMP-2,'MM/YYYY');

Task Audit records per month

---------------------------------------

select to_char(to_date('01/1900', 'MM/YYYY')+StartTime-2,'MM/YYYY') as "MMYYYY", count(*) as "Number of entries"

from  AppName_TASK_AUDIT

group by to_char(to_date('01/1900', 'MM/YYYY')+StartTime-2,'MM/YYYY');

select to_char(to_date('01/1900', 'MM/YYYY')+StartTime-2,'DD/MM/YYYY') as "DDMMYYYY", count(*) as "Number of entries"

FROM AppName_TASK_AUDIT

GROUP BY TO_CHAR(TO_DATE('01/1900', 'MM/YYYY')+STARTTIME-2,'DD/MM/YYYY')

order by substr(to_char(to_date('01/1900', 'MM/YYYY')+StartTime-2,'DD/MM/YYYY'),7,4),substr(to_char(to_date('01/1900', 'MM/YYYY')+StartTime-2,'DD/MM/YYYY'),4,2) asc

To get records in task audit for 3 months in HFM

-----------------------------------------------------------

select g.servername,g.appname,to_char((to_date('01/1900','MM/YYYY')+g.starttime-2),'DD/MM/YYYY hh24:mi:ss'),

to_char((to_date('01/1900','MM/YYYY')+g.endtime-2),'D/MM/YYYY hh24:mi:ss'),g.strdescription,s.susername

from AppName_task_audit g,hsv_activity_users s

where to_char((to_date('01/1900','MM/YYYY')+g.starttime-2),'MM/YYYY') > to_char(add_months (sysdate, -3),'MM/YYYY') and

to_char((to_date('01/1900','MM/YYYY')+g.starttime-2),'YYYY') = to_char(sysdate,'YYYY')

Data Audit records per month

--------------------------------------------

select to_char(to_date('01/1900', 'MM/YYYY')+DTIMESTAMP-2,'MM/YYYY') as "MMYYYY", count(*) as "Number of entries"

from AppName_DATA_AUDIT

group by to_char(to_date('01/1900', 'MM/YYYY')+DTIMESTAMP-2,'MM/YYYY');

 Activity Codes in HFM

 ------------------------------

Activity Code	Activity Description
0	Idle
1	RulesLoad
2	RulesScan
3	RulesExtract
4	Consolidation
5	ChartLogic
6	Translation
7	CustomLogic
8	Allocate
9	DataLoad
10	DataExtract
11	DataExtractviaHAL
12	DataEntry
13	DataRetrieval
14	DataClear
15	DataCopy
16	JournalEntry
17	JournalRetrieval
18	JournalPosting
19	JournalUnposting
20	JournalTemplateEntry
21	MetadataLoad
22	MetadataExtract
23	MemberListLoad
24	MemberListScan
25	MemberListExtract
26	SecurityLoad
27	SecurityScan
28	SecurityExtract
29	Logon
30	LogonFailure
31	Logoff
32	External
33	MetadataScan
34	DataScan
35	ExtendedAnalyticsExport
36	ExtendedAnalyticsSchemaDelete
37	TransactionsLoad
38	TransactionsExtract
39	DocumentAttachments
40	DocumentDetachments
41	CreateTransactions
42	EditTransactions
43	DeleteTransactions
44	PostTransactions
45	UnpostTransactions
46	DeleteInvalidRecords
47	DataAuditPurged
48	TaskAuditPurged
49	PostAllTransactions
50	UnpostAllTransactions
51	DeleteAllTransactions
52	UnmatchAllTransactions
53	AutoMatchbyID
54	AutoMatchbyAccount
55	IntercompanyMatchingReportbyID
56	IntercompanyMatchingReportbyAcct
57	IntercompanyTransactionReport
58	ManualMatch
59	UnmatchSelected
60	ManageICPeriods
61	Lock/UnlockICEntities
62	ManageICReasonCodes
63	Null