how to use wildcard (*) for join parameter in KQL?

Question

I'm racking my brain with this and would like some help. :)

I want to know how to use wildcard(*) for join union parameter.

I need to join two tables with the same names in the fields, however, some fields may come with the wildcard(*), since for this field I want all to be validated.

My exceptions table:

let table_excep=  datatable (Computer:string,Event_id:string, logon_type:string) 
[
"Pc_01","*","4", 
"Pc_02","4648","*", 
"*","*","60" 
];

My data table:

let table_windows=  datatable (Computer:string,Event_id:string, logon_type:string)
[ 
"Pc_01","5059","4",
"Pc_02","4648","1",
"Pc_03","61","60"
];

When running, it doesn't bring anything in the result.

For this union, I want the 3 union fields to be considered, ie based on the exceptions table, if computer_name is Pc_01 and logon_type is 4, no matter what event_id is, this log should be displayed, since the field of eventi_id in the exception list is wildcard(*).

I'm not finding a way to solve this problem since the join condition only allows "==" and "and".

pasting the definitions of the datatables will make it much easier to understand and answer the question — Avnera, Aug 11 '21 at 12:39
Hi Avnera. Thanks for the suggestion. I just edited the text to make the tabala content clearer. — luizao_f, Aug 11 '21 at 13:09

score 0 · Answer 1 · answered Feb 26 '22 at 09:07

cross join (inner join on 1=1) + where

let table_excep=  datatable (Computer:string,Event_id:string, logon_type:string) 
[
"Pc_01","*","4", 
"Pc_02","4648","*", 
"*","*","60" 
];
let table_windows=  datatable (Computer:string,Event_id:string, logon_type:string)
[ 
"Pc_01","5059","4",
"Pc_02","4648","1",
"Pc_03","61","60"
]; 
table_excep | extend dummy = 1 
| join kind=inner (table_windows | extend dummy = 1) on dummy 
| where     (Computer == Computer1 or Computer == '*') 
        and (Event_id == Event_id1 or Event_id == '*')
        and (logon_type == logon_type1 or logon_type == '*')

Computer	Event_id	logon_type	dummy	Computer1	Event_id1	logon_type1	dummy1
Pc_01	*	4	1	Pc_01	5059	4	1
Pc_02	4648	*	1	Pc_02	4648	1	1
*	*	60	1	Pc_03	61	60	1

Fiddle

how to use wildcard (*) for join parameter in KQL?

1 Answers1