Hi @Casial06 Firstly I think you should be able to achieve this with a stats rather than a join, I'll show an example below. The other thing to consider is that using a span of 4 hours might cause ...
See more...
Hi @Casial06 Firstly I think you should be able to achieve this with a stats rather than a join, I'll show an example below. The other thing to consider is that using a span of 4 hours might cause incorrect matchings depending on when within the 4 hour span the activity occurs. For example, if an account is locked at 11:50 and unlocked at 12:05, the 4 hour span might split into 08:00-12:00 and 12:00-16:00 - meaning that the lock and unlock are captured in different spans. Instead you could look at just checking if there has been a lock since the last unlock, or no unlocks. Check the following and see if its useful, I've generated some sample data to work through some scenarios: | makeresults format=csv data="_time,Account_Name,EventCode,host,Workstation_Name,src_ip
2025-04-12T08:00:00Z,Acct1,4740,hostA,workA,10.1.1.1
2025-04-12T09:00:00Z,Acct1,4740,hostB,workB,10.1.1.1
2025-04-12T13:00:00Z,Acct1,4767,hostB,workB,10.1.1.1
2025-04-12T08:10:00Z,Acct2,4740,hostC,workC,10.2.2.2
2025-04-12T09:12:00Z,Acct2,4740,hostC,workD,10.2.2.2
2025-04-12T14:15:00Z,Acct2,4740,hostE,workD,10.2.2.2
2025-04-12T10:00:00Z,Acct3,4740,hostD,workF,10.3.3.3
2025-04-12T15:00:00Z,Acct3,4767,hostD,workG,10.3.3.3
2025-04-12T11:00:00Z,Acct4,4740,hostG,workH,10.4.4.4
2025-04-12T15:00:00Z,Acct4,4767,hostG,workH,10.4.4.4
2025-04-12T13:00:00Z,Acct5,4740,hostH,workI,10.5.5.5
2025-04-12T14:00:00Z,Acct1,4740,hostA,workA,10.1.1.1"
| eval _time=strptime(_time,"%Y-%m-%dT%H:%M:%SZ")
| eval UnlockTime=IF(EventCode=4767,_time,null())
| eval LockTime=IF(EventCode=4740,_time,null())
| stats earliest(LockTime) as firstLockTime, latest(LockTime) as lastLockTime, latest(UnlockTime) as lastUnlockTime, range(_time) as timeRange, count(eval(EventCode=4740)) as Locked, count(eval(EventCode=4767)) as Unlocked by Account_Name
| where lastLockTime>lastUnlockTime OR isnull(lastUnlockTime) Did this answer help you? If so, please consider: Adding karma to show it was useful Marking it as the solution if it resolved your issue Commenting if you need any clarification Your feedback encourages the volunteers in this community to continue contributing