Can you pull previous values from an inputlookup using streamstats?

[asmithe]

I have a large mixed search, part of the resulting data is being pulled from search and part from an inputlookup csv table.

Once the retrieved + inputlookup data is in, it is sorted (-_time).

I then want to pull values from the prior event (inputlookup) data to fill in null values in the search/retrieved data.

The reason i have the inputlookup is because, while this process works as a subsearch, that subsearch is computationally expensive and takes a long time. I was hoping to save some time and cycles with the canned data.

FWIW, here is the post inputlookup part of the search.

|  inputlookup append=true max=50000000 this_week_spaces.csv
| sort limit=0 -_time 
| reverse
| streamstats current=f last(sn) as l27_sn2 last(cycle) as l27_cycle last(sc) as l27_sc
| reverse 


| eval sn=if(isnull(sn),l27_sn,sn)
| eval cycle=if(isnull(cycle),l27_cycle,cycle)
| eval space_cycle=if(isnull(sc),l27_sc,sc)
| streamstats sum(last_idle) as space_idle by space_cycle 
| streamstats current=f last(sn) as n27_sn2

It works, in another search, as a subsearch, something like this:

...
| append maxtime=500000  maxout=10000000 timeout=1000000 [search index=flowspaces sourcetype="growl_xml" eventtype="Space Changed" | replace "Collect & Tag" with "Tag" in title | replace "Admin" with "Administer" in title | replace "Read & Note" with "Read" in title|  eval time=_time+(exact(1.999)) | eval _time=time | eval spacename=if(isnull(spacename),l27_spacename,spacename) | eval spacetime=_time | streamstats current=f last(spacetime) as n27_spacetime last(time) as n27_timesp  | eval spaceduration=n27_spacetime-_time | reverse | streamstats current=t count as space_cycle |  eval duration=spaceduration | streamstats current=f window=1 global=f last(duration) as l27_space_duration | eval endtime=strftime(n27_spacetime,"%m/%d/%y %l:%M:%S.%3N %p") | eval cycle_type="space" | eval starttime=_time | eval this_endtime=n27_spacetime | eval et=17

]
...
| sort limit=0 -_time 
| reverse
| streamstats current=f last(spacename) as l27_spacename last(cycle) as l27_cycle last(space_cycle) as l27_space_cycle
| reverse 


| eval spacename=if(isnull(spacename),l27_spacename,spacename)
| eval cycle=if(isnull(cycle),l27_cycle,cycle)
| eval space_cycle=if(isnull(space_cycle),l27_space_cycle,space_cycle)
| eval et=if(eventtype="Space Changed",17,if(eventtype="App Active",14,if(eventtype="UI Active",15,if(eventtype="Observer Failed",16,et))) ) 
| streamstats sum(last_idle) as space_idle by space_cycle 
| streamstats current=f last(spacename) as n27_spacename

Any thoughts or tweaks appreciated!

[asmithe · Accepted solution]

Yes you can.

However, to use streamstats (which takes 'now' as its point of reference) to pull the prior value from an undetermined number of previous events (until the next non-null value) you need to clear the values field=null() after a transaction and prior to the inputlookup. I also tried to first reverse the temporal order of the inputlookup data by using | reverse or sort +_time while preparing the date for export using outputlookup.

I thought that importing backwards data was the trick, but now I believe it has to do with clearing mvlist field values that appear null in table output but are not really null.

So it seems that I stumbled on this accidentally with my subsearch and could not recall how do recreate this. Glad to have it in the Community Answers for next time I forget.