Splunk Search

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

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

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

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

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

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

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

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

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

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

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

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

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
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!