topic Re: custom span in timechart in Splunk Search

custom span in timechart

dsiob — Mon, 20 Nov 2017 09:37:12 GMT

Hi there,
I have a set of events say 8 records. One record of particular date and other 7 records of past 7 days. when i am applying timechart on it with span=1w, I need to club the first 7 day as one week and 8th record as a separate week.

For example:
2017-09-12
2017-09-13
2017-09-14
2017-09-15
2017-09-16
2017-09-17
2017-09-18
2017-09-19 are 8 records.

on applying span=1w (for avg)
I want records of:

2017-09-12
2017-09-19

But it starts week from the first date in uploaded data source:

2017-09-07
2017-09-14

here 2017-09-07 is first date in whole data source. (The 8 records i am getting after applying some conditions/filters on data source)

Any help?

Re: custom span in timechart

niketn — Mon, 20 Nov 2017 14:28:55 GMT

@dsiob you should use
1) Either your Time Picker input to pick only last 7 days (-7d@d) including current date.

<earliest>-7d@d</earliest>
<latest>now</latest>

2) Or directly apply earliest and latest to your base search

  <YourBaseSearch> earliest="-7d@d" latest="now"
  | timechart span=1w avg(<YourFieldName>) as Avg_Field

Re: custom span in timechart

somesoni2 — Mon, 20 Nov 2017 15:15:08 GMT

Custom spans will require custom aggregation (regular timechart may not suffice). Could you post your current full search? Do you always get 8 rows and are they always start from yesterday to last 8 days?

Re: custom span in timechart

dsiob — Tue, 21 Nov 2017 04:05:39 GMT

yeah, thats how I am filtreing records to get selected date and 7 days prev. records. That is coming, but it is not spaning that records only, It is taking whole records in count when clubing

Re: custom span in timechart

mtulett_splunk — Tue, 21 Nov 2017 04:37:00 GMT

If you want to break your events into separate weeks you can pipe your data into:

<search> | bin _time span=1d@w | timechart <data>

This breaks it into one day blocks, snapping to the start of the week. Note that this uses the US definition of the start of the week (Sunday). To use Monday as the start of the week, for example, you can use span=1d@w1.

Re: custom span in timechart

niketn — Tue, 21 Nov 2017 05:46:30 GMT

What is your data source? The dates mentioned in the question are _time field values or do you have Time stored in a different field?

If you are not having data prior to last 7 days they should not show up in aggregated timechart. Not sure why they show up. Please share data and your SPL.

Re: custom span in timechart

HiroshiSatoh — Tue, 21 Nov 2017 05:53:34 GMT

For example, this is the desired result.
However, performance etc are ignored.

･･･ earliest=-7d@d latest=+1d@d|eval  week=strftime(_time,"%Y-%m-%d")|stats count max(week) as week
|union [search ･･･ earliest=-14d@d latest=-7d@d|eval  week=strftime(_time,"%Y-%m-%d")|stats count max(week) as week]
|union [search ･･･ earliest=-21d@d latest=-14d@d|eval  week=strftime(_time,"%Y-%m-%d")|stats count max(week) as week]
|union [search ･･･ earliest=-28d@d latest=-21@d|eval  week=strftime(_time,"%Y-%m-%d")|stats count max(week) as week]
|union [search ･･･ earliest=-35d@d latest=-28@d|eval  week=strftime(_time,"%Y-%m-%d")|stats count max(week) as week]

Re: custom span in timechart

kamlesh_vaghela — Tue, 21 Nov 2017 15:53:03 GMT

Can you please try this search?

| makeresults | eval Date="2017-09-12" 
| append [ | makeresults | eval Date="2017-09-13"]
| append [ | makeresults | eval Date="2017-09-14"]
| append [ | makeresults | eval Date="2017-09-15"]
| append [ | makeresults | eval Date="2017-09-16"]
| append [ | makeresults | eval Date="2017-09-17"]
| append [ | makeresults | eval Date="2017-09-18"]
| append [ | makeresults | eval Date="2017-09-19"] 
| eval srno=1,Date=strptime(Date,"%Y-%m-%d") 
| accum srno 
| eval srno=floor(srno/8) 
| stats min(Date) as Date count by srno  | eval _time=Date 
| timechart avg(count) as average

Re: custom span in timechart

dsiob — Thu, 23 Nov 2017 10:32:20 GMT

So here is the complete scenario:

I had a single value panel with trend indicator. I have 3 months data for every day. It is showing current month avg and difference between latest two months. What I want is, when I select a date/date-range from date picker, It should show the difference between selected date and avg of one month before from the selected date. So the Trend indicator use timechart span in its query, so I asked before.