Calulated value compared to averge over time

[swdonline]

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.

[swdonline · Accepted solution]

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