Say eg I have Category field with NULL value so simply use below code, Category!=NULL ... View more

Thanks @scelikok Works like a charm! Just needed to add ascending sort on _time and a new field to show the month prior as that is really the month that the counts of lines changed is for: index=foo | stats first(_time) as _time min(date_mday) as firstDay latest(date_mday) as lastDay earliest(lineCount) as firstlineCount latest(lineCount) as lastlineCount by date_month | sort +_time | autoregress lastlineCount p=1 | eval lineCount=if(firstDay==1,firstlineCount,lastlineCount_p1) | eval lastmonth=strftime(relative_time(_time,"-1mon"),"%b") | delta lineCount AS linesChanged | convert timeformat="%b" ctime(_time) | table lastmonth lineCount linesChanged    ... View more

I'm assuming you're trying to find the release number for any given log event and therefore work out which release it applies to. Here's an example that shows you how you can do it, assuming you have a CSV file called dates.csv with your release date information in your example What this does is simulate a bunch of dates in your range and give them random pass/fail attributes. The append/eventstats/mvzip/mvexpand/rex technique will then create a row per date, but it's basically the last three lines that will give you the comparison technique to do the range checking.  | makeresults | eval _time=strptime("2020-03-14", "%F") | eval n=mvrange(1,61) | mvexpand n | eval _time=_time-(n*86400) | bin _time span=1m | stats count by _time | append [ | inputlookup dates.csv ] | eventstats values(release) as release values(priorreleasedate) as priorreleasedate values(implementationdate) as implementationdate | eval result=mvindex(mvappend("Pass","Fail"),random() % 2) | eval d=mvzip(release, mvzip(priorreleasedate, implementationdate, ":"), ":") | fields _time count d result | mvexpand d | rex field=d "(?<release>[^:]*):(?<priorreleasedate>[^:]*):(?<implementationdate>.*)" | table _time release priorreleasedate implementationdate result | eval from=strptime(priorreleasedate,"%m/%d/%Y"), to=strptime(implementationdate,"%m/%d/%Y") + 86399 | where _time>=from AND _time<=to | stats count(eval(result="Fail")) as Fail count(eval(result="Pass")) as Pass by release  On your use of 'transaction' you don't seem to be using any kind of id to group common records and in any case, I would always avoid transaction where possible, as it often has unintended side effects. Using stats will almost always give you the same result without the potential headaches. Hope this gives you some pointers.   ... View more

Thanks a lot Rich. This was very helpful. I ended up having to add a few checks to make sure a time difference wasn't being calculated for coalesced WO's with the same eventtype (i.e. export was started more than once but never complete), as well as a time range restriction on the final table output. Here is what ended up working for me: index=... eventtype=export_start OR eventtype=export_in_progress | where isnull(period) OR period=total_periods | eval WO = coalesce(selected_WO, period_WO) | dedup WO eventtype | stats range(_time) as diff by WO | where diff>0 AND diff<=30 | table WO diff | sort -diff | rename WO as "Work Order", diff as "Export Time" | where isnull(period) OR period=total_periods - Narrows events to only the start of the export or the last step (start will have a null period, last export will have 6/6 for example - period = 6, total_periods = 6) | dedup WO eventtype - removes duplicates for events with the same WO and eventtype ... View more