SOLVED

User Events Per Second KQL

%3CLINGO-SUB%20id%3D%22lingo-sub-3064308%22%20slang%3D%22en-US%22%3EUser%20Events%20Per%20Second%20KQL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3064308%22%20slang%3D%22en-US%22%3E%3CP%3EHey!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hope%20you're%20all%20doing%20well!%3C%2FP%3E%3CP%3EI've%20been%20working%20with%20a%20monthly%20report%20with%20Sentinel%20and%20PowerBI%20-%20and%20I've%20been%20trying%20to%20get%20a%20%22User%20EPS%20Count%22.%20This%20would%20be%20a%20KQL%20statement%20that%20looks%20at%20the%20last%2030%20days%20of%20events%2C%20and%20searches%20based%20on%20a%20user%3F%20For%20example%20User1%20has%20an%20average%20of%206000%20events%20linked%20to%20their%20account%20in%20a%20month%2C%20and%20User%202%20has%203000%20events%20etc.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20this%20possible%20in%20KQL%20or%20do%20I%20have%20to%20do%20make%20a%20few%20searches%20and%20correlate%20based%20around%20that%3F%3C%2FP%3E%3CP%3ETIA%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3064465%22%20slang%3D%22en-US%22%3ERe%3A%20User%20Events%20Per%20Second%20KQL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3064465%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1274406%22%20target%3D%22_blank%22%3E%40CharlieK95%3C%2FA%3E%26nbsp%3BThere%20was%20a%20very%20similar%20question%20regarding%20EPS%20a%20while%20ago%20(maybe%20in%20the%20last%20week)%20so%20you%20can%20find%20a%20good%20query%20there.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20far%20as%20mapping%20the%20users%2C%20you%20would%20need%20to%20have%20the%20MS%20Sentinel%20queries%20auditing%20feature%20enabled.%20Go%20to%20this%20page%20for%20more%20information%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fsentinel%2Faudit-sentinel-data%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EAudit%20Microsoft%20Sentinel%20queries%20and%20activities%20%7C%20Microsoft%20Docs%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3097672%22%20slang%3D%22en-US%22%3ERe%3A%20User%20Events%20Per%20Second%20KQL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3097672%22%20slang%3D%22en-US%22%3EHi%20Gary%2C%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%20for%20your%20response%2C%3CBR%20%2F%3EI've%20tried%20the%20below%2C%20which%20has%20brought%20me%20the%20list%20of%20users%2C%20however%20I%20can't%20quite%20figure%20out%20how%20to%20pull%20their%20events%20with%20them.%20Is%20there%20a%20way%20I%20can%20make%20another%20column%20in%20the%20results%2C%20with%20their%20events%20per%20second%2C%20tied%20to%20that%20user%20account%3F%3CBR%20%2F%3E%3CBR%20%2F%3ESecurityEvent%3CBR%20%2F%3E%7C%20where%20TimeGenerated%20%26gt%3B%20ago(1h)%3CBR%20%2F%3E%7C%20where%20TargetAccount%20has%20%22%3CCUSTOMERNAME%3E%22%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3EMany%20thanks%2C%3CBR%20%2F%3EC%3C%2FCUSTOMERNAME%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hey!

 

I hope you're all doing well!

I've been working with a monthly report with Sentinel and PowerBI - and I've been trying to get a "User EPS Count". This would be a KQL statement that looks at the last 30 days of events, and searches based on a user? For example User1 has an average of 6000 events linked to their account in a month, and User 2 has 3000 events etc. 

 

Is this possible in KQL or do I have to do make a few searches and correlate based around that?

TIA

3 Replies

@CharlieK95 There was a very similar question regarding EPS a while ago (maybe in the last week) so you can find a good query there.

 

As far as mapping the users, you would need to have the MS Sentinel queries auditing feature enabled. Go to this page for more information: Audit Microsoft Sentinel queries and activities | Microsoft Docs

Hi Gary,

Thanks for your response,
I've tried the below, which has brought me the list of users, however I can't quite figure out how to pull their events with them. Is there a way I can make another column in the results, with their events per second, tied to that user account?

SecurityEvent
| where TimeGenerated > ago(1h)
| where TargetAccount has "<customername>"


Many thanks,
C
best response confirmed by CharlieK95 (Occasional Contributor)
Solution

Something like this?
SecurityEvent
| where TimeGenerated > ago( 1h)
| summarize EventCount=count() by TargetAccount
| extend EPSCount = EventCount/60/60

Not sure too many single user accounts would be generating so many events to actually get very high EPS, so that last line may be redundant, if you take it out you will just get a count per TargetAccount

Also with SecurityEvent logs in particular, depending on the EventId the TargetAccount field can be blank, maybe just double check you are getting everything you need