Splunk Search

How to write a search for continuous day count?

srinivasup
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

woodcock
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

HiroshiSatoh
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

srinivasup
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

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

srinivasup
Explorer

this is working but we need to append time for weekdays

0 Karma

srinivasup
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

woodcock
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

woodcock
Esteemed Legend

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

0 Karma

srinivasup
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

srinivasup
Explorer

And its not displaying sunday

0 Karma

somesoni2
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

srinivasup
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

cmerriman
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

niketn
Legend

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

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

srinivasup
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

somesoni2
SplunkTrust
SplunkTrust

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

0 Karma

somesoni2
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

srinivasup
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

somesoni2
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

srinivasup
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
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...