Splunk SPL - How to extract associated logs from stats queries?

[ahendler1]

Hello,

I have a search which returns the moving average # of logs for a 12hr period (1hr prior) and the most recent hour, where the most recent hour has been at least double the moving average.

index=1234 message="auth failure*" earliest=-13h@ latest=-1h@
 | bucket _time span=1h 
 | stats count as logCountByHour by _time 
 | stats avg(logCountByHour) as prevTwelveHourAvg
 | appendcols [search index=1234 message="auth failure*" earliest=-h@h latest=@h 
 | stats count as lastHour ] 
 | where lastHour>2*prevTwelveHourAvg

How would I extract the associated logs and fields out of this query (eg the logs of the most recent hour).

Thank you for your help!

[DalJeanis]

Try something like this...

index=1234 message="auth failure*" earliest=-13h@h latest=-0h@h
| bucket _time as Hour span=1h 

| rename COMMENT as "process all of the records through an appendpipe to see if traffic is double"
| appendpipe [
    | stats count as logCountByHour by Hour 
    | eventstats max(Hour) as maxHour
    | stats 
         max(maxHour) as maxHour
         avg(eval(case(Hour!=maxHour,logCountByHour))) as prevTwelveHourAvg 
         avg(eval(case(Hour=maxHour,logCountByHour))) as lastHour
    | eval myflag=case(lastHour>2*prevTwelveHourAvg,"alertme")

    | rename COMMENT as "setting Hour to maxHour will connect the myflag field (if set) to each record in that Hour."
    | eval Hour = maxHour
    ] 

| rename COMMENT as "Kill all events but the last hour, then roll over whatever we received from the appendpipe"
| where Hour == maxHour
| eventstats max(myflag) as myflag by Hour

| rename COMMENT as "Pass the last Hour's events, only if the flag was set."
| where isnotnull(myflag)

[ahendler1]

@DalJeanis

This works great! However, it returns the stats for the fields Hour, lasHour, maxHour, myflag, and prevTwelveHourAvg

Ultimately I am trying a csv export of those logs associated with the maxHour in the case it is above that 2*prevTwelveHourAvg threshold.

Is there a way to alter the query so that it is returning the logs for export from hours 12-13?

[DalJeanis]

@ahendler1 - That's what it should be doing. Verify, using the _time field, that the records are for the final hour. Then just use the |fields or |table commands to limit the output to the fields that you want to see, including _raw.

|table _time _raw ...whatever other fields you want...