#265 remove_offline_addresses.php not removing offline ip addresses

[Anonymous]

Hello,

I updated my phpipam installation from 1.2.1 to 1.3 and everything is working.
The only issue I have is that when I run script remove_offline_addresses.php it does not return any error but does not remove offline ip addresses that are offline many months...

Do you know how to remove old offline addresses?

thank you

P.S: VERY nice work with the new update, Tons of usefull stuff

[Miha Petkovsek]

Hi, there is new directove in config.dist.php:

# remove_offline_addresses.php script parameters
$config['removed_addresses_send_mail'] = true;       // true/false, send or not mail on pomoving inactive addresses
$config['removed_addresses_timelimit'] = 86400 * 7;  // int, after how many seconds of inactivity address will be deleted (7 days)

Copy it to config.php

[Anonymous]

I have copied all of that and even changed timelimit to only 86400 but nothing...

[Miha Petkovsek]

Can you manually run this query to see if you get any results:

select
            `ip`.`id`,`ip`.`ip_addr`,`ip`.`lastSeen`,`ip`.`subnetId`,`ip`.`description`,`ip`.`dns_name`,`ip`.`lastSeen`,
            `su`.`subnet`,`su`.`mask`,`su`.`sectionId`,`su`.`description`,
            'delete' as `action`
         from
            `ipaddresses` as `ip`, `subnets` as `su`
         where
            `ip`.`subnetId` = `su`.`id`
            and `su`.`pingSubnet` = 1
            and `ip`.`excludePing` != 1
            and
            (`ip`.`lastSeen` < '$beforetime' and `ip`.`lastSeen` != '0000-00-00 00:00:00' and `ip`.`lastSeen` is not NULL);

replace $beforetime with date you wish to delete addresses from (e.g. 2016-06-01 00:00:00 for before first of June).

Also, as you can see there are some requirements:

• Address must have been active at least once
• Subnet must be selected for scanning

br

[Anonymous]

ok I will try this querry
Also for the requirements

addresses appeared from scanning of phpipam so they were active

[Anonymous]

mysql> select
-> ip.id,ip.ip_addr,ip.lastSeen,ip.subnetId,ip.description,ip.dns_name,ip.lastSeen,
-> su.subnet,su.mask,su.sectionId,su.description,
-> 'delete' as action
-> from
-> ipaddresses as ip, subnets as su
-> where
-> ip.subnetId = su.id
-> and su.pingSubnet = 1
-> and ip.excludePing != 1
-> and
-> (ip.lastSeen < '2016-06-01 00:00:00' and ip.lastSeen != '0000-00-00 00:00:00' and ip.lastSeen is not NULL);

Empty set (0.02 sec)

The set is Empty and my phpipam is full of offeline addresses....
I remember there wa a scipt to delete this stale addresses pruneOffline.php but cannot find it

[Stergiou Thanasis]

I made an account in order not to wait to be moderated

mysql> select
-> ip.id,ip.ip_addr,ip.lastSeen,ip.subnetId,ip.description,ip.dns_name,ip.lastSeen,
-> su.subnet,su.mask,su.sectionId,su.description,
-> 'delete' as action
-> from
-> ipaddresses as ip, subnets as su
-> where
-> ip.subnetId = su.id
-> and su.pingSubnet = 1
-> and ip.excludePing != 1
-> and
-> (ip.lastSeen < '2017-06-10 00:00:00' and ip.lastSeen != '0000-00-00 00:00:00' and ip.lastSeen is not NULL);
Empty set (0.00 sec)

My set is EMPTY then I put today date

mysql> select
-> ip.id,ip.ip_addr,ip.lastSeen,ip.subnetId,ip.description,ip.dns_name,ip.lastSeen,
-> su.subnet,su.mask,su.sectionId,su.description,
-> 'delete' as action
-> from
-> ipaddresses as ip, subnets as su
-> where
-> ip.subnetId = su.id
-> and su.pingSubnet = 1
-> and ip.excludePing != 1
-> and
-> (ip.lastSeen < '2017-06-20 00:00:00' and ip.lastSeen != '0000-00-00 00:00:00' and ip.lastSeen is not NULL);
+-------+------------+---------------------+----------+----------------------------------------+---------------------------+---------------------+------------+------+-----------+-------------+--------+
| id | ip_addr | lastSeen | subnetId | description | dns_name | lastSeen | subnet | mask | sectionId | description | action |
+-------+------------+---------------------+----------+----------------------------------------+---------------------------+---------------------+------------+------+-----------+-------------+--------+
| 18977 | 3232236976 | 2017-06-18 16:00:02 | 8 | MS assessment and toolkit planning map | hqpmap01.hq.aegeanair.com | 2017-06-18 16:00:02 | 3232236800 | 24 | 3 | HQ Servers | delete |
+-------+------------+---------------------+----------+----------------------------------------+---------------------------+---------------------+------------+------+-----------+-------------+--------+
1 row in set (0.01 sec)

mysql>

also i think action is for every record delete!!!

[dmds]

works only if
and ip.excludePing is NULL
instead
and ip.excludePing != 1

[Miha Petkovsek]

excludePing can also be 0.

Try with

ip.excludePing != "1"

or

ip.excludePing <> 1

[dmds]

i'm sorry... it works fine with ip.excludePing != 1
but only if set ip.lastSeen=current month

[Miha Petkovsek]

This is set each time script loads dinamically.

Please add

print $query;

to line 48 in functions/scripts/remove_offline_addresses.php and check if date part is ok after you execute script.

[dmds]

Oh, sorry again ....
everything works perfectly!))

[Anonymous]

I added print $query;

The querry

select
ip.id,ip.ip_addr,ip.lastSeen,ip.subnetId,ip.description,ip.dns_name,ip.lastSeen,
su.subnet,su.mask,su.sectionId,su.description,
'delete' as action
from
ipaddresses as ip, subnets as su
where
ip.subnetId = su.id
and su.pingSubnet = 1
and ip.excludePing != 1
and
(ip.lastSeen < '2017-06-27 11:13:57' and ip.lastSeen != '0000-00-00 00:00:00' and ip.lastSeen is not NULL);

The problem i thing is that and ip.excludePing != 1 dows not match the NULL entries.
So I chenged to

(ip.excludePing != 1 or ip.excludePing is NULL)

and the query returns some resuts

can you tell me if that is correct?

[Miha Petkovsek]

Yes it seems to be. Strange that it does not match.

Which sql version do you have ?

[Stergiou Thanasis]

Server version: 5.5.55-0+deb8u1 (Debian)

[Anonymous]

[Anonymous]