Forum Discussion
how to add custom field in "CommonSecurityLog" 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)
2 Replies
Hi, the issue is that if you put | where Activity == "SYSTEM" after | where Activity in ("TRAFFIC", "THREAT"), KQL will return nothing for SYSTEM because those rows were already filtered out.
Use one where statement first, then only populate the cat value into the correct custom column based on the Activity.
CommonSecurityLog
| where Activity in~ ("TRAFFIC", "THREAT", "SYSTEM")
| extend SessionEndReason_CF = iff(Activity in~ ("TRAFFIC", "THREAT"), extract(@"reason=([^;]+)", 1, AdditionalExtensions), "")
| extend ThreatContentName_CF = iff(Activity =~ "THREAT", extract(@"cat=([^;]+)", 1, AdditionalExtensions), "")
| extend thr_category_CF = iff(Activity =~ "THREAT", extract(@"PanOSThreatCategory=([^;]+)", 1, AdditionalExtensions), "")
| extend EventID_CF = iff(Activity =~ "SYSTEM", extract(@"cat=([^;]+)", 1, AdditionalExtensions), "")
This way the cat field from THREAT logs goes into ThreatContentName_CF, and the cat field from SYSTEM logs goes into EventID_CF, so they will not overlap.
If TRAFFIC also needs the reason field only, you can keep it separated like this too:
CommonSecurityLog
| where Activity in~ ("TRAFFIC", "THREAT", "SYSTEM")
| extend SessionEndReason_CF = iff(Activity =~ "TRAFFIC", extract(@"reason=([^;]+)", 1, AdditionalExtensions), "")
| extend ThreatContentName_CF = iff(Activity =~ "THREAT", extract(@"cat=([^;]+)", 1, AdditionalExtensions), "")
| extend thr_category_CF = iff(Activity =~ "THREAT", extract(@"PanOSThreatCategory=([^;]+)", 1, AdditionalExtensions), "")
| extend EventID_CF = iff(Activity =~ "SYSTEM", extract(@"cat=([^;]+)", 1, AdditionalExtensions), "")
So the main fix is: do not run two separate where filters one after another. Filter all needed activities first, then use iff() to control which custom field gets populated.
You may consider using KQL extend operator to create custom fields from existing data (like AdditionalExtensions). To avoid overlap between Threat and System logs, you must conditionally extract values based on the Activity field.
