Forum Discussion

OmriPinsker's avatar
OmriPinsker
Copper Contributor
May 17, 2020

IOC's cross custom tables

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
  • CliveWatson's avatar
    CliveWatson
    Icon for Microsoft rankMicrosoft
    May 17, 2020
    &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
    • Ofer_Shezaf's avatar
      Ofer_Shezaf
      Icon for Microsoft rankMicrosoft
      May 17, 2020

      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?

Resources