SOLVED
Home

How to query in unknown columns?

%3CLINGO-SUB%20id%3D%22lingo-sub-855875%22%20slang%3D%22en-US%22%3EHow%20to%20query%20in%20unknown%20columns%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-855875%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20using%20this%20query%20to%20find%20the%20presence%20of%20certain%20IPs%20in%20a%20specific%20table%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3EThreatIntelligenceIndicator%0A%7C%20where%20Description%20has%20%22IPv4%20indicator%20from%22%0A%7C%20project%20Description%2C%20BadIP%3Dextract(%22(%5B0-9%5D%7B1%2C3%7D).(%5B0-9%5D%7B1%2C3%7D).(%5B0-9%5D%7B1%2C3%7D).(%5B0-9%5D%7B1%2C3%7D)%24%22%2C%200%2C%20ExternalIndicatorId)%0A%7C%20join%20kind%3Dinner(%0ASigninLogs%0A%7C%20project%20OperationName%2C%20BadIP%3DIPAddress%20)%20on%20BadIP%20%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%2C%20i%20would%20like%20to%20search%20in%20all%20the%20tables%20for%20these%20IPs%2C%20but%20am%20not%20sure%20how%20to%20proceed%20as%20i%20don't%20know%20the%20IP%20column%20names%20in%20advance.%3C%2FP%3E%3CP%3EI%20have%20replaced%20the%20SigninLogs%20with%26nbsp%3Bsearch%20*%20but%20different%20tables%20have%20different%20IP%20column%20names.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-855875%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Log%20Analytics%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-856509%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20query%20in%20unknown%20columns%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-856509%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F409040%22%20target%3D%22_blank%22%3E%40CloudMe%3C%2FA%3E%20From%20what%20I%20am%20seeing%20you%20are%20doing%20extraction%20but%20you%20mention%20you%20want%20to%20do%20find.%3C%2FP%3E%0A%3CP%3EIf%20you%20want%20to%20search%20on%20all%20columns%20on%20all%20tables%20you%20can%20use%20search%20operator.%20Something%20like%20this%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3Esearch%20%20*%20matches%20regex%20%40%22.dock.%22%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20is%20important%20to%20note%20that%20search%20is%20very%20heavy%20operations.%20Usually%20it%20should%20be%20used%20only%20in%20situations%20where%20you%20initially%20want%20to%20find%20what%20data%20%2C%20where%20is%20located.%20After%20you%20have%20that%20information%20it%20is%20better%20to%20start%20getting%20the%20data%20directly%20from%20the%20tables.%20Besides%20regex%20you%20can%20use%20other%20string%20operators%20as%20well.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-857289%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20query%20in%20unknown%20columns%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-857289%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9172%22%20target%3D%22_blank%22%3E%40Stanislav%20Zhelyazkov%3C%2FA%3E%26nbsp%3BThank%20you%20for%20the%20reply%2C%20but%20maybe%20i%20failed%20to%20understand%20or%20explain%20myself%20properly.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20like%20to%20to%20keep%20my%20original%20query%20with%20the%20join%20operator%2C%20but%20am%20not%20sure%20how%20to%20create%20it%20as%20i%20don't%20know%20the%20name%20of%20the%20column%20for%20the%20BadIP.%3C%2FP%3E%3CP%3EMy%20goal%20is%20to%20check%20the%20presence%20of%20each%20BadIP%20from%20the%26nbsp%3BThreatIntelligenceIndicator%20table%20in%20all%20the%20other%20tables.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3EThreatIntelligenceIndicator%0A%7C%20where%20Description%20has%20%22IPv4%20indicator%20from%22%0A%7C%20project%20Description%2C%20BadIP%3Dextract(%22(%5B0-9%5D%7B1%2C3%7D).(%5B0-9%5D%7B1%2C3%7D).(%5B0-9%5D%7B1%2C3%7D).(%5B0-9%5D%7B1%2C3%7D)%24%22%2C%200%2C%20ExternalIndicatorId)%0A%7C%20join%20kind%3Dinner(%0A%2F%2FI'm%20not%20sure%20how%20to%20write%20the%20query%20below.%0Asearch%20*%0A%7C%20where%20Table%20!%3D%20%22ThreatIntelligenceIndicator%22%0A%7C%20project%20BadIP%3D%3F%3F%3F%3F%3F%20)%20on%20BadIP%20%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-857298%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20query%20in%20unknown%20columns%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-857298%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F409040%22%20target%3D%22_blank%22%3E%40CloudMe%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESomeone%20else%20can%20weigh%20if%20it%20knows%20way%20but%20I%20do%20not%20know%20how%20to%20extract%20data%20from%20multiple%20columns.%20the%20example%20query%20with%20search%20*%20will%20return%20records%20from%20different%20tables%20without%20doing%20any%20re-structure%20on%20them%20there%20is%20no%20way%20you%20can%20join%20the%20results%20from%20the%20search%20*%20query%20to%20yours%20as%20the%20IP%20information%20will%20be%20in%20different%20columns%20and%20potentially%20can%20find%20information%20in%20text%20that%20for%20example%20contains%20IP.%3C%2FP%3E%0A%3CP%3EWhat%20is%20the%20reason%20for%20not%20knowing%20the%20tables%20and%20columns%20where%20you%20search%20for%20information%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-857524%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20query%20in%20unknown%20columns%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-857524%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9172%22%20target%3D%22_blank%22%3E%40Stanislav%20Zhelyazkov%3C%2FA%3E%26nbsp%3BThanks%20for%20the%20input.%26nbsp%3B%3C%2FP%3E%3CP%3EI%20thought%20it%20will%20be%20useful%20to%20have%20a%20way%20to%20scan%20a%20Workspace%20for%20malicious%20IPs%20without%20worrying%20about%20the%20possibility%20of%20missing%20a%20Table.%3C%2FP%3E%3CP%3EIn%20larger%20organizations%20sources%20may%20be%20added%20to%20a%20Workspace%20without%20our%20prior%20knowledge%20of%20the%20table%20and%20its%20content%2C%20and%20thus%20the%20risk%20of%20missing%20a%20malicious%20event%20increases.%3C%2FP%3E%3CP%3EIt%20can%20also%20help%20hunting%20for%20threats%20in%20a%20new%20and%20unfamiliar%20environment.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-858127%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20query%20in%20unknown%20columns%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-858127%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F409040%22%20target%3D%22_blank%22%3E%40CloudMe%3C%2FA%3E%20Ok.%20My%20take%20on%20this%20is%20to%20address%20this%20to%20Azure%20Sentinel%20team.%20There%20is%20already%20Malicious%20IP%20feature%20that%20is%20able%20to%20flag%20malicious%20IPs.%20It%20should%20work%20for%20Firewall%20logs%2C%20Wire%20Data%20and%20IIS%20logs.%20May%20be%20a%20few%20more.%20But%20it%20will%20be%20good%20if%20they%20make%20it%20work%20for%20all%20their%20logs%20they%20have%20connectors%20for.%20Trying%20to%20do%20this%20on%20your%20own%20will%20never%20be%20optimal%20or%20never%20good%20enough%20as%20Microsoft%20has%20internal%20service%20that%20is%20able%20to%20recognize%20these%20IPs.%20That%20service%20they%20use%20for%20Malicious%20IP.%20Additionally%20besides%20the%20IP%20being%20malicious%20they%20also%20feed%20you%20with%20other%20information%20like%20is%20it%20botnet%2C%20the%20country%2C%20etc.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
CloudMe
Occasional Contributor

Hi,

 

I'm using this query to find the presence of certain IPs in a specific table:

 

 

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(
SigninLogs
| project OperationName, BadIP=IPAddress ) on BadIP 

 

 

Now, i would like to search in all the tables for these IPs, but am not sure how to proceed as i don't know the IP column names in advance.

I have replaced the SigninLogs with search * but different tables have different IP column names.

 

Thank you.

 

 

5 Replies

Hi@CloudMe 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 

 

 

@CloudMe 

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.

 

 

 

Solution

@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.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies