Azure Log Analytics: Queries, Are you looking for a pattern? RegEx
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.
| 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…
For the eagle eyed, Melchett is from the UK TV series Black Adder, so I wonder what he is doing in our security logs?
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
The final syntax looks like this:
| 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.