Bucket the data for the proper time chart for volume and response time

[samarkumar]

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!!

[sundareshr · Accepted solution]

What if user picks last 15min or 30 mins etc? Or a date/time range? If you want to limit it to 1d vs 7d, you should use a dropdown instead of timepicker. You can then control the span by setting drilldown token values like this

<input type=dropdown>
<option value="7d">7 days</option>
<option value="1d">1 day</option>
<change>
<condition value="7d">
<set token="span">1d</set>
</condition><condition value="1d">
<set token="span">1h</set>
</condition>

And in your query use

... | timechart span=$span$ count

[samarkumar]

Thanks Sundar for your reply.

As i have another reports in the dashboard and thoughts better to keep time picker.

is there any way we can bucket the following data...

2016-09-27 17:22:00 1 61 37 - data set1
2016-09-27 17:29:00 1 41 17 - data set 2
2016-09-27 17:45:00 1 12 13 - data set 3
2016-09-27 17:59:00 1 11 11 - data set 4

as below:
data set 1 and data set 2
2016-09-27 17:00:00-2016-09-27 17:30:00 2 102 54

data set 3 and data set 4

2016-09-27 17:30:00 - 2016-09-27 18:00:00 2 23 24

and later on decide the bucketing based on the time range and restrict with 12-24 bars in timechart.

Appreciate your time to suggest.

Thank You.