topic Re: Fillnull until specific time? in Splunk Search

Fillnull until specific time?

cmzhu — Thu, 13 Jul 2017 18:21:47 GMT

Is there a way to get fillnull to work over all entries up until a certain time?

Right now, I'm evaluating a field in a subsearch with earliest=@d, latest=now and then working with the field outside of the subsearch. I want fillnull to work on that field up until now, but when I put "| fillnull value=0 field" at the end of the subsearch, it's not working. When I put that statement outside of the subsearch, fillnull is working over all times, not until now.

Re: Fillnull until specific time?

cmerriman — Thu, 13 Jul 2017 18:37:56 GMT

can you show a snippet of your search? i see no immediate reason why that wouldn't work in a subsearch, so i'm curious how the subsearch is joined to the actual search.

Re: Fillnull until specific time?

cmzhu — Thu, 13 Jul 2017 19:14:37 GMT

| makeresults count=288 | eval TimeDeltaSec=300 | accum TimeDeltaSec as TimeOffsetSec | eval _time=relative_time(now(),"@d-5m")+TimeOffsetSec | fields - TimeDeltaSec,TimeOffsetSec | eval TimeSlice=strftime(_time,"%H:%M") 
| join type=left TimeSlice [
search earliest=-30d@d latest=@d
...
| eventstats median(absDev) as mad by TimeSlice
| eval LowNormalCt=(med-mad*3), HighNormalCt=(med+mad*3)
| rename med as AvgNormalCt]
| fillnull value=0 HighNormalCt, AvgNormalCt, LowNormalCt
| join type=left TimeSlice
[search
earliest=@d latest=now
...
| stats first(ReqCt5m) as RC5 by TimeSlice, ActivSlice, ActivSite, CgClass
| stats sum(RC5) as LiveRequestCt by TimeSlice 
]
| table TimeSlice,LiveRequestCt,HighNormalCt,AvgNormalCt,LowNormalCt

I've tried adding "| fillnull value=0 LiveRequestCount" right before the last ']', but it's not working. When I add the statement after the bracket, it's filling every TimeSlice (as expected).

Re: Fillnull until specific time?

cmerriman — Thu, 13 Jul 2017 19:40:32 GMT

how are you calculating TimeSlice? are they the same buckets in every single search? I see with the makeresults command it's every 5 minutes. If when you break the search apart, the TimeSlice is a different bucket, then that could be the problem.

Re: Fillnull until specific time?

cmzhu — Thu, 13 Jul 2017 19:52:42 GMT

TimeSlices is calculated the same way across the search; it's a field value in the indexes of the subsearch.
| eval TimeSlice=strftime(_time,"%H:%M")

Re: Fillnull until specific time?

DalJeanis — Thu, 13 Jul 2017 20:01:55 GMT

| fillnull value=0 fieldA

is 100% equivalent to

| eval fieldA=coalesce(fieldA,0)

so you want something like

| eval fieldA = coalesce(fieldA, if(_time<now(), 0, null() )  )

Re: Fillnull until specific time?

cmzhu — Thu, 13 Jul 2017 20:23:04 GMT

thanks so much!