KQL Query to filter values based on condition

Shabarinath 0
Viewed 43

Need help on filtering the user domain from userprincipalname collected from sentinel signin logs. I have all user domains separated out from upn as below.

extend UserDomains = split(UserPrincipalName,'@')[1]

In addition to the UserDomains, I need internal domains and external domains. How can I filter UserDomains based on a particular conditions further.

Cheers !

azure-sentinel kql azure
1 Answers

You can use operators like iif:

T 
| extend IsInternalDomain = iif(UserDomains contains "mycompany.com", true, false)
| where IsInternalDomain

or

T 
| extend DomainType = iif(UserDomains contains "mycompany.com", "Internal", "External")
| where DomainType == "Internal"

Or the case operator:

T 
| extend DomainType = case(UserDomains contains "us.mycompany.com", "Internal US", 
                           UserDomains contains "mycompany.com", "Internal",
                           "External")
| where DomainType == "Internal US"

Or you can simply use a where like this:

T 
| where UserDomains !endswith "mycompany.com" // include only external domains
Peter Bons0
Related