How to write a search for continuous day count?

[srinivasup]

I have a scenario that when i write a search, i will get count for each day. But if there is no count that day, the row is not appearing

Ex:

Day count
Mon 1
Tue 2
Sat 3

This is the output, but here Wed, Thu, Friday days are missing. So i would like hard code the days or want to include missing dates with count zero

Something like this

Day count
Mon 1
Tue 2
Wed 2
Thu 3
Fri 5
Sat 6

Sometimes days may change , please help in writing a search

[HiroshiSatoh]

try this!

|gentimes start=-7|eval day=strftime(endtime,"%a") ,count=0|fields day,count
  |join type=outer day [search (your search)|eval day=strftime(_time,"%a")|stats count by day]

[srinivasup]

|gentimes start=-7 -- it looks for last 7 days.

I want to update start time like earliest time

ex: earliest=-w@w latest=@w, usually it will fetch the records for last week,

How can i pass these earliest and latest values to gentimes start =-w@w end=@w

[woodcock · Accepted solution]

Like this:

| makeresults 
| eval raw="Mon 1::Tue 2::Sat 3" 
| makemv delim="::" raw 
| mvexpand raw 
| rex field=raw "^(?<Day>\S+)\s+(?<count>\S+)$" 
| fields - _time raw 

| rename COMMENT AS "Everything above fakes sample (intermediate) data; everything below is your solution"
| rename COMMENT AS "YOUR EXISTING SEARCH HERE"

| append 
    [| makeresults count=7 
    | streamstats count AS serial 
    | eval Day=case(serial=1, "Mon", serial=2, "Tue", serial=3, "Wed", serial=4, "Thu", serial=5, "Fri", serial=6, "Sat", serial=7, "Sun", true(), "N/A")
    | fields - _time
    | eval count=0 ]
| stats sum(count) AS count first(serial) AS serial BY Day
| sort 0 serial
| fields - serial
| eval count=if(count=0, null(), count)
| filldown count
| fillnull count value="0"

[srinivasup]

this is working but we need to append time for weekdays

[srinivasup]

Input:

Day count
Mon May.2.2017 1
Tue May.3.2017 2
Sat May.7.2017 3

Output:

Day count
Mon 1
Tue May.3.2017 2
Wed ...............

Thu ...............

Fri ...............

Sat May.7.2017 3
Sun ...............

As per the previous query we could able to add week days but now we want to add days corresponding to each week day

[woodcock]

OK, try this:

| makeresults 
| eval raw="Mon 1::Tue 2::Sat 3" 
| makemv delim="::" raw 
| mvexpand raw 
| rex field=raw "^(?<Day>\S+)\s+(?<count>\S+)$" 
| fields - _time raw 
| rename COMMENT AS "Everything above fakes sample (intermediate) data; everything below is your solution"
| rename COMMENT AS "YOUR EXISTING SEARCH HERE"
| append 
    [| makeresults count=7 
    | streamstats count AS serial 
    | eval serial = serial - 1
    | eval Day=case(serial=0, "Sun", serial=1, "Mon", serial=2, "Tue", serial=3, "Wed", serial=4, "Thu", serial=5, "Fri", serial=6, "Sat", true(), "N/A")
    | eval time = relative_time(now(), "@w") + (serial  * 60 * 60 * 24)
    | eval count=0]
| stats sum(count) AS count first(serial) AS serial first(time) AS Day_Detail BY Day
| sort 0 serial
| fields - serial
| eval count=if(count=0, null(), count)
| filldown count
| fillnull count value="0"
| fieldformat Day_Detail = strftime(Day_Detail, "%a %b %d %Y")

It may not be exactly perfect but it has all the moving parts that you should need to massage it to your needs.

[woodcock]

I do not understand. What is your output now and what should it be?

[srinivasup]

Input:

Day count
Mon May.2.2017 1
Tue May.3.2017 2
Sat May.7.2017 3

Output:

Day count
Mon 1
Tue May.3.2017 2
Wed ...............
Thu ...............
Fri ...............
Sat May.7.2017 3
Sun ...............

As per the previous query we could able to add week days but now we want to add days corresponding to each week day

[srinivasup]

And its not displaying sunday

[somesoni2]

Using @cmerriman's solution, this should do it
Updated

|inputlookup Day.csv
| eval _time=strptime(Day,"%a %b.%d.%Y")
| makecontinuous | eval Day=strftime(_time,"%a %b.%d.%Y") | fields - _time
| fillnull value=0 count

[srinivasup]

Day _time count
Mon May.2.2017 2017-05-02 1
Tue May.3.2017 2017-05-03 2
2017-05-04 0
2017-05-05 0
2017-05-06 0
Sat May.7.2017 2017-05-07 3

This is how it is comming but under Day column it should display weekdays but it is showking blanks, please help

[cmerriman]

can you use the |makecontinuous command on your _time field? followed by |fillnull count value=0

https://docs.splunk.com/Documentation/Splunk/6.6.0/SearchReference/Makecontinuous

[niketn]

What is the final transforming command you are using to create your statistical table?

[srinivasup]

my query |inputlookup Day.csv and output is as follows

Day count

Mon May.2.2017 1
Tue May.3.2017 2
Sat May.7.2017 3

i want to see the output

Mon May.2.2017 1
Tue May.3.2017 2
wed 0
Thu 0
Fri 0
Sat May.7.2017 3

tried above query but didnt work

[somesoni2]

What's your current query and time-range??

[somesoni2]

Assuming that values of days are always Mon to Sat, you can try like this

your current search 
| append [| gentimes start=-1 | eval Day=split("Mon Tue Wed Thu Fri Sat"," ") | table Day | mvexpand Day | eval count=0 ]
| stats max(count) as count by Day

[srinivasup]

sorry for the confusion

Day count
Mon Mar.1.2017 1
Tue Mar.2.2017 2
Thu Mar.3.2017 4
Fri Mar.5.2017 7
Sat Mar.6.2017 8

now want to include missing day count as 0

[somesoni2]

Give this a try

your current search 
 | append [| gentimes start=-1 | addinfo | eval Day=mvrange(info_min_time,info_max_time+86400,86400)  | table Day | mvexpand Day | eval Day=strftime(Day,"%a %b.%d.%Y") | eval count=0 ]
 | stats max(count) as count by Day

May need to adjust mvrange command upper limit.

[srinivasup]

my query |inputlookup Day.csv and output is as follows

Day count

Mon May.2.2017 1
Tue May.3.2017 2
Sat May.7.2017 3

i want to see the output

Mon May.2.2017 1
Tue May.3.2017 2
wed 0
Thu 0
Fri 0
Sat May.7.2017 3

tried above query but didnt work