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

Expert Tips from Splunk Professional Services, Ensuring Compliance, and More New ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Observability Release Update: AI Assistant, AppD + Observability Cloud Integrations & ...

This month’s releases across the Splunk Observability portfolio deliver earlier detection and faster ...

Stay Connected: Your Guide to February Tech Talks, Office Hours, and Webinars!

💌Keep the new year’s momentum going with our February lineup of Community Office Hours, Tech Talks, ...