SOLVED

Log Analytics query filter select multiple accounts

%3CLINGO-SUB%20id%3D%22lingo-sub-1124854%22%20slang%3D%22en-US%22%3ELog%20Analytics%20query%20filter%20select%20multiple%20accounts%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1124854%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20setup%20a%20query%20and%20create%20an%20alert%20for%20(failed)%20signin%20attempts%20of%20multiple%20service%20accounts.%20I%20collect%20the%20signin%20attempts%20in%20Log%20Analytics%20and%20use%20this%20query%20to%20filter%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESigninLogs%20%7C%20where%20OperationName%20%3D%3D%20%22Sign-in%20activity%22%20%7C%20where%20UserPrincipalName%20%3D%3D%20%22auobrien.david%40outlook.com%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20problem%20is%2C%20I%20want%20to%20use%20this%20for%20multiple%20service%20accounts%20and%20I%20can't%20use%20a%20wildcard%20like%20auobrien.*%40outlook.com%20for%20example.%20Any%20idea's%20on%20how%20to%20specify%20multiple%20accounts%20or%20do%20I%20have%20to%20create%20a%20query%20for%20each%20account%20I%20want%20to%20monitor%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1124854%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Log%20Analytics%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EQuery%20Language%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1126362%22%20slang%3D%22en-US%22%3ERe%3A%20Log%20Analytics%20query%20filter%20select%20multiple%20accounts%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1126362%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F529949%22%20target%3D%22_blank%22%3E%40marwedit%3C%2FA%3E%2C%20would%20something%20like%20this%20be%20OK%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3ESigninLogs%20%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E%7C%20where%20OperationName%20%3D%3D%20%22Sign-in%20activity%22%20and%20UserPrincipalName%20in~%20('auobrien.david%40outlook.com'%2C'john.doe%40outlook.com'%2C'mary.jones%40outlook.com')%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1127484%22%20slang%3D%22en-US%22%3ERe%3A%20Log%20Analytics%20query%20filter%20select%20multiple%20accounts%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1127484%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F453722%22%20target%3D%22_blank%22%3E%40hspinto%3C%2FA%3E%26nbsp%3BThanks%20for%20the%20reply!%20I%20tried%20it%20and%20it%20works%20great.%20Thanks!%20This%20tackles%20the%20multiple%20query%20problem%20since%20I%20can%20put%20multiple%20users%20in%20one.%20One%20more%20question.%20Do%20you%20know%20of%20a%20way%20I%20could%20enter%20a%20wildcard%20in%20the%20filter%20so%20new%20service%20accounts%20(svc_*)%20are%20automatically%20added%3F%20When%20I%20replace%20part%20of%20the%20username%20with%20*%20it%20just%20ignores%20it.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1127883%22%20slang%3D%22en-US%22%3ERe%3A%20Log%20Analytics%20query%20filter%20select%20multiple%20accounts%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1127883%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F529949%22%20target%3D%22_blank%22%3E%40marwedit%3C%2FA%3E%2C%20you%20just%20have%20to%20add%20a%20different%20condition%20to%20the%20query%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3ESigninLogs%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E%7C%20where%20OperationName%20%3D%3D%20%22Sign-in%20activity%22%20and%20(UserPrincipalName%20in~%20('auobrien.david%40outlook.com'%2C'john.doe%40outlook.com'%2C'mary.jones%40outlook.com')%20or%20UserPrincipalName%20startswith%20%22svc_%22)%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3ESee%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fkusto%2Fquery%2Fdatatypes-string-operators%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3Ehere%3C%2FA%3E%20a%20full%20list%20of%20the%20string%20operators%20you%20can%20use.%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EHope%20that%20helps!%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1128097%22%20slang%3D%22en-US%22%3ERe%3A%20Log%20Analytics%20query%20filter%20select%20multiple%20accounts%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1128097%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F453722%22%20target%3D%22_blank%22%3E%40hspinto%3C%2FA%3E%26nbsp%3BThanks%20for%20the%20help%20and%20the%20link.%20This%20is%20exactly%20what%20I%20was%20looking%20for.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi,

 

I want to setup a query and create an alert for (failed) signin attempts of multiple service accounts. I collect the signin attempts in Log Analytics and use this query to filter:

 

SigninLogs | where OperationName == "Sign-in activity" | where UserPrincipalName == "auobrien.david@outlook.com"

 

The problem is, I want to use this for multiple service accounts and I can't use a wildcard like auobrien.*@outlook.com for example. Any idea's on how to specify multiple accounts or do I have to create a query for each account I want to monitor?

4 Replies

@marwedit, would something like this be OK?

 

SigninLogs

| where OperationName == "Sign-in activity" and UserPrincipalName in~ ('auobrien.david@outlook.com','john.doe@outlook.com','mary.jones@outlook.com')

@hspinto Thanks for the reply! I tried it and it works great. Thanks! This tackles the multiple query problem since I can put multiple users in one. One more question. Do you know of a way I could enter a wildcard in the filter so new service accounts (svc_*) are automatically added? When I replace part of the username with * it just ignores it.

Best Response confirmed by marwedit (New Contributor)
Solution

@marwedit, you just have to add a different condition to the query:

 

SigninLogs

| where OperationName == "Sign-in activity" and (UserPrincipalName in~ ('auobrien.david@outlook.com','john.doe@outlook.com','mary.jones@outlook.com') or UserPrincipalName startswith "svc_")

 

See here a full list of the string operators you can use.

 

Hope that helps!

@hspinto Thanks for the help and the link. This is exactly what I was looking for.