FAZ - custom report unique IPs in logs for a given time period

[vik99]

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:

S.No. 

IP Address

First seen time

Last seen time

[Anthony_E]

Hello vik99,

Thank you for using the Community Forum. I will seek to get you an answer or help. We will reply to this thread with an update as soon as possible.

Thanks,

[Anthony_E]

Hello vik99,

We are still looking for someone to help you.

We will come back to you ASAP.

Regards,

[vik99]

Thank you Anthony, fingers crossed!

[gfleming]

You can probably do this but keep in mind "first seen" time will be dependent on how often you are running this report and how far back your analytics logs go.

So if you have say 15 days of analytics logs and are running this report every week you may not get the true "first seen" time.

[vik99]

thanks for the reply. how do i go about getting the first and last seen times?

Also, hope the query in the initial post on this thread is accurate. The requirement is to extract a list of unique IP addresses for a given period of time as a report.

[gfleming]

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?

[vik99]

what is the purpose of this report?

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. 

[gfleming]

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.

[vik99]

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.