topic Re: SUM & COUNT from a lookup table in Splunk Search

SUM & COUNT from a lookup table

Simona11 — Wed, 25 Jun 2025 12:14:02 GMT

I have a lookup table with daily records which includes: area, alarm description, date, number of bags per area and for that specific day (repetitive number). There is a timestamp for each alarm, and a bag column repeating the total bags for that day (same number appears multiple times because the same day has multiple alarm rows). I want to:

1) compute the total number of bags for the whole 3-month period.

2) compute the total number of alarm events (counted as total occurrences across 3 months).

What is the best approach in Splunk enterprise to get both in the same final stats result?

Example of scenario:

AREA	ALARM DESCRIPTION	TOTAL DAILY BAGS	TIME
1111	TRIGGER	18600	01/03/2024
1111	TRIGGER	18600	01/03/2024
1222	FAILURE	18600	01/03/2024
1323	FAILURE	18600	01/03/2024
1323	HAC	18600	01/03/2024
1222	FAILURE	33444	01/02/2024
1111	FAILURE	33444	01/02/2024
1323	TRIGGER	33444	01/02/2024

Re: SUM & COUNT from a lookup table

livehybrid — Wed, 25 Jun 2025 12:50:57 GMT

Hi @Simona11

You could try:

| timechart span=1d latest("TOTAL DAILY BAGS") as daily_bags, count as total_alarms |stats sum(total_alarms) as total_alarms, sum(daily_bags) as total_bags

| makeresults count=8 | streamstats count as row | eval AREA=case(row=1,"1111", row=2,"1111", row=3,"1222", row=4,"1323", row=5,"1323", row=6,"1222", row=7,"1111", row=8,"1323") | eval "ALARM DESCRIPTION"=case(row=1,"TRIGGER", row=2,"TRIGGER", row=3,"FAILURE", row=4,"FAILURE", row=5,"HAC", row=6,"FAILURE", row=7,"FAILURE", row=8,"TRIGGER") | eval "TOTAL DAILY BAGS"=case(row<=5,18600, row>5,33444) | eval TIME=case(row<=5,"2024-03-01", row>5,"2024-02-01") | eval _time=strptime(TIME,"%Y-%m-%d") | timechart span=1d latest("TOTAL DAILY BAGS") as daily_bags, count as total_alarms | stats sum(total_alarms) as total_alarms, sum(daily_bags) as total_bags

🌟 Did this answer help you? If so, please consider:

Adding karma to show it was useful
Marking it as the solution if it resolved your issue
Commenting if you need any clarification

Your feedback encourages the volunteers in this community to continue contributing

Re: SUM & COUNT from a lookup table

ITWhisperer — Wed, 25 Jun 2025 12:50:19 GMT

What would be the expected result from your sample data? 8 events and 52044 total bags or something else?

| bin span=1d TIME | stats count latest("TOTAL DAILY BAGS") as TOTAL_DAILY_BAGS by TIME | stats sum(count) as total_events sum(TOTAL_DAILY_BAGS) as total_daily_bags

If your TIME field is not already the date (as shown in your sample), you may need to bin it first

Re: SUM & COUNT from a lookup table

Simona11 — Wed, 25 Jun 2025 12:47:50 GMT

Yes, exactly

Re: SUM & COUNT from a lookup table

Simona11 — Thu, 26 Jun 2025 13:36:36 GMT

Unfortunately it is still not working as I am working with a consistent list of multiple areas, and descriptions. Are there other approaches that I might try out? Thank you

Re: SUM & COUNT from a lookup table

ITWhisperer — Thu, 26 Jun 2025 17:46:21 GMT

Please provide more examples of the events you are dealing with, and include your desired results, and what you are getting (and why it is not correct)?