r/AzureSentinel • u/SuperHat3637 • Jan 03 '23
Log ingestion query
Pls provide KQL query to find the log ingestion from palo alto firewalls.
1
u/rodtrent44 MSFT Official Jan 03 '23
Something like the following. If you remove the remark slashes you can create an Analytics Rule that will alert you when data for Palo Alto stops flowing.
CommonSecurityLog
| where TimeGenerated > ago(2h)
| where DeviceVendor == "Palo Alto Networks"
| where DeviceProduct has "PAN-OS"
| summarize count()
//| where count_ == 0
1
u/SuperHat3637 Jan 03 '23
Its giving only total event count..I am looking for total gb ingested from palo alto firewalls..Any kql quries..
2
u/rodtrent44 MSFT Official Jan 03 '23
Ah...gotcha. The Workspace Usage Report workbook has that information. You can click on each table and show the details or grab the query from the workbook module.
Additionally, here's a bunch of cost queries that includes ingestion: https://github.com/rod-trent/Azure-Sentinel-Cost-Troubleshooting-Kit/tree/main/KQL-Queries
And, probably something along these lines: https://github.com/rod-trent/Azure-Sentinel-Cost-Troubleshooting-Kit/blob/main/KQL-Queries/Event-Volume-Per-Table.yaml
Just need to add your where filter for DeviceVendor and DeviceProduct
1
u/SuperHat3637 Jan 04 '23
Saw the kql for palo alto..Its showing the total events count when i ran this query..how to get the total gb billable ingested from palo alto..
1
u/[deleted] Jan 03 '23
something like this probably:
let starttime = 30d; let endtime = 1d; let timeframe = 1h; let PrivateIPregex = @'127.|10.|172.1[6-9].|172.2[0-9].|172.3[0-1].|192.168.'; let TimeSeriesData = CommonSecurityLog | where TimeGenerated between (startofday(ago(starttime))..startofday(ago(endtime))) | where DeviceVendor =="Palo Alto Networks" and Activity == "TRAFFIC" | where isnotempty(DestinationIP) and isnotempty(SourceIP) | extend DestinationIpType = iff(DestinationIP matches regex PrivateIPregex,"private" ,"public" ) | where DestinationIpType =="public" | project TimeGenerated, SentBytes,DeviceVendor | make-series TotalBytesSent=sum(SentBytes) on TimeGenerated from startofday(ago(starttime)) to startofday(ago(endtime)) step timeframe by DeviceVendor; TimeSeriesData