CODEWITHSUNDEEP
azurekql
newbie-python
newbie-python

Reputation: 3

Column contains text contains other column

Thanks in advance, I don't know much about KQL or English

    let bad_software = datatable(d: dynamic) [
        dynamic(["Windows 10 its cool"]),
        dynamic(["Windows 11 its cool"]),
        dynamic(["linux its the best"])
    ];

    let mysoftware = datatable(d: dynamic) [
        dynamic(["Windows"]),
        dynamic(["Windows 11"]),
        dynamic(["Some words"])
    ];

I need to have in the mysoftware table, the records whose value is in bad_software,but not literal, contains

Example, in this case, i need: FROM MYSOFTWARE windows windows 11

Let me explain it another way: I have a table with a list of software with vulnerabilities, for example: "Windows 10 Kerberos failure" "linux systemd failure"

I have a table with computers example: "windows" "linux" "mac" In this case, I would like to obtain: Windows, windows 10 kerberos failure linux, linux systemd failure

Ex:

table 1:

["rod is handsome"],
["rod is nice"],
["alice is pretty"]

table 2:

["rod"],
["alice"],
["Ben"]

output I need

rod,rod is handsome
rod,rod is nice 
alice, alice is pretty

I tried with in(table) but it doesn't do "contains"

That is, I make the union with a contains, not with an IN

thank you.

Upvotes: 0

Views: 45

Answers (1)

Rakesh Govindula
Rakesh Govindula

Reputation: 11529

You can use the below query to achieve your requirement.

First expand the dynamic array using mv-expand in both tables and store as string column. Now, cross join both tables by using a placeholder column and use contains on the required columns.

let bad_software = datatable(d: dynamic) [
    dynamic(["rod is handsome"]),
    dynamic(["rod is nice"]),
    dynamic(["alice is pretty"])
];

let mysoftware = datatable(d: dynamic) [
    dynamic(["rod"]),
    dynamic(["alice"]),
    dynamic(["Ben"])
];

let a=bad_software
| mv-expand d
| extend total_str = tostring(d);

let b= mysoftware | mv-expand d | extend req_str = tostring(d);

a | extend placeholder=1
| join kind=inner (b | extend placeholder=1) on placeholder
| where total_str contains req

Output:

enter image description here

Upvotes: 0

Related Questions