How to join 2 tables based on a partial match

Question

Hello,

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

Does anyone know what I should be doing?

Answer 1

Hello,

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)
[
    'arg1','Tool1',
    'arg2','Tool2'
];
let myDataTable = datatable ( ColumnValues:string, ColumnValues2:string)
[
    'arg1','Tool-11',
    'arg2','Tool-22',
    'arg3','tool-33'
];
myWatchlist
| 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?

https://portal.azure.com#@4bd2cd73-7c32-48aa-8a02-646c8bc0d343/blade/Microsoft_Azure_Monitoring_Logs/DemoLogsBlade/resourceId/%2FDemo/source/LogsBlade.AnalyticsShareLinkToQuery/q/H4sIAAAAAAAAA8tJLVHIrQxPLEnOyMksLlGwVUhJLAHCpJxUBQ0F5%252Fyc0tw8Q6vikqLMvHQdKN8IytfkiuZSAAL1xKJ0Q3Ud9ZD8%252FBwgDRczgooZqXPFWnPlgG1yAZodAjYdm01hiTmlqcVo1kEE8Vmqa4jNWl0jIyRRY6BoCUjU2BjsHCRPc9UoZOVn5iloIDlPUyE%252FT0ElJzWtRA8aCLa2KkWZ6RkwPsRVAG0LF8U%252FAQAA/timespan/P1D