Hi, I am trying to report on access requests to actual logins. I have a list of events from our systems of when users have logged in: | table _time os host user clientName clientAddress signature logonType I have a list of requests which cover a time frame and potentially multiple logins to multiple systems: | table key host reporterName reporterEmail summary changeStartDate changeEndDate So i want a list of events, with any corresponding requests (could be none, so i can alert the user/IT) joining on host, user, and _time between changeStartDate and changeEndDate. I do have this working by using map (see below), but it's very slow and not operable over large datasets/times. There must be a better way. I had issues with matching on the time range, and where it may not have a match, and optional username matching based on OS. Does anyone have any ideas? Existing search: ...search...
| table _time os host user clientName clientAddress signature logonType
| convert mktime(_time) as epoch
| sort -_time
| map maxsearches=9999 search="
| inputlookup Request_admin_access.csv
| eval os=\"$os$\"
| eval outerHost=\"$host$\"
| eval user=\"$user$\"
| eval clientName=\"$clientName$\"
| eval clientAddress=\"$clientAddress$\"
| eval signature=\"$signature$\"
| eval logonType=\"$logonType$\"
| eval startCheck=if(tonumber($epoch$)>=tonumber(changeStartDate), 1, 0)
| eval endCheck=if(tonumber($epoch$)<=tonumber(changeEndDate), 1, 0)
| eval userCheck=if(normalisedReporterName==\"$normalisedUserName$\", 1, 0)
| where host=outerHost
| eval match=case(
os==\"Windows\" AND startCheck==1 AND endCheck==1,1,
os==\"Linux\" AND startCheck==1 AND endCheck==1 AND userCheck==1,1)
| appendpipe [
| makeresults format=csv data=\"_time,os,host,user,clientName,clientAddress,signature,logonType,wimMatch
$epoch$,$os$,$host$,$user$,$clientName$,$clientAddress$,$signature$,$logonType$,1\"
]
| where match==1
| eval _time=$epoch$
| head 1
| convert ctime(changeStartDate) timeformat=\"%F %T\" | convert ctime(changeEndDate) timeformat=\"%F %T\"
| fields _time os host user clientName clientAddress signature logonType key reporterName reporterEmail summary changeStartDate changeEndDate"
... View more