How to join 2 tables based on a partial match

I'mLenny 51 Reputation points
2023-11-30T12:50:00.3566667+00:00

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?

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
    2023-12-01T09:18:14.9766667+00:00

    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?

    enter image description here

    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