Hi Everyone,
I am struggling to define time within query for workbook. My kql is represent 10 deny action followed by 1 allow connection with same external source IP to private destination IP with in 300 second. First deny connection should be Start time and first allow connection should be end time. Source IP should be summarize so we can check how many external source ip successed to make connection with in 24 hours or 48 hours. Please some one help me to get proper result for workbook. when i am running this query below notification occurs:
'summarize' operator: Failed to resolve scalar expression named 'TimeGenerated'
let threshold=10;
let threshold1=1;
let authenticationWindow = 5m;
let a=
CommonSecurityLog
| where DeviceVendor == "Palo Alto Networks" and DeviceProduct =~ "PAN-OS"
| where DeviceAction == "deny"
| where (ipv4_is_private(SourceIP) == 'False' and ipv4_is_private(DestinationIP) == 'True')
| summarize denycount = count() by DeviceAction, SourceIP, DestinationIP
| where denycount >= ["threshold"];
let b=
CommonSecurityLog
| where DeviceVendor == "Palo Alto Networks" and DeviceProduct =~ "PAN-OS"
| where DeviceAction == "allow"
| where (ipv4_is_private(SourceIP) == 'False' and ipv4_is_private(DestinationIP) == 'True')
| summarize allowcount = count() by DeviceAction, SourceIP, DestinationIP
| where allowcount >= ["threshold1"];
a|join kind = inner(b) on SourceIP
| summarize StartTimeUtc = min(TimeGenerated), EndTimeUtc = max(TimeGenerated) by bin(TimeGenerated, authenticationWindow), SourceIP, DestinationIP, denycount, allowcount
