Splunk Search
Highlighted

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 linecount, 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 LineCount 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
Highlighted

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

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
Highlighted

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

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
Highlighted

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

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
Highlighted

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

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/




| eval message="Happy Splunking!!!"


Highlighted

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

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
Highlighted

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

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>



| eval message="Happy Splunking!!!"


Highlighted

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

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
Highlighted

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

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
Highlighted

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

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=HeaderCount path=payload.gpmGenerateEventLogs.gpmGenerateEventLog{}.attribute1
|spath output=Line
Count path=payload.gpmGenerateEventLogs.gpmGenerateEventLog{}.attribute2
|spath output=OrgCode 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),HeaderCount),LineCount),OrgCode),status),TimeZone),CDCRDC)
|mvexpand combined| eval combined=split(combined,",")
|eval OpName=mvindex(combined,0)
|eval EvType=mvindex(combined,1)
|eval HeaderCount=mvindex(combined,2)
|eval Line
Count=mvindex(combined,3)
|eval OrgCode =mvindex(combined,4)
|eval status =mvindex(combined,5)
|eval TimeZone =mvindex(combined,6)
|eval CDC
RDC=mvindex(combined,7)
|where status="hvoperror" OR status="validationerror"|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=HeaderCount path=payload.gpmGenerateEventLogs.gpmGenerateEventLog{}.attribute1
|spath output=Line
Count path=payload.gpmGenerateEventLogs.gpmGenerateEventLog{}.attribute2
|spath output=OrgCode 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),HeaderCount),LineCount),OrgCode),status),TimeZone),CDCRDC)
|mvexpand combined
|eval combined=split(combined,",")
|eval OpName=mvindex(combined,0)

|eval EvType=mvindex(combined,1)

|eval HeaderCount=mvindex(combined,2)
|eval Line
Count=mvindex(combined,3)
|eval OrgCode =mvindex(combined,4)
|eval status =mvindex(combined,5)
|eval TimeZone =mvindex(combined,6)
|eval CDC
RDC=mvindex(combined,7)
|where status= "hvoperror" OR status="validationerror"]
| bucket time span=1d

| stats sum(Line
Count) AS Requests by _time

0 Karma