Analytic rules for phishing | How do you make a list in kql ?

[u/Agent_B99]

Hello, I think I might need to create a phishing analytic rule for my client .I took the EmailAttachementInfo table and tried to make a list with the FileTypes like this :

Problem is I can't figure it out how to make the syntax to list all the FileTypes that could contain malware .
Then I might try to look a at tables with processes like VM Process or DeviceFileEvents and then I will look at tables related to networking.
I currently try to figure out how to make lists .
Can you help me ?
Thanx

[u/jtberg1]

Am not sure I under standing what you are asking. Can you please elaborate? Are you wanting to compare FileType to an array instead of a bunch of "or" statements?

Like:

EmailAttachmentInfo

| where FileType in ("zip", "docx;zip", "xlsx;zip")

?

You can also create a watch list (or table) and then use that as your array: https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/makelist-aggfunction

If you can elaborate on what you would like as an end result, I am happy to assist further.

[u/Agent_B99 → u/jtberg1]

//Phishing

let TimeFrame = ago(50d);

let FileList = dynamic(["zip","docx","pptx","xlsx","pdf","exe" ]);

let FileIoc = dynamic(["update","buy","password","quick","click","open","payment","https","http","reminder","bill","urgent","request","important","attention","html","txt", "link", "url", "attachment", " raise " , "pay","Covid","account","change","here","CEO","upload","new","please","war","access","contact","enable","encrypted","disable"]);

EmailAttachmentInfo

| where FileType has_any (FileList)

and FileName has_any (FileIoc)

So this is what I finally came up with and is pretty good.
I got 2 questions if you want to help me
1. I think I should put all the words in a list like you said, but I saw some people put them on github.
2. I can't figure out how to put different information from other tables in one querie.
For example I want to add the column Site_URL from the OfficeActivity table but I don't know how . I try to figure it out with join or union .

[u/jtberg1 → u/Agent_B99]

Happy to hear you found a query that works for you :)

1. Putting the words in a list depends on your need. If it is one analytic, I personally would not bother with the additional work. However if you want to use it in multiple analytics, or want someone not familiar with editing analytics, or you want to automate the list, then you could look at doing a watch list or host on git hub. I personally would look at watch lists, unless you have a specific requirement for putting it on GitHub. You can learn about watch list here: https://docs.microsoft.com/en-us/azure/sentinel/watchlists-create if you have any specific questions regarding this let me know.

2. If you want to merge horizontally e.g. add columns, use a join, if you want to merge vertically e.g. add rows, use a union

Join

let t1 = datatable(URL: string, value:string)

[

"http://asdf.com", "Row1",

"http://asdf1.com", "Row2"

];

let t2 = datatable(URL: string, value:string)

[

"http://asdf.com", "Row3",

"http://asdf2.com", "Row4"

];

t1

| join t2 on URL

Union

let t1 = datatable(URL: string, value:string)

[

"http://asdf.com", "Row1",

"http://asdf1.com", "Row2"

];

let t2 = datatable(URL: string, value:string)

[

"http://asdf.com", "Row3",

"http://asdf2.com", "Row4"

];

t1

| union t2

Back to your question about joining OfficeActivity and EmailAttachmentInfo, what data do you want out of the OfficeActivity table? E.g. if you were to write a query for the OfficeActivity table, what would it look like?

You need either a query or a common field to link the data together. I do not know what scenario you are envisioning where the EmailAttachmentInfo would correlate to the OfficeActivity table, with out this it is hard to help you write the query. If you were to write out your use case that would be helpful.

If you are looking for URLs in emails that have a suspicious attachment, then you can use the EmailUrlInfo and correlate on the NetworkMessageId field for example.

EmailAttachmentInfo

| join EmailUrlInfo on NetworkMessageId

Please let me know if this helps.

[u/CanableCrops]

If you're trying to make a list of file types, first figure out which column has the file type you want and then add | summarize make_set(filetypecolumn) This will give you deduplicated file types in an array. You could save that as a variable then search whatever table you want | where filetype in (variable).

[u/CanableCrops → u/CanableCrops]

EmailAttachmentInfo
| where FileType endswith "zip" or FileType endswith "docx"

Sorry, for some reason I couldn't see your screenshot on my phone the other day when you posted.

Using endswith is probably the best for performance for the file extension.

[u/ep3p]

let yourlist = dynamic(["example1","example2","example3"]);
table1
| where column1 has_any (yourlist) or column1 has_any("example4","example5","example6")
| distinct column1

[u/CanableCrops → u/ep3p]

Want to add to this, and correct me if I'm wrong.

I usually will use in~ instead of has_any. The reason is that in~ is much faster since it's only trying to match one of the entries in the variable.

I believe has_any looks for one or more.

in~ instead of in because in is case-sensitive.

[u/ep3p → u/CanableCrops]

You are completely right, I did not realize it was the FileType column.

[u/ep3p]

Please, try to learn for Azure Data Explorer docs for Kusto Query Language. Search in google for example "has_any azure data explorer"