SOLVED

Use "where contains" from a list

New Contributor

Hello,

 

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:

 

 

logrotate
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:

 

 
SerialNumber
8063430
 
Computer
anonymous-system
 
PPID
["3768"]
 
PID
["3789"]
 
AUID
["471"]
 
RecordTypes
["SYSCALL","EXECVE","CWD","PATH","PROCTITLE"]
 
Keyword
["T1166_Seuid_and_Setgid"]
 
Executable
["PROCTITLE: 2F6F70742F6D6963726F736F66742F61756F6D732F62696E2F61756F6D7363746C002D73","PATH: ","CWD: ","EXECVE: ","SYSCALL: /opt/microsoft/auoms/bin/auomsctl "]
 
Process
[]
 
ConsoleCommand
["EXECVE: /opt/microsoft/auoms/bin/auomsctl -s ","SYSCALL: 564fb89d63a0 564fb89d75c0 564fb89d6d80 5b9"]
 
TTY
["(none)"]
 
Succesful
["yes"]
 
User
["root"]
 
AuditUser
["omsagent"]
 
CurrentWorkingDirectory
["/opt/microsoft/omsconfig/Scripts/2.6x-2.7x"]
 
Path
["/lib64/ld-linux-x86-64.so.2","/opt/microsoft/auoms/bin/auomsctl"]

 

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?
usage:
table
| where field1 has_any(<dynamic list>)

@mergene 

 

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 (New Contributor)
Solution
you can use "not (fieldname has_any(dynamiclist))"

@mergene 

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.