How do you average count values in certain timeslots?

[VanyBerg]

Dear Community,

I got a use case I seem to be too inexperienced with to complete on my own. Since I just started delving into splunk I still lack alot knowledge, so I would be glad for your advice.

I want:
- Count all DNS queries of a source IP in 8 hour slices per day (to make it easier to explain: timeslot t1=0-8, t2=8-16, t3=16-24)
- Calculate the average of each timeslot the last 7 days ( average of t1 on monday - sunday, average of t2 on mo-su etc.)

I already tried:
- Trying to eval the timespan in 8 hour slots and then do a count

index=dns 
| eval t1 = relative_time(now(), "-8h")
| eval t2 = relative_time(t1, "-8h")
| eval t3 = relative_time(t2, "-8h")
| stats count(query) by src, t1, t2, t3

Result:
I always get the same result, nevermind which t variable I select. When I display the t field values I get epoch time stamps, so seems it's not really a timespan

• Tried the timechart command, which works fine to some point but since I don't have values to compare I just get the same results for count and average

  index=dns earliest=-24h@h latest=@h
  | timechart count(query) as average count span="8h" by src limit=10
  

Is it even possible to do what I want?
Thanks alot for your ideas,

best regards
VB

[cvssravan]

Hi VanyBerg,

As you didn't mention any time field, I have taken _time as reference

I used _internal index, you can try this and modify the query per your requirement.

index=_internal | eval time_hour=strftime(_time, "%H") | eval time_hour=case(time_hour <9,"1",time_hour <17,"2", time_hour <24, "3") | stats count(_raw) as count by time_hour | appendcols [ search index=_internal | stats count as Total_count ] | eval Average_value=count/Total_count

You need to add src to stats if you need to get stats by src

Hope it helps.