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 from Table "B". Something like:
A join B on $left.ErrorMessage contains $B.Keyword
GaryBushey I have it working but not sure if it is the best way to do it. First, I add a dummy column to each table that contains the same value, then I do a full outer join on the two tables using those new columns. Since I just need to get a count of the matching items I can do
summarize countif(TableBcolumn contains TableAcolumn) by TableAcolumn- 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- 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 😃
