Splunk Search

How to build a trend chart that compares today's result with previous day, week, and month for the same time frame?

avaishsplunk
Path Finder

I have a search result having a column line_count, which gets incremented every 5 min on the basis of my events coming to Splunk. I need to build 3 trend charts which showing trends with Yesterday, Last week and Last month data. I need the Trends comparison with exact date/time e.g. Lets say I view my dashboard at 5:10 PM today i.e. 12/23/2016 17:10 PM , then comparing with yesterday it should compare with cumulative Line Count for yesterday i.e Sum of Line_Count from 12/22/2016 00:00:00 to 12/22/2016 17:10 PM (i.e. exact timestamp)

Similarly for Last week it should sum line_count from 12/16/2016 00:00:00 to 12/16/2016 17:10 PM (with exact timestamp). I tried multiple options but was not able to figure out the way to do it, pls help

0 Karma
1 Solution

avaishsplunk
Path Finder

Thanks a lot gokadroid and niketnilay, i tried both the options and they are working perfectly fine, i have one more requirement sorry i missed in my earlier requirment, in case if the day today is Monday, I would like comparison to be done with last Friday as Saturday and Sunday we do not get any data and this hold good for each of the cases i.e. Yesterday, Last Week and Last Month, can you pls help me with that, thanks once again for all your help on this, you guys are genius!

View solution in original post

0 Karma

avaishsplunk
Path Finder

Thanks a lot gokadroid and niketnilay, i tried both the options and they are working perfectly fine, i have one more requirement sorry i missed in my earlier requirment, in case if the day today is Monday, I would like comparison to be done with last Friday as Saturday and Sunday we do not get any data and this hold good for each of the cases i.e. Yesterday, Last Week and Last Month, can you pls help me with that, thanks once again for all your help on this, you guys are genius!

0 Karma

harishnpandey
Explorer

Hi Avinash/gokadroid/niketnilay

I have similar query search for my case study..

can you please help me with query so that I will use and explore it more

Currently, I have dashboard which has time input as "Today" which lists the total count of Payments

index=myindex "paymentmodule" | stats count as Payments

I can add another "Time Input -2" with same search query which I used above for "Time Input-1"to existing dashboard but don't know who to make the "Time input -2" dynamic so that I can compare the total counts for any weekday with current day.

For e.g. Total count for current week Wednesday v/s Total count for last week Wednesday

I could not find any option to make Time Range -2 dynamic so that I need not to go and manually change values in time picker

Appreciate your valuable suggestions in advance

0 Karma

avaishsplunk
Path Finder

Thanks Niketnilay for your support, I was able to fix the issue, hope to get similar kind of help in future 🙂

Regards,

0 Karma

avaishsplunk
Path Finder

Hello NiketNilay,

In my below query, I am facing one issue, while getting the stats for 2 days, if there are no events for a particular day say today i have 0 records then i am getting N/A as error, I am trying to plot a single value field with a trend ,so that i can show the comparison in %age between the chosen period.

But I am not able to fix this 0 count issue. Can you pls help

