Forum Discussion
Performing a join using "contains"
I have two tables and Table "A" has a list of error messages while Table "B" has a list of keywords. Is there any way to return a list of those error messages from Table A that a contain a keyword ...
I think the query made for Zscaler might do what you are looking for. Just replace the ThreatIntelligenceIndicator and Common SecurityLog tables with the fields you need, then project the matches. The query is projecting fields from each table. Let me know if you need a hand!
let dt_lookBack = 7d;
let ioc_lookBack = 14d;
ThreatIntelligenceIndicator
| where TimeGenerated > ago(ioc_lookBack)
| where DomainName contains "." and DomainName !contains ".storage.googleapis.com" and DomainName !contains ".office.com"
| join (
CommonSecurityLog
| where TimeGenerated > ago(dt_lookBack)
| where DestinationHostName contains (".")
| extend CommonSecurityLog_TimeGenerated = TimeGenerated
)
on $left.DomainName == $right.DestinationHostName
| project SourceUserName, DomainName, CommonSecurityLog_TimeGenerated, Description, Activity
| extend AccountCustomEntity = SourceUserName
| extend HostCustomEntity = DomainName
| extend URLCustomEntity = DomainName
let dt_lookBack = 7d;
let ioc_lookBack = 14d;
ThreatIntelligenceIndicator
| where TimeGenerated > ago(ioc_lookBack)
| where DomainName contains "." and DomainName !contains ".storage.googleapis.com" and DomainName !contains ".office.com"
| join (
CommonSecurityLog
| where TimeGenerated > ago(dt_lookBack)
| where DestinationHostName contains (".")
| extend CommonSecurityLog_TimeGenerated = TimeGenerated
)
on $left.DomainName == $right.DestinationHostName
| project SourceUserName, DomainName, CommonSecurityLog_TimeGenerated, Description, Activity
| extend AccountCustomEntity = SourceUserName
| extend HostCustomEntity = DomainName
| extend URLCustomEntity = DomainName
- It is very close but in my case the ".storage.googleapis.com" and ",.office.com" would be coming from the other table (CommonSecurityLog in this example)
- Ok, I see now. You are looking for a way around the == only join. Searching each value from the left through the text blob on the right. Ooof... I will keep thining 😃
