Splunk Search

Calulated value compared to averge over time

swdonline
Path Finder

I have a large data set with values like this:

#date,host,eventid,eventCnt
01/01/2013,myhost1,100,5
01/01/2013,myhost2,122,8
01/01/2013,myhost1,130,10
01/01/2013,myhost2,100,25
01/01/2013,myhost1,130,3

I would like to generate a table that shows the delta between the sum of eventCnt on a given day (per host) compared against the 30 day average(sum of eventCnt) for each host. Something like this:

#date host uniqueEvtIDs 30dAvgEventCnt EventCntPerDay EventCntDelta(PerDay/30dAvg)
01/01/2013 myhost1 3 10 18 1.8
01/01/2013 myhost2 2 50 33 .66

What is the most efficient way to accomplish this? It seems like I need a 30-day summary index, which is then fed into a search pipeline for each day, but I yet to figure out the right combination of search commands.

0 Karma
1 Solution

swdonline
Path Finder

I found a workable solution to the problem, though I am unsure if it's the most efficient way. First, I established a Saved Search called "Event Stats By Host" for calculated value. The time range was set from -30d@d to @d. Accelerate this search is checked with a Summary range set to 1 month.

index=myindex | bucket span=1d | stats dc(eventid) as idCnt, sum(eventCnt) as eventsPerDay by _time host

Next, I conduct searches to compare yesterday to a 30 day average and calculate deltas:

|savedsearch "Event Stats By Host" | stats latest(_time) as _time,latest(idCnt) as lidCnt,avg(eventsPerDay) as aEventsPerDay,latest(eventCnt) as lEventsPerDay | eval eventCntDelta=lEventsPerDay/aEventsPerDay | table _time lidCnt aEventsPerDay lEventsPerDay eventCntDelta

View solution in original post

0 Karma

swdonline
Path Finder

I found a workable solution to the problem, though I am unsure if it's the most efficient way. First, I established a Saved Search called "Event Stats By Host" for calculated value. The time range was set from -30d@d to @d. Accelerate this search is checked with a Summary range set to 1 month.

index=myindex | bucket span=1d | stats dc(eventid) as idCnt, sum(eventCnt) as eventsPerDay by _time host

Next, I conduct searches to compare yesterday to a 30 day average and calculate deltas:

|savedsearch "Event Stats By Host" | stats latest(_time) as _time,latest(idCnt) as lidCnt,avg(eventsPerDay) as aEventsPerDay,latest(eventCnt) as lEventsPerDay | eval eventCntDelta=lEventsPerDay/aEventsPerDay | table _time lidCnt aEventsPerDay lEventsPerDay eventCntDelta

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