How to join 2 tables based on a partial match

I'mLenny 51 Reputation points


I have a query where I have a list of command line arguments and tools they are associated with in a Watchlist of ~500 entries in the following format:

Column1 Column2
argument1 Tool1
argument2 Tool2
argument2.1 Tool2
... ...
argumentn+1 Tooln+1

I am then querying my datatable to identify where a column contains one of the values in Column1 using the following KQL Query:

let myWatchlist = (_GetWatchlist('watchlist-alias') | project Column1, Column2);
| where columnvalues has_any (myWatchlist)
| project ColumnA, ColumnB, ColumnC

What I want to do now is whenever there is any results I want to project the identified value from Column1 and Column2, for example:

| project ColumnA, ColumnB, ColumnC, Column1, Column2

I am having trouble achieving this, as the join operators only work on exact matches. I have found this question on StackOverflow and modified the solution to work with my query but I can't quite get it working the way I want. This returns the the CommandLineArgument only and not the AttackerTool

let packedArgs = (
	| project Column1, Column2
	| extend packedValues = pack_array(Column1, Column2)
	| project packedValues
); //pack values into an array
let myList = toscaler (
	| summarize make_list(packedValues)
}; //transform array into scalar
let myWatchlist = (_GetWatchlist('watchlist-alias') | project Column1, Column2);
let matchedArgs = (
	| where columnvalues has_any (myWatchlist)
	| project ColumnA, ColumnB, ColumnC

Does anyone know what I should be doing?

Microsoft Sentinel
Microsoft Sentinel
A scalable, cloud-native solution for security information event management and security orchestration automated response. Previously known as Azure Sentinel.
1,057 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Clive Watson 5,951 Reputation points MVP


    I'm not sure I understand what you want the output to look like, if I use Datatables to make this easier:

    let myWatchlist = datatable ( Column1:string, Column2:string)
    let myDataTable = datatable ( ColumnValues:string, ColumnValues2:string)
    | join (myDataTable) on $left.Column1==$right.ColumnValues

    This gets me to this result (then you could rename the columns), but I'm not sure this is the format you want?

    enter image description here