Microsoft Entra Suite Tech Accelerator
Aug 14 2024, 07:00 AM - 09:30 AM (PDT)
Microsoft Tech Community

IOC's cross custom tables

Copper Contributor

Hi @CliveWatson 

when using the above query i am trying to search across multiple tables at once and see if there is a match for any relevant IOC

1. i am managing several lists: IP. Hash. URL etc and have created various external data containers

when running it via join there is a limitation of ~3-4 tables to run on. i need to tun across 8+ tables

2. when trying to use an in statement as described below im unable to output the Category,Indicator, Campaign, :

let TI = toscalar(externaldata(Category:string,Indicator:string,Campaign:string) [ @"https://xxxxxxxxxips.csv"] | summarize make_list(Indicator));
let IoCs = TI | project Indicator;
let security1 = PaloAlto_CL | where Source_IP_s in (IoCs);
let security2= OfficeActivity | where ClientIP in (IoCs);
let security3= securityAkamai_CL| where src_s in (IoCs);
let AWSCloudTrailp = AWSCloudTrail | where SourceIpAddress in (IoCs);
let SecurityEventp = SecurityEvent | where IpAddress in (IoCs);
let security4= Custom_CL | where client_ip_s in (IoCs);
union withsource= TableName security1, security2, security3,AWSCloudTrailp


3. also tried the following

let TI = toscalar(externaldata(Category:string,Indicator:string,Campaign:string) [ @"https://xxxxxxxxx.csv"] | summarize IndicatorList=make_list(Indicator));
let cortex_match = toscalar(<CustomTable>_CL| summarize by Source_IP_s | extend Indicator=TI | mv-apply IndicatorList=TI on (where Source_IP_s == IndicatorList) | project Source_IP_s |summarize makelist(Source_IP_s));
| where Source_IP_s in (cortex_match)
6 Replies
How about?&nbsp; I added a few lines to test this, please remove.&nbsp; If you do a lot of union's, then standardizing (normalization) can help, I added this, but please ignore if this isn't necessary.&nbsp;&nbsp;&nbsp;
&nbsp;let TI = toscalar(externaldata(Category:string,Indicator:string,Campaign:string) [ @"https://xxxxxxxxxips.csv"] | summarize make_list(Indicator));
let IoCs = TI | project Indicator;
// test line
let fakeLet = dynamic(["1"]);
union isfuzzy=true
( PaloAlto_CL | where Source_IP_s in (IoCs) | extend normalizedIP = Source_IP_s ),
( OfficeActivity | where ClientIP in (IoCs) | extend normalizedIP = ClientIP),
( securityAkamai_CL | where src_s in (IoCs) | extend normalizedIP = src_s),
( AWSCloudTrail | where SourceIpAddress in (IoCs) | extend normalizedIP = SourceIpAddress),
( SecurityEvent | where IpAddress in (IoCs) | extend normalizedIP = IpAddress),
( Fastly_CL | where client_ip_s in (IoCs) | extend normalizedIP =client_ip_s),
// test table
( SecurityEvent | where EventID &gt; 7000 | extend normalizedIP = EventID)
// now show data you need
| project normalizedIP, fakeLet.[0]
If you need it, change the last line to:

| project normalizedIP, fakeLet.[0], Type
This would show the TableName (as Type == TableName)

@CliveWatson , @OmriPinsker :


If I understand Omri's question correctly, it is about getting the category and campaign associated once a match is found. The answer is to join the result set of the union on the IP address. A few implementation guidelines:

  • You need to keep the TI table and not make a list out of it. The "let IoCs" line should not work as-is and assume TI is still a table. 
  • As Clive mentions, assigning the relevant IP to a common field such as NormalizedIP is needed for that.


A few additional differences worth mentioning between your two versions:

  • Clive is using "isfuzzy=true," which is recommended (thanks Clive) as otherwise, if any of the parts of the union has an error, the all thing fails.
  • @OmriPinsker : If your external list includes values that are not IP addresses, please filter them out when creating IoCs.
  • The difference between using let statements and or not using it should not make a difference. @CliveWatson : I wonder if you see an advantage either way apart from style?

Hi @CliveWatson 

when trying to add to the query additional external data such as

as there is no deceleration of the column names in the feed i am unable to determine what is the proper syntax of adding this.


let openphish = externaldata ( domain:string) ([@""] with (format="txt"));
I cant make that file open with externaldata - do you also get a Query Aborted error?



yes, any alternatives?


@OmriPinsker : My guess is that openpish has some protection mechanism in place that blocks externaldata. As a long shot, you may want to check with them, however using Logic Apps to copy it daily to Azure storage would be easier.