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
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.