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
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"
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]
|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
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"
this is working but we need to append time for weekdays
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
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.
I do not understand. What is your output now and what should it be?
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
And its not displaying sunday
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
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
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
What is the final transforming command you are using to create your statistical table?
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
What's your current query and time-range??
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
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
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.
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