Splunk Search

How do you average count values in certain timeslots?

VanyBerg
Engager

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

0 Karma

cvssravan
Path Finder

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.

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...