Jaa

Azure Log Analytics: Queries, Are you looking for a pattern? RegEx

  • Artikkeli

RegEx is a common way of finding data .  It was helpful recently when Jon needed a small bit of help (he says more blogs will follow - the first post is quite cryptic).

Basically what was needed was to only show the UserNames that started with A thru E and maybe a few other random ones.  He'd already tried a Dynamic array and that didn't quite do it.  As Jon found, you can do this with a startswith command, but you could end up with lots of OR statements, I've shown just two entries here, but you get the point, line 3 could get rather long and hard to manage/read.

SecurityEvent

| where TargetAccount !="" or TargetUserName !="-"

| where TargetUserName startswith "a" or TargetUserName startswith "b"

| project TargetUserName

 

So I eventually came up with using RegEx and SubString to achieve this.  I also converted the data to Upper Case just to make sure, nothing was missed (that's optional).

The first task was to remove some rogue entries, such as blanks.  with !=”“

Regex examples are listed in the Help

I combined the pattern match with the extract option.  In my example I wanted A-E and I also used J and T - which is written [A-EJT], you could also do [a-ejtA-EJT] to get the right case.  I then pulled the first letter into a variable called first_letter and it was the substring operator that enabled me to get just the first character (from position 0 to 1) from the TargetUserName field.

So with this syntax, you can see what letter matches were made in the first_letter column.  An “A” for Administrator was a match, M wasn't, L wasn’t etc…

SecurityEvent
| where TargetAccount !=""
| project toupper(TargetUserName) , first_letter=extract("([A-EJT])", 1, substring(toupper(TargetUserName),0,1) ) image

For the eagle eyed, Melchett is from the UK TV series Black Adder, so I wonder what he is doing in our security logs?   Smile

 

Adding two extra lines at the end, was a good way to make sure only the needed letter were returned, A-E and J & T

image

The final syntax looks like this:

SecurityEvent
| where TargetAccount !=""
| project toupper(TargetUserName) , first_letter=extract("([A-EJT])", 1, substring(toupper(TargetUserName),0,1) )
| where first_letter !=""

I know Jon will adjust this for his use, but essentially this is how to find a range of specific characters at the first position of a field in Log Analytics.

image