How to combined query with same table.

[akshay250692]

Hi Guys,

I am adding new column in CommonSecurity Table. But i am having issue in kql quey. Please help me. This is Palo alto related logs. As "cat" field is in both Threat and System. So it is going to overlap. thats why i have to run  query with "OR" operator so Threat "cat" field will not overlap with System "cat" field. Please help me.

.

CommonSecurityLog
| where Activity in ("TRAFFIC", "THREAT")
| extend SessionEndReason_CF = extract('reason=([^;]+)',1, AdditionalExtensions)
| extend ThreatContentName_CF = extract('cat=([^;]+)',1, AdditionalExtensions)
| extend thr_category_CF = extract('PanOSThreatCategory=([^;]+)',1, AdditionalExtensions)

combined with "OR" condition

| where Activity == "SYSTEM"
| extend EventID_CF = extract('cat=([^;]+)',1, AdditionalExtensions)

[#2]

Would this help?

CommonSecurityLog
| where Activity in ("TRAFFIC", "THREAT")
| where AdditionalExtensions contains "cat="
| extend threat_ = iif (Activity=="THREAT",extract('cat=([^;]+)',1, AdditionalExtensions),"")
| extend system_ = iif (Activity=="SYSTEM",extract('cat=([^;]+)',1, AdditionalExtensions),"")
| extend all_ = strcat(threat_,system_)

[#3]

not working as expected

[#4]

@akshay250692 in what way, please explain? 

[#5]

CommonSecurityLog
| where Activity in ("TRAFFIC", "THREAT")
| extend SessionEndReason_CF = extract('reason=([^;]+)',1, AdditionalExtensions)
| extend ThreatContentName_CF = extract('cat=([^;]+)',1, AdditionalExtensions)
| extend thr_category_CF = extract('PanOSThreatCategory=([^;]+)',1, AdditionalExtensions)
up to here i m good.
now in same table "commonsecuritylog" there is another activity called "system" in which also "cat" field as already same "cat " field in above query for activity "threat". if i m applying query then it is overlapping system "cat" with threat "cat". so i want to separate column for both "cat" field. so that overlapping will not happen.
i want merge above query with below query
| whrer activity in ("SYSTEM")
| extend EventID_CF = extract('cat=([^;]+)',1, AdditionalExtensions)

is it possible ?

[#6]

@Clive_Watson 

works for me, unless akshay was expecting another result.

(I don't have system logs coming in so an empty column is expected)

[#7]

clive define for particular
| where AdditionalExtensions contains "cat="
that is not my requirement.

[#8]

Just remove that line, it was there just to filter the records down in my testing, line 6 you may not want either. This was merely to show you the iif/iff command in use