topic Per day and Per second results not matching up. in Splunk Search

Per day and Per second results not matching up.

angersleek — Sun, 08 Sep 2019 20:16:48 GMT

I am running following queries to get event counts average per second and per day over a weeks period but the results makes no sense.

This is the query to get average per day over a week:

index=some_some2_idx ns=something app_name=my-api STATUS_CODE!=5*
| timechart span=1d count as requests_per_second | stats avg(requests_per_second)

The average per day result is 3010.5714285714284

This is the query to get average per second over a week:

    index=some_some2_idx ns=something app_name=my-api STATUS_CODE!=5*
    | timechart span=1s count as requests_per_second | stats avg(requests_per_second)

The average per second result is 1.4238227146814404

There are 86400 seconds in a day so 86400 * 1.4238227146814404 = 123018 and some change. I am not expecting an exact match but this is not even remotely close to 3010.5714285714284. What am I missing. Is this is expected or is there something wrong with my query?

Re: Per day and Per second results not matching up.

adonio — Mon, 09 Sep 2019 03:30:42 GMT

lets imagine, that you are looking over 2 days, and the grand total of events (count) is 100,000
on your first query, you are counting events in each day, lets say, 60,000 in day 1 and 40,000 on day 2
now you have 2 events, that you are averaging. in this case the average will be 50,000
on the second query, you are breaking the total count of (100,00 0 in 2 days) to a count by second, meaning, you will have 100,000 events spread on 86,400 * 2 seconds, now you are averaging that.
obviously, 100,000 / 172,800 (is less then 1) is far away from 50,000 and i hope the above example explains why.
the main question is, what are you trying to report? what is the question you have on your data?
let us know your question, and we will assist with writing the correct query

Re: Per day and Per second results not matching up.

angersleek — Mon, 09 Sep 2019 08:33:54 GMT

Thanks for replying. All I am trying to do is to get a count on how many requests came in (which I believe is called events in this case) per second and per day based on each app_name (I am checking it over a week's period).

Re: Per day and Per second results not matching up.

adonio — Wed, 30 Sep 2020 02:05:41 GMT

in this case, you have to count the events per app, (app_name) and then divide by the amount of seconds to create the average with an eval statement
lets say you are counting events in 24 hours exactly, and you want the average for hour, minute and second:
earliest=-25h@h latest=-1h@h index = sourcetype= ... all other filters here ... | stats count as event_count | rename COMMENT as "the above counts events in a 24 hours period" | eval average_per_hour = round(event_count / 60, 2) | eval average_per_minute = round(event_count / 3600,2) | eval average_per_second = round(event_count / 86400, 2) ...

hope it helps