Splunk Search

How to add values of multiple fields and display daily total for past 7 days

Explorer

Hi all,

I am having trouble figuring out how to multiply the number of events by the values that are given in the fields of those events and then plotting those results for the last 7 days.

For example: I have 3 logs for February 1 where each log has event=total_cards and the value for total_cards is 1000, 500, 400.

I would like to be able to essentially add the value of total_cards (1000+500+400) and display that result for each day in the last 7 days.

base search |stats count by total_cards | eval total = (total_cards*count)  | eventstats sum(total) AS Total | table Total | table _time Total

My code from above is essentially giving me the result that I was (example: 1,900) but now I need calculation to be done for each day in the last 7 days...

0 Karma
1 Solution

Motivator

based on the previous comments i understand your data is something like this

date    total_cards
1-Feb   1000
1-Feb   400
1-Feb   500
2-Feb   400
2-Feb   300
2-Feb   200
2-Feb   100

Expected is

date    total_cards
1-Feb   1900
2-Feb   1000

then try the below search,

... base search | bin span=1d _time  | stats sum(total_cards) as Total by _time

I am not sure why you have calculated the multiplication of count and total_cards. In-case that's what you want means try the below query,

 base search | bin span=1d _time |stats count by total_cards,_time | eval total = (total_cards*count)  | eventstats sum(total) AS Total  by _time | table _time Total

Hope this helps you.

View solution in original post

Motivator

based on the previous comments i understand your data is something like this

date    total_cards
1-Feb   1000
1-Feb   400
1-Feb   500
2-Feb   400
2-Feb   300
2-Feb   200
2-Feb   100

Expected is

date    total_cards
1-Feb   1900
2-Feb   1000

then try the below search,

... base search | bin span=1d _time  | stats sum(total_cards) as Total by _time

I am not sure why you have calculated the multiplication of count and total_cards. In-case that's what you want means try the below query,

 base search | bin span=1d _time |stats count by total_cards,_time | eval total = (total_cards*count)  | eventstats sum(total) AS Total  by _time | table _time Total

Hope this helps you.

View solution in original post

Explorer
base search | bin span=1d _time  | stats sum(total_cards) as Total by _time

This did it! I feel like I was missing the concept of "bin". Can you please explain how this command works? I guess I don't really understand the use case?

Thank you!

0 Karma

Revered Legend

The bin or bucket command will create buckets/bins of the specified field based on specified option. E.g. above, the field _time will be aggregated into bins spanning 1 day, so _time values (actual values are in epoch, just representing in human-readable here for understanding) 2017-02-01 00:17 OR 2017-02-01 09:50 OR 2017-02-01 17:55 will be part of same bucket 2017-02-01 00:00 (start of the bin i.e. day). See this for more information: http://docs.splunk.com/Documentation/Splunk/6.5.2/SearchReference/Bin

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!