Search matching data on list/datatable and table

Question

I am trying to search all requests that matches certain regular expression (simple wildcards mostly), but I am not sure how to proceed. I can easily do this with a single endpoint, where I would use it directly in the query, but I have multiple endpoints per type/group of requests I want to get data for (for example, I need all request for login that matches multiple endpoints, same for, logouts, posts, etc...).

I have, for example, a dynamic array (even tried with a string delimited by a semicolon, which I split) but I get the same error at the end.

let login = dynamic(["https://example.com/*/login*", "https://example.com/login*"]);
requests
| extend group = "Login"
| mv-expand endpoint = login
| where url matches regex endpoint

'matches regex' operator requires string arguments

Tried force casting and using typeof string, but that doesn't seem to help...

let login = dynamic(["https://example.com/*/login*", "https://example.com/login*"]);
requests
| extend group = "Login"
| mv-expand endpoint = login to typeof(string)
| where url matches regex tostring(endpoint) // tried with and without tostring

matches regex: failed to cast argument 2 to scalar constant

I tried using a datatable, I wanted to do something like this, but not sure how to proceed with that...:

let TEndpoints = datatable(group: string, endpoints: dynamic)
[
    "Login", dynamic(["https://example.com/*/login*", "https://example.com/login*"]),
    "Logouts", dynamic(["https://example.com/*/logout*", "https://example.com/logout*"]),
    "Register", dynamic(["https://example.com/*/register*", "https://example.com/register*"])
];

Note that the endpoints are really just example. The wildcards are due to different locales and geo-regions.

Anyone has any idea if this is achievable?

Thanks,

Answer 1

That was a challenging one.

It seems all of KQL regex functions & operators accept only string literals / arguments as patterns, except for one exception I was able to identify in context of print, e.g.:

print str = 'A', pat = '.' 
| where str matches regex pat

or

print str = 'A', pat = '.' 
| union (print str = 'B', pat = '.')
| where str matches regex pat

I was also able to manipulate partition by and use is it to answer your question.

Please note that I am not projecting the login column within the partition by brackets, however I do use login later on with match regex. My guess is that login, the partition value, is stored behind the scenes in a variable and therefore can be used with match regex.

P.S.
The patterns you supplied are not regex patterns, but globbing, so I changed them accordingly.

let requests = datatable(id:int, url:dynamic)
[
    1 ,dynamic(["https://example.com/login123","https://example.com/foo/bar/login123"]),
    2 ,dynamic(["https://example.com/tic/login","https://example.com/tic/login/tac/toe"])
];
let login = dynamic(["https://example.com/[^/]+/login.*", "https://example.com/login.*"]);
requests
| mv-expand url
| mv-expand login = login
| extend url = tostring(url), login = tostring(login)
| partition by login (project id, url | where url matches regex login)

id	url
1	https://example.com/login123
2	https://example.com/tic/login
2	https://example.com/tic/login/tac/toe

Fiddle