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
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!
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!
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
Thanks Niketnilay for your support, I was able to fix the issue, hope to get similar kind of help in future 🙂
Regards,
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
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>
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
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/
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
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!
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