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!

Splunk Observability Cloud | Unified Identity - Now Available for Existing Splunk ...

Raise your hand if you’ve already forgotten your username or password when logging into an account. (We can’t ...

Index This | How many sides does a circle have?

February 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...