CODEWITHSUNDEEP
kqlazure-sentinel
kbo-eqs
kbo-eqs

Reputation: 11

KQL: Merge 2 column of the same table

I'm trying to merge 2 column of the same table into 1 column.

from

Col1 Col2
1 4
2 5
3 6

into

Col3
1
2
3
4
5
6

I'm a KQL newbie. My goal is to have a query, extract information of two column, merge the two columns into a new one and perform some string manipulation to extract the data that I want (domain names from emails addresses)

I have try with join, extend and summarize.

my current unworking query is the following (MS Sentinel):

let emaildomain = dynamic(['aaa', 'bbb']); EmailEvents | where RecipientEmailAddress in (emaildomain) or SenderFromDomain in (emaildomain) | extend mailsaddreses = RecipientEmailAddress, SenderFromAddress | project mailsaddreses | project splitted = split(mailsaddreses, '@') | project domainnames = splitted[1] | distinct tostring(domainnames) | where domainnames !has "myCompany"

the simplified query is the following

let emaildomain = dynamic(['AAA.com']); EmailEvents | where RecipientEmailAddress in (emaildomain) or SenderFromDomain in (emaildomain) | distinct RecipientEmailAddress, SenderFromAddress

where I want "RecipientEmailAddress", "SenderFromAddress" to be in the same column

Thank you in advance for your help :)

Upvotes: 1

Views: 617

Answers (1)

Yoni L.
Yoni L.

Reputation: 25955

you can use the union operator.

for example:

let T = datatable(Col1: int, Col2: int)
[
    1, 4,
    2, 5,
    3, 6
]
;
T
| project Col3 = Col1
| union (T | project Col3 = Col2)
Col3
1
2
3
4
5
6

Upvotes: 1

Related Questions