Splunk Search

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

samarkumar
Path Finder

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

0 Karma
1 Solution

sundareshr
Legend

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

View solution in original post

0 Karma

sundareshr
Legend

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
0 Karma

samarkumar
Path Finder

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.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...