@bowesmana I understand the technique now! After some tweaking, I now get the expected results, some important points for anyone who is reading this... group by parent table so that all the child records are in multi-value columns find the multi-value index that matches remove the non-matching multi-value records list() does not dedup results, this is needed when filtering the mv results by index | convert mktime(_time) as epoch
| inputlookup append=t Request_admin_access.csv
``` need the same use column name for stats to group on ```
| eval userForMatching=coalesce(normalisedUserName, normalisedReporterName)
``` group by events, so that all the possible child requests are in mv columns ```
| stats list(epoch) as epoch list(key) as key values(reporterName) as reporterName values(reporterEmail) as reporterEmail list(summary) as summary list(changeStartDate) as changeStartDate list(changeEndDate) as changeEndDate values(user) as user values(os) as os values(clientName) as clientName values(clientAddress) as clientAddress values(signature) as signature values(logonType) as logonType by host userForMatching
``` expand the events ```
| mvexpand epoch
``` find the mv index where event._time between request.start and request.end dates ```
| eval isAfterStart=mvmap(changeStartDate, if(epoch>=changeStartDate, 1, 0))
| eval isBeforeEnd=mvmap(changeEndDate, if(epoch<changeEndDate, 1, 0))
| eval idx=mvfind(mvzip(isAfterStart, isBeforeEnd), "1,1")
| rename epoch as _time
``` filter to just the matching request ```
| eval key=mvindex(key, idx)
| eval reporterName=if(isnull(idx),"",reporterName)
| eval reporterEmail=if(isnull(idx),"",reporterEmail)
| eval summary=mvindex(summary, idx)
| eval changeStartDate=mvindex(changeStartDate, idx)
| eval changeEndDate=mvindex(changeEndDate, idx)
``` human readable times ```
| convert ctime(changeStartDate) timeformat="%F %T" | convert ctime(changeEndDate) timeformat="%F %T"
| table _time os host user clientName clientAddress signature logonType key reporterName reporterEmail summary changeStartDate changeEndDate
| sort -_time Many thanks
... View more