IOC's cross custom tables

[OmriPinsker]

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));

CustomTable_CL

| where Source_IP_s in (cortex_match)

tnx

[#2]

&nbsp;
&nbsp;
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]
&nbsp;
&nbsp;
If you need it, change the last line to:
&nbsp;

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

[#3]

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

[#4]

Hi @CliveWatson 

when trying to add to the query additional external data such as https://openphish.com/feed.txt

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) ([@"https://openphish.com/feed.txt"] with (format="txt"));

[#5]

I cant make that file open with externaldata - do you also get a Query Aborted error?

[#6]

@CliveWatson

yes, any alternatives?

[#7]

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