Hi using following query
index=np_3cm sourcetype=3CM:QA:3cmlog CorrelationId ="*" communicationRequestHeader* CommunicationMQListener* |table CorrelationId , EventType, TransactionType, BatchCorrelationId|join type=outer BatchCorrelationId[search index=np_3cm sourcetype=3CM:QA:3cmlog SendRequestToQueue*|rex "(?i).*?BatchFileCorrelationId:::(?P[^ )]+)" |rename _time as 3CMStartTime]|fields CorrelationId , EventType, TransactionType, BatchCorrelationId, 3CMStartTime|join type=outer CorrelationId [search index=np_3cm sourcetype=3CM:QA:3cmlog *SendCommunicationMQ* *SUCCESS*|rex "(?i).*?3CM (?P[^ -)]+)"|rename _time as 3CMEndTime]|fields CorrelationId , EventType, TransactionType, BatchCorrelationId, 3CMStartTime, 3CMEndTime|join type=outer CorrelationId [search earliest=-30d@d latest=+10h@h index=np_3cm sourcetype=3CM:QA:3cmlog deliveryTime*]|fields CorrelationId , EventType, TransactionType, BatchCorrelationId, 3CMStartTime, 3CMEndTime, CustDeliveryTime|join type=outer CorrelationId[search index=np_dpa Application=3CM OR Application=IEWMS sourcetype="DP:SIT:SYSLOG" PROXYNAME="mpgw_Generic3CMCommunicationAPI" (EventType = CUST_REGISTRATION OR EventType = CUST_LOGIN)|eval 3CMStartTime=(_time-3600)]|fields CorrelationId , EventType, TransactionType, 3CMStartTime, 3CMEndTime, CustDeliveryTime|join type=outer CorrelationId [search index=np_dpa sourcetype="DP:SIT:SYSLOG" *SIT* *-monitor PROXYNAME="mpgw_ENT_CommunicationAPI" (ResponseStatusCode = 202)(EventType = CUST_REGISTRATION OR EventType = CUST_LOGIN)|eval 3CMEndTime=(_time-3600)]|fields CorrelationId , EventType, TransactionType, 3CMStartTime, 3CMEndTime, CustDeliveryTime|Eval RequestType="Verified"|fields CorrelationId , EventType, TransactionType, 3CMStartTime, 3CMEndTime, CustDeliveryTime, RequestType|join type=outer CorrelationId [search index = np_3cm sourcetype="3CM:QA:3cmlog" "[ERROR]"|rex "(?i).*?3CM (?P[^ -)]+)"|Eval RequestType="ERROR"]|fields CorrelationId , EventType, TransactionType, 3CMStartTime, 3CMEndTime, CustDeliveryTime, RequestType|eval SFMCEndTime=round(strptime(CustDeliveryTime, "%Y-%m-%dT%H:%M:%S"),0)|eval SFMCEndTime=(SFMCEndTime-3600)|fields CorrelationId , EventType, TransactionType, 3CMStartTime, 3CMEndTime,SFMCEndTime,RequestType|eval 3CMTimeTaken=('3CMEndTime'-'3CMStartTime')|eval SFMCTimeTaken=('SFMCEndTime'-'3CMEndTime')|fields CorrelationId , EventType, TransactionType, 3CMStartTime,3CMTimeTaken, SFMCTimeTaken, RequestType|eval 3CMTimeTaken=if('3CMTimeTaken'<0,0,'3CMTimeTaken')|eval SFMCTimeTaken=if(SFMCTimeTaken<0,0,SFMCTimeTaken)|table 3CMStartTime, CorrelationId , EventType, TransactionType, 3CMTimeTaken, SFMCTimeTaken,RequestType|eventstats count as TransactionCount by 3CMStartTime|where RequestType="Verified"|eventstats avg(3CMTimeTaken) as AvgTimeIn3CM, avg(SFMCTimeTaken) as AvgTimeInSFMC by 3CMStartTime|stats values(TransactionCount) as TransactionCount, values(AvgTimeIn3CM) as AvgTimeIn3CM, values(AvgTimeInSFMC) as AvgTimeInSFMC by 3CMStartTime|eval 3CMStartTime= strftime('3CMStartTime',"%F %T")|eval AvgProcessingTimeIn3CM=round(AvgTimeIn3CM,0)|eval AvgProcessingTimeInSFMC=round(AvgTimeInSFMC,0)|table 3CMStartTime, TransactionCount , AvgProcessingTimeIn3CM, AvgProcessingTimeInSFMC|
i am getting the data in below table.
3CMStartTime TransactionCount AvgProcessingTimeIn3CM AvgProcessingTimeInSFMC
2016-09-27 17:22:00 1 61 37
2016-09-27 17:30:00 1 0 94
2016-09-27 17:37:46 1 0 3
2016-09-27 18:01:47 1 0 3
2016-09-27 18:03:26 1 0 3
2016-09-27 18:38:13 1 0 3
Now i am looking to group the above records based on Time intervals where if someone selects the Time Picker as 24 hours, then it will display the interval of 1 hours; and if someone selects 7 days, it will display the records based on every day.
Please suggest.
R!!
... View more