Like you said, there are LOTS of ways that would be just fine to do this. Here's one...
(sourcetype=*:Security ((EventCode=4624 user32 ) OR EventCode=4625) host="*" user=$t_user$ )
OR (sourcetype="changes" changecoordinator=$t_user$)
| rename COMMENT as "Eliminate all fields we don't need"
| fields _time, user, src_nt_host, signature, changeid, startdate, enddate, changecoordinator,
| rename COMMENT as "Kill logon records that are not off-hours"
| eval hour = tonumber(strftime(_time,"%H"))
| eval wknd = tonumber(strftime(_time,"%w"))
| where (sourcetype="changes") OR (hour<=8 or hour>=19) OR (wknd==0 OR wknd==6 )
Now you have all the data in one file. You can take the user= and the changecoordinator= out of the two halves of the search if I've misunderstood the value there, leaving only the token.
It seems like you are looking for the last off-hours logon for each change request. We can sort the records into _time order, and use streamstats to copy down the last logon within an arbitrary window, for instance 8 hours. But first, we need to establish a single field for the user for both kinds of records. In this case, we'll use user .
| rename COMMENT as "sort records and propagate logon time"
| eval user=coalesce(user,changecoordinator)
| sort 0 user _time
| streamstats time_window=8h last(eval(case(sourcetype!="changes",_time))) as lastLogon
last(eval(case(sourcetype!="changes",signature))) as lastSignature by user
| rename COMMENT as "roll together all change records"
| stats count values(changeid) as changeid by user lastLogon lastSignature
| rename lastLogon as _time
It might be better practice to do the elimination of on-hours records AFTER rolling the records together. That would make the time_window unnecessary, since your changecoordinator would ALWAYS have to have logged on sometime, and we can just kill the on-hours ones all at the same time.
(sourcetype=*:Security ((EventCode=4624 user32 ) OR EventCode=4625) host="*" user=$t_user$ )
OR (sourcetype="changes" changecoordinator=$t_user$)
| rename COMMENT as "Eliminate all fields we don't need"
| fields _time, user, src_nt_host, signature, changeid, startdate, enddate, changecoordinator,
| rename COMMENT as "sort records and propagate logon time"
| eval user=coalesce(user,changecoordinator)
| sort 0 user _time
| streamstats last(eval(case(sourcetype!="changes",_time))) as lastLogon
last(eval(case(sourcetype!="changes",signature))) as lastSignature by user
| rename COMMENT as "roll together all change records"
| stats count values(changeid) as changeid by user lastLogon lastSignature
| rename lastLogon as _time
| rename COMMENT as "Kill logon records that are not off-hours"
| eval hour = tonumber(strftime(_time,"%H"))
| eval wknd = tonumber(strftime(_time,"%w"))
| where (hour<=8 or hour>=19) OR (wknd==0 OR wknd==6 )
... View more
