Forum Discussion
How to query in unknown columns?
CloudMe Ok. My take on this is to address this to Azure Sentinel team. There is already Malicious IP feature that is able to flag malicious IPs. It should work for Firewall logs, Wire Data and IIS logs. May be a few more. But it will be good if they make it work for all their logs they have connectors for. Trying to do this on your own will never be optimal or never good enough as Microsoft has internal service that is able to recognize these IPs. That service they use for Malicious IP. Additionally besides the IP being malicious they also feed you with other information like is it botnet, the country, etc.
HiCloudMe From what I am seeing you are doing extraction but you mention you want to do find.
If you want to search on all columns on all tables you can use search operator. Something like this:
search * matches regex @".dock."
It is important to note that search is very heavy operations. Usually it should be used only in situations where you initially want to find what data , where is located. After you have that information it is better to start getting the data directly from the tables. Besides regex you can use other string operators as well.
Stanislav_Zhelyazkov Thank you for the reply, but maybe i failed to understand or explain myself properly.
I would like to to keep my original query with the join operator, but am not sure how to create it as i don't know the name of the column for the BadIP.
My goal is to check the presence of each BadIP from the ThreatIntelligenceIndicator table in all the other tables.
ThreatIntelligenceIndicator
| where Description has "IPv4 indicator from"
| project Description, BadIP=extract("([0-9]{1,3}).([0-9]{1,3}).([0-9]{1,3}).([0-9]{1,3})$", 0, ExternalIndicatorId)
| join kind=inner(
//I'm not sure how to write the query below.
search *
| where Table != "ThreatIntelligenceIndicator"
| project BadIP=????? ) on BadIP
Someone else can weigh if it knows way but I do not know how to extract data from multiple columns. the example query with search * will return records from different tables without doing any re-structure on them there is no way you can join the results from the search * query to yours as the IP information will be in different columns and potentially can find information in text that for example contains IP.
What is the reason for not knowing the tables and columns where you search for information?
Stanislav_Zhelyazkov Thanks for the input.
I thought it will be useful to have a way to scan a Workspace for malicious IPs without worrying about the possibility of missing a Table.
In larger organizations sources may be added to a Workspace without our prior knowledge of the table and its content, and thus the risk of missing a malicious event increases.
It can also help hunting for threats in a new and unfamiliar environment.
CloudMe Ok. My take on this is to address this to Azure Sentinel team. There is already Malicious IP feature that is able to flag malicious IPs. It should work for Firewall logs, Wire Data and IIS logs. May be a few more. But it will be good if they make it work for all their logs they have connectors for. Trying to do this on your own will never be optimal or never good enough as Microsoft has internal service that is able to recognize these IPs. That service they use for Malicious IP. Additionally besides the IP being malicious they also feed you with other information like is it botnet, the country, etc.