Splunk Search

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

demkic
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

vasanthmss
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.

V

View solution in original post

vasanthmss
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.

V

demkic
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

somesoni2
SplunkTrust
SplunkTrust

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

Index This | How many sides does a circle have?

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

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...

Splunkbase | Splunk Dashboard Examples App for SimpleXML End of Life

The Splunk Dashboard Examples App for SimpleXML will reach end of support on Dec 19, 2024, after which no new ...