Microsoft Secure Tech Accelerator
Apr 03 2024, 07:00 AM - 11:00 AM (PDT)
Microsoft Tech Community

Use "where contains" from a list

Copper Contributor



I have been trying to setup Linux audit logs in Azure Sentinel, using the OMS auditd parser found in the OMS agent. (Not AUOMS, which I can't use as I have isolated servers).


Anyone who've played with auditd knows that the logs generated can be incredibly hard to do anything with, so I have set up keywords based on what I want to monitor for, however even then, I will still get some sort of "system noise" and filtering auditd is even harder, especially when some of the things logged can be relevant to threat hunting later on.


So, I took a bit of a different approach, made sure we had the relevant rules setup in auditd and then sent that up to Sentinel, where I then decided to filter out slowly.


However, it slowly became a long query filled with "excludes", i.e. where Column contains "XXXYYYZZZ".


And I have to use contains, else I would have to set a column for each field in auditd and thus make it even harder to read and parse, with a huge amount of columns that serve no purpose. 


I have tried to put the events in "ConsoleCommand", "Execute" and "Process", as well as the normal ones such as CWD, Path, User etc.


So essentially I have to exclude from 6-7 different fields and none of these field will ever only have the value that I want to exclude, so I have to use contains.


Now, I wanted to do this in a smart way and instead of having to constantly update a function in Azure Sentinel, I am making use of externaldata, looking at a separate exclude list for each field, such as LinuxConsoleCommandExclude.txt, which have been uploaded to an Azure Storage Blob.


There are in turn created from a csv file, with a self-made ExcludeID, Data (the string to exclude) and a comment field, so there is some sort of documentation as to why we exclude this. I have done this as I have found no way to make use of externaldata to only look at the second column. But I digress, I just wanted to explain the setup.


So, LinuxConsoleCommandExeclude.txt contains something like:



chown omsagent.omiusers
/opt/microsoft/auoms/bin/auomsctl -s



Now, I would like to have a "foreach" kind of thing, with the contains string operator, but as far as I have found out, the only string operator that actually has this feature is in.


So how do I do this right or am I really just setting myself up for failure here?


I wish I could set this up in a datatable, just to provide an example but the amount of noise auditd but here is an example of an output I have created, with my homebrew parser:


["PROCTITLE: 2F6F70742F6D6963726F736F66742F61756F6D732F62696E2F61756F6D7363746C002D73","PATH: ","CWD: ","EXECVE: ","SYSCALL: /opt/microsoft/auoms/bin/auomsctl "]
["EXECVE: /opt/microsoft/auoms/bin/auomsctl -s ","SYSCALL: 564fb89d63a0 564fb89d75c0 564fb89d6d80 5b9"]


Just to add: I am not looking for ways to filter out the example, this is an extreme example.


I am mainly asking for a way to have where contains to actually do a foreach based on a list as the alternative would be messy and hard to administer. 

4 Replies
I didn't understand what you want to achieve. did you try has_any operator?
| where field1 has_any(<dynamic list>)



The lists have things I want to exclude from the query in them. The fields I want to exclude this from can and often contains other data so I can never make a 100% match, which is why I need to make use of contains.


has_any would work wonders for an inclusive list but not exclusive and !has_any sadly doesn't exist.



best response confirmed by rurno (Copper Contributor)
you can use "not (fieldname has_any(dynamiclist))"


It works! You're fantastic.

Glad to know that not would also work in this instance, I never even thought of it as a possibility.

1 best response

Accepted Solutions
best response confirmed by rurno (Copper Contributor)
you can use "not (fieldname has_any(dynamiclist))"

View solution in original post