Splunk Search

How to write a search for continuous day count?

Explorer

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

Tags (3)
0 Karma
1 Solution

Esteemed Legend

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"

View solution in original post

0 Karma

Champion

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]
0 Karma

Explorer

|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

0 Karma

Esteemed Legend

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"

View solution in original post

0 Karma

Explorer

this is working but we need to append time for weekdays

0 Karma

Explorer

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

0 Karma

Esteemed Legend

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.

0 Karma

Esteemed Legend

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

0 Karma

Explorer

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

0 Karma

Explorer

And its not displaying sunday

0 Karma

SplunkTrust
SplunkTrust

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
0 Karma

Explorer

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

0 Karma

Super Champion

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

SplunkTrust
SplunkTrust

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

Explorer

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

0 Karma

SplunkTrust
SplunkTrust

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

0 Karma

SplunkTrust
SplunkTrust

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
0 Karma

Explorer

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

0 Karma

SplunkTrust
SplunkTrust

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.

0 Karma

Explorer

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

0 Karma