Forum Discussion

akshay250692's avatar
akshay250692
Brass Contributor
Oct 31, 2022

How to combined query with same table.

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)

KQL
  • Clive_Watson's avatar
    Clive_Watson
    Bronze Contributor
    Oct 31, 2022
    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_)

Resources