Data with multiple occurences

[aquinojason]

Hi,

We have the following source tables (2 sourcetypes):

1st table:

ACCOUNT_CODE	Application	Business Unit	Application RTO
AD30OH00	Accounts Reconciliation Control System	Individual Life Insurance	C - Greater than 4 hours and less than or equal to 24 hours
ADR3OH00	Accounts Reconciliation Control System	Individual Life Insurance	C - Greater than 4 hours and less than or equal to 24 hours
AJ52OH00	Accounts Reconciliation Control System	Individual Life Insurance	C - Greater than 4 hours and less than or equal to 24 hours
AN00OH00	Accounts Reconciliation Control System	Individual Life Insurance	C - Greater than 4 hours and less than or equal to 24 hours
AN0WOH00	Accounts Reconciliation Control System	Individual Life Insurance	C - Greater than 4 hours and less than or equal to 24 hours

2nd Table:

DATE	MVS_SYSTEM_ID	ACCOUNT_CODE	CALCMIPS
1/1/2020	SYST1	AD18IEDA	1.31
1/1/2020	SYST2	AD18IE00	2.11

We wanted to create a table where we can show the SUM or AVG CALCMIPS per Application Name based on month. However, the ACCOUNT_CODE can be used by Multiple Application so it needs to be calculated within all those occurrences. Can you help us on how we can achieve it?

Table will look something like:

Application Name	Business Unit	Application RTO	January Avg. MIPS
Accounts Reconciliation Control System	Individual Life Insurance	C - Greater than 4 hours and less than or equal to 24 hours	0.0472767857142857

Thanks and Regards,

[aquinojason]

I will test this out. Thank you very much!

Yes, the ACCOUNT_CODE does exist in both source types. It's just that it can used my multiple Business Unit and should be summed up or included in the average of the Business Units that use that particular ACCOUNT_CODE.

[manjunathmeti]

Assuming both source types contain the same ACCOUNT_CODE values. 

index=index sourcetype=sourcetype1 
| append [ search index=index sourcetype=sourcetype2 
| eval DATE =strftime(strptime(DATE, "%m/%d/%Y", "%b_%Y"))] 
| eventstats max(Application) AS Application, max("Business Unit") AS "Business Unit", max("Application RTO") AS "Application RTO" by ACCOUNT_CODE 
| search sourcetype!=sourcetype1 
| stats avg(CALCMIPS) as avg_CALCMIPS, values("Business Unit") AS "Business Unit", values("Application RTO") AS "Application RTO" by DATE, Application 

If this reply helps you, an upvote/like would be appreciated.