Determine count of devices sending data from a zone in Panorama logging

[talford]

Hi,

I've been asked to provide a count of all IP addresses sending data from a specific zone over a period of weeks from Panorama\NGFW logs, but exporting data from the logs into Excel and removing duplicate IP's is proving impractical due to the amount of data.

Is there a way to create a log query to do something like a SQL DISTINCT query, or is there some other way to get this information?

Thanks in advance

[Mudhireddy · Accepted solution]

There are multiple way to do it

1. Using Log Filters in the Web UI (Quick Check)
If you just need a rough count:

Go to Monitor → Logs → Traffic.
Use the Query Builder to filter for:
Edit
( zone.src eq <your_zone> )
Export results to CSV (if necessary), then use a tool like PowerShell or Python to extract unique IPs

2. If you have imported the firewall logs into an SQL database (e.g., Microsoft SQL Server, MySQL, PostgreSQL), you can use a SQL query to get the count of unique IPs

SELECT COUNT(DISTINCT `Source Address`) AS Unique_IP_Count FROM firewall_logs
WHERE `Zone` = '<your_zone>' AND `Timestamp` BETWEEN '2024-02-01' AND '2024-02-20';
example: 
SELECT COUNT(DISTINCT [Source Address]) AS Unique_IP_Count FROM firewall_logs
WHERE [Zone] = 'trust' AND [Timestamp] BETWEEN '2024-02-01' AND '2024-02-20';

or last using CLI > show log traffic query "( zone.src eq <your_zone> )" direction equal forward limit 100000 | match source

[talford]

Thanks - I've effectively been doing option 1 but there's so much data that each export only contains a small timeframe and the max export record count is limited.   Unfortunately the SQL option isn't available, but I'll have a look at the CLI option and see if that helps.