Forum Discussion
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 an...
CliveWatson
Microsoft
Microsoft
How about? I added a few lines to test this, please remove. If you do a lot of union's, then standardizing (normalization) can help, I added this, but please ignore if this isn't necessary.
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 > 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)
Thanks
How about? I added a few lines to test this, please remove. If you do a lot of union's, then standardizing (normalization) can help, I added this, but please ignore if this isn't necessary.
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 > 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)
Thanks
Ofer_ShezafMicrosoft
Microsoft
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 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"));- CliveWatsonI cant make that file open with externaldata - do you also get a Query Aborted error?