[| gentimes start=-1 | eval earliest=if("$SearchOption$" == "Last Working Day Same Time" AND strftime(now(),"%A")="Monday","-3d@d",if("$SearchOption$" == "Last Working Day Same Time" AND strftime(now(),"%A")!="Monday","-1d@d",if("$SearchOption$" == "Last Week Same Day Time","-7d@d",if("$SearchOption$" == "Last Month Same Time","-1mon@d","-1d@d")))) | eval latest= if("$SearchOption$" == "Last Working Day Same Time" AND strftime(now(),"%A")="Monday","-3d@s",if("$SearchOption$" == "Last Working Day Same Time" AND strftime(now(),"%A")!="Monday","-1d@s",if("$SearchOption$" == "Last Week Same Day Time","-7d@s",if("$SearchOption$" == "Last Month Same Time","-1mon@s","-1d@s"))))
|table earliest, latest | format "" "" "" "" "" ""] index=YY sourcetype=ZZ
| search "XX"
|spath output=OpName path=payload.gpmGenerateEventLogs.gpmGenerateEventLog{}.operationName
|spath output=EvType path=payload.gpmGenerateEventLogs.gpmGenerateEventLog{}.eventTypeCode
|spath output=Header_Count path=payload.gpmGenerateEventLogs.gpmGenerateEventLog{}.attribute1
|spath output=Line_Count path=payload.gpmGenerateEventLogs.gpmGenerateEventLog{}.attribute2
|spath output=Org_Code path=payload.gpmGenerateEventLogs.gpmGenerateEventLog{}.attribute3
|spath output=status path=payload.gpmGenerateEventLogs.gpmGenerateEventLog{}.attribute4
|spath output=TimeZone path=payload.gpmGenerateEventLogs.gpmGenerateEventLog{}.attribute5
|spath output=CDC_RDC path=payload.gpmGenerateEventLogs.gpmGenerateEventLog{}.attribute6
|eval combined=mvzip(mvzip(mvzip(mvzip(mvzip(mvzip(mvzip(OpName,EvType),Header_Count),Line_Count),Org_Code),status),TimeZone),CDC_RDC)
|mvexpand combined| eval combined=split(combined,",")
|eval OpName=mvindex(combined,0)
|eval EvType=mvindex(combined,1)
|eval Header_Count=mvindex(combined,2)
|eval Line_Count=mvindex(combined,3)
|eval Org_Code =mvindex(combined,4)
|eval status =mvindex(combined,5)
|eval TimeZone =mvindex(combined,6)
|eval CDC_RDC=mvindex(combined,7)
|where status="hvop_error" OR status="validation_error"|append [search earliest=@d index=YY sourcetype=ZZ
| search "XX"
|spath output=OpName path=payload.gpmGenerateEventLogs.gpmGenerateEventLog{}.operationName
|spath output=EvType path=payload.gpmGenerateEventLogs.gpmGenerateEventLog{}.eventTypeCode
|spath output=Header_Count path=payload.gpmGenerateEventLogs.gpmGenerateEventLog{}.attribute1
|spath output=Line_Count path=payload.gpmGenerateEventLogs.gpmGenerateEventLog{}.attribute2
|spath output=Org_Code path=payload.gpmGenerateEventLogs.gpmGenerateEventLog{}.attribute3
|spath output=status path=payload.gpmGenerateEventLogs.gpmGenerateEventLog{}.attribute4
|spath output=TimeZone path=payload.gpmGenerateEventLogs.gpmGenerateEventLog{}.attribute5
|spath output=CDC_RDC path=payload.gpmGenerateEventLogs.gpmGenerateEventLog{}.attribute6
|eval combined=mvzip(mvzip(mvzip(mvzip(mvzip(mvzip(mvzip(OpName,EvType),Header_Count),Line_Count),Org_Code),status),TimeZone),CDC_RDC)
|mvexpand combined
|eval combined=split(combined,",")
|eval OpName=mvindex(combined,0)

|eval EvType=mvindex(combined,1)

|eval Header_Count=mvindex(combined,2)
|eval Line_Count=mvindex(combined,3)
|eval Org_Code =mvindex(combined,4)
|eval status =mvindex(combined,5)
|eval TimeZone =mvindex(combined,6)
|eval CDC_RDC=mvindex(combined,7)
|where status= "hvop_error" OR status="validation_error"]
| bucket _time span=1d

| stats sum(Line_Count) AS Requests by _time

0 Karma

niketn
Legend

Ideally you should have asked a separate question. However, in any case, what you need is eval to set token based on condition. You can evaluate if the current day is Monday or not to set Last working day to previous day or 3 days prior.

<eval token="option">case($selOption$=="Last_Week_Same_Day" AND strftime(now(),"%A")=="Monday","earliest=-3d@d latest=-3d@s",$selOption$=="Last_Week_Same_Day" AND strftime(now(),"%A")!="Monday","earliest=-1d@d latest=-1d@s", $selOption$=="Last_Working_Day" ,"earliest=-7d@d latest=-7d@s"</eval>

Following is an example, however, please note that it is in 6.5 which uses init tag to initialize the token for first time load. The same is not available in previous version, so you might need to change as per your need.

<form>
  <label>Sample Dashboard eval to set token</label>
  <init>
    <eval token="option">case(strftime(now(),"%A")=="Monday","earliest=-3d@d latest=-3d@s", strftime(now(),"%A")!="Monday","earliest=-1d@d latest=-1d@s"</eval>
  </init>
  <fieldset submitButton="false" autoRun="true">
    <input type="radio" token="selOption" searchWhenChanged="true">
      <label>Overlay Options</label>
      <choice value="Last_Working_Day">Last Working Day</choice>
      <choice value="Last_Week_Same_Day">Last Week Same Day</choice>
      <change>
        <eval token="option">case($selOption$=="Last_Week_Same_Day" AND strftime(now(),"%A")=="Monday","earliest=-3d@d latest=-3d@s",$selOption$=="Last_Week_Same_Day" AND strftime(now(),"%A")!="Monday","earliest=-1d@d latest=-1d@s", $selOption$=="Last_Working_Day" ,"earliest=-7d@d latest=-7d@s"</eval>
      </change>
      <default>Last_Working_Day</default>
    </input>
  </fieldset>
  <row>
    <panel>
      <title>Option: $option$</title>
      <chart>
        <search>
          <query>index=_internal sourcetype=splunkd log_level=error earliest=-0d@d latest=-0d@s 
 | timechart count as Today 
 | appendcols 
     [ search index=_internal sourcetype=splunkd log_level=error $option$ 
     | timechart count as $selOption$ ]</query>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="charting.chart">line</option>
      </chart>
    </panel>
  </row>
</form>
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

avaishsplunk
Path Finder

Hello niketnilay,

We are on version 6.4.1.2 of Splunk, I tried a lot to convert the above logic to fit into my 6..4.1.2 version, but unfortunately i am not able to figure out how will I convert the above logic to fit into my 6.4.1.2 version, can you pls help in putting the similar logic for the mentioned version.

Thanks a lot for all your help on this.

Regards

0 Karma

niketn
Legend

Since you will be overlaying a lot of events in single chart using correlation method, you must ensure you are filtering only the required events upfront. For overlaying older data only till current time you can snap to current time using @s. Following are some of the ways to achieve the same:

Option 1 Try using appendcols if all your series has similar data. Notice each search is essentially the same but with different earliest and latest time for three series namely Today (-0d), Yesterday (-1d) and last month (-1mon)

index=_internal sourcetype=splunkd log_level=error earliest=-0d@d latest=-0d@s 
| timechart count as Today 
| appendcols 
    [ search index=_internal sourcetype=splunkd log_level=error earliest=-1d@d latest=-1d@s 
    | timechart count as Yesterday ] 
| appendcols 
    [ search index=_internal sourcetype=splunkd log_level=error earliest=-1mon@d latest=-1mon@s 
    | timechart count as LastMonth ]

Option 2: Use append to correlate events similar to the one above, however, bin has to be adjusted manually to overlap time on x-axis i.e yesterday will require a correction to time by 1day= 24hour * 60min * 60sec= 86400. Refer to the following blog on using append to achieve the same :http://blogs.splunk.com/2012/02/19/compare-two-time-ranges-in-one-report/

Option 3: If you are using Splunk 6.5 onward you can try timewrap command where you can a lot of variations for timescale selection like timechart with daily, weekly, monthly comparison etc.

Option 4. For Older Splunk versions you can check out Timewrap app on Splunkbase which does something similar. https://splunkbase.splunk.com/app/1645/

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

gokadroid
Motivator

How about you try to do it this way wherein you compute the tasks for each of the day span and plot them together. Something like:

index=yourIndex sourcetypr=yourSourcetype earliest=@d
| bucket _time span=1d
| do the stuff you want to do
| stats sum(Line_Count) AS Requests by _time
| eval reportKey="Today"
| 
append [search index=yourIndex sourcetypr=yourSourcetype earliest=-1d@d latest=-24h
| bucket _time span=1d
| do the stuff you want to do
| stats sum(Line_Count) AS Requests by _time
| eval ReportKey="Yesterday" 
| eval _time=_time+(60*60*24)] 
| 
append [search index=yourIndex sourcetypr=yourSourcetype earliest=-7d@d latest=-168h
| bucket _time span=1d
| do the stuff you want to do
| stats sum(Line_Count) AS Requests by _time
| eval ReportKey="LastWeek" 
| eval _time=_time+(60*60*24*7)] 
|
chart Requests as Req over _time by ReportKey

avaishsplunk
Path Finder

Thanks a lot gokadroid , i tried both the options and they are working perfectly fine, i have one more requirement sorry i missed in my earlier requirement, in case if the day today is Monday, I would like comparison to be done with last Friday as Saturday and Sunday we do not get any data and this hold good for each of the cases i.e. Yesterday, Last Week((if the day is Monday then compare with Friday) and Last Month(if the day is Monday then compare with Friday), can you pls help me with that, thanks once again for all your help on this, you guys are genius!

0 Karma

avaishsplunk
Path Finder

Below is the sample query I used for doing comparison with yesterdays data, this works fine, but the moment i change to view last week or last month the query is adding up all line_count from 16th in case of weeks and 23 Nov in case of Month which is not correct

earliest=-1d@d latest=now index=yyyy sourcetype=xxxx| search "x"|spath output=OpName
path=payload.gpmGenerateEventLogs.gpmGenerateEventLog{}.operationName|             spath output=EvType
path=payload.gpmGenerateEventLogs.gpmGenerateEventLog{}.eventTypeCode|             spath output=state path=payload.gpmGenerateEventLogs.gpmGenerateEventLog{}.state|
spath output=Line_Count path=payload.gpmGenerateEventLogs.gpmGenerateEventLog{}.recordCount|             spath output=OC
path=payload.gpmGenerateEventLogs.gpmGenerateEventLog{}.attribute1|             spath output=TimeZone
path=payload.gpmGenerateEventLogs.gpmGenerateEventLog{}.attribute2|             spath output=CR
path=payload.gpmGenerateEventLogs.gpmGenerateEventLog{}.attribute3|             eval
combined=mvzip(mvzip(mvzip(mvzip(mvzip(mvzip(OpName,EvType),state),Line_Count),Org_Code),TimeZone),CR)|             mvexpand combined|eval
combined=split(combined,",")|             eval  OpName=mvindex(combined,0)|             eval  EvType=mvindex(combined,1)|             eval
state=mvindex(combined,2)|                                            eval Line_Count=mvindex(combined,3)|             eval OC=mvindex(combined,4)|
eval TimeZone =mvindex(combined,5)|             eval CR=mvindex(combined,6)|where OpName="TC"| where strftime(now(), "%H:%M:
%S")>=strftime(_time, "%H:%M:%S") | bin span=1d  _time|stats sum(Line_Count) AS Requests by _time
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 ...