FAZ - custom report unique IPs in logs for a given time period
We need to generate a report with a list of unique IPs featuring on all logs for a given time period.
Have tried using Chart builder, this is the query for unique IPs and destination ports grouped by IP Address
select `srcip`, string_agg(distinct (`dstport`)::text, ' ') as dstport__agg_ from ###(select `srcip`, `dstport` from $log where $filter and (logflag&1>0) group by `srcip`, `dstport`)### t group by `srcip`
Is there any way to add the first and last times the IP featured in the logs with this?
I am trying to build a table with the following columns:
Sorry but I am not a SQL expert by any means. I am confirming though that you have all of the information you need in the FAZ logs to build the report. It's up to you to create the query or find a SQL expert who can do it.
The logic would be:
1. Find all unique IP addresses existing in traffic logs
2. Iterate on those IP addresses to extract the relevant information
3. Find time stamp of first returned log entry
4. Find time stamp of last returned log entry
Here's a question for you though: what is the purpose of this report? What useful information are you getting out of it? Perhaps there's another way of getting that info?
Very often there is a requirement to extract unique IPs between a given time period. Say, from midnight to 3 am for instance. This may be filtered down further based on other parameters like say a destination IP or a destination port or perhaps a hostname.
The purpose of this report is to provide the above data. The inbuilt reports which are available in FAZ provide the Top users and not all users.
Yes but why do you have the requirement to extract unique IPs? What useful feedback is it providing? Again if you can give some insight into the why we can possibly come up with a different/better way of gleaning this information.
why do you have the requirement to extract unique IPs?
-to identify the hosts on the network that are involved in the matter being worked upon. Say, we are looking for hosts who've visited a given website, unique IPs generating traffic to the website would be the starting point.
The Fortinet Security Fabric brings together the concepts of convergence and consolidation to provide comprehensive cybersecurity protection for all users, devices, and applications and across all network edges.