SUM & COUNT from a lookup table

[Simona11]

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

[ITWhisperer]

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

[Simona11]

Yes, exactly

[livehybrid]

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

[Simona11]

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

[ITWhisperer]

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)?