Clear ALL call detail records

[Andrew Byrd]

A few upgrades ago you could go to Call Detail Records, click select all and delete

Now the little ticker box (square white box) to the left of the records is no longer there. Not sure why the developer felt it necessary to remove this.

I have Fusion installed on Centos

Can someone tell me what to use in SSH to delete all call detail records?

Thank you

 

[ad5ou]

Have a look at line 63 in the maintenance script in link below. The command will delete all records older than 90 days. Pretty simple to modify the sql query to clear all if you really want to.

https://github.com/fusionpbx/fusion...debian/resources/backup/fusionpbx-maintenance

*Yes, the script is for Debian but can be adapted for centos

 

[Davesworld]

The removal of the radio buttons beside each call detail record as well as a select all radio button is very irritating. Anyone who has seen junk records added from sql injection attempts or other override attempts to place calls knows what I am talking about. If it is still in work and a date range deletion feature is being added then that is good, if not, why why why was it removed completely?

It would appear that the delete php is gone completely and an attempt was made to merge it into one of the main php files, if so it doesn't work.

 

[DigitalDaz]

If you really want to get rid of all just log into postgres and do a:

truncate table v_xml_cdr;

 

[Davesworld]

If you really want to get rid of all just log into postgres and do a:

truncate table v_xml_cdr;

I don't want to get rid of legitimate calls made and received, just the bogus hack call attempts.

 

[DigitalDaz]

The OP did ask that. The best way to deal with your issue is to stop the calls generating the cdr in the first place by using the appropriate fail2ban filters which by default are disabled. To get the crap out of the cdrs you need, probably, to craft an sql statement that looks for calls to the ip address rather than to one of your domains.

 

[markjcrane]

Actually CDR delete is a permission. To make it slightly more secure that permission is not given to superadmin group by default. You add the permission and logout and back in. Then you will be able to delete call detail records from the web interface. If there are a lot of calls then SQL is the right tool for the job.

 

[Davesworld]

Actually CDR delete is a permission. To make it slightly more secure that permission is not given to superadmin group by default. You add the permission and logout and back in. Then you will be able to delete call detail records from the web interface. If there are a lot of calls then SQL is the right tool for the job.

OK, this is a good explanation and I am glad to see you chime in Mark. This may help the OP provided he is still watching the thread. I found that you have to find it in the Group Permissions under advanced however when I check the delete function, it does not save after hitting the save button.

 

[Davesworld]

The OP did ask that. The best way to deal with your issue is to stop the calls generating the cdr in the first place by using the appropriate fail2ban filters which by default are disabled. To get the crap out of the cdrs you need, probably, to craft an sql statement that looks for calls to the ip address rather than to one of your domains.

I was beginning to wonder about the same method of cleaning out anything via sql that is only pointed at my ip. I once had reverse lookup enabled on my domain, once I saw a mischievous attempt point to my domain I quickly decided that it was a bad idea.

 

[Davesworld]

Actually CDR delete is a permission. To make it slightly more secure that permission is not given to superadmin group by default. You add the permission and logout and back in. Then you will be able to delete call detail records from the web interface. If there are a lot of calls then SQL is the right tool for the job.

Ah I see what you may have meant, the xml_cdr_delete field was removed from the database so by adding the permission as you say it has to be added back into the database as an added line and superuser as the user. It works perfectly now.

 

[Davesworld]

In case anyone was wondering, the actual file xml_cdr_delete.php was deprecated and the function moved over the xml_cdr.php file a few months back. It still looks for the same permission xml_cdr_delete if it exists, well one can make it exist by adding it to the database.

 

[ad5ou]

Permission entries will exist if you run "Permission defaults" on upgrade page. Some permissions, such as xml_cdr_delete, are disabled by default to keep from "accidentally" deleting something.

 

[Davesworld]

Permission entries will exist if you run "Permission defaults" on upgrade page. Some permissions, such as xml_cdr_delete, are disabled by default to keep from "accidentally" deleting something.

Without adding it to the database hence "permission if it exists" as it is stated in xml_cdr.php, you cannot tick the box and have it save that permission in the gui alone, I tried it on a second installation on a separate device as well. Same for archive, this is all in the master branch in case there is any confusion as to which branch I am talking about. While on this I change the protection to true for the superadmin so that I don't accidentally change permissions I deliberately enabled. I don't see where the need to tick Permission Defaults during upgrades is even necessary unless I missed something completely here, if so please advise.

Was the ability for the superadmin to delete records ever a common problem? The superadmin has the power to delete or change lots of things as it is necessary to administer. One of the reasons I like the delete function is for garbage records that occasionally occur from unsuccessful attacks. Having a page or more of these is annoying. FusionPBX is a gui, if one is doing more and more by the command line, then it is more of administering Freeswitch by hand and less with the FusionPBX gui. You want to talk about accidentally deleting records? The fastest way to accidentally delete records is to log into the database and start administering commands to clear records where you find out exactly what they did after the fact. If the gui is operating in this function, you can select them each visually.

In the Master Branch we both lost our ability to delete records with the gui by default as well as administer the database and import one of our daily database backups if needing to restore what we accidentally deleted from the gui as well. Adminer is gone.

As an aside, another thing I do is to have /var/lib/freeswitch on it's own partition and move backups to that folder as well and change the path in variables. This allows one to do a clean installation and have the last number of days worth of database backups as well as any voicemails, recordings and such so long as you do not format this partition, then install FusionPBX/Freeswitch by the Fusionpbx install script. I only mention this because we have backups of the last few or more days of both the database and the relevant files separate from the database in their own tarballs as well.

 

[markjcrane]

If you want to understand more you could attend FusionPBX Continuing Education so you could see the reasons for the changes. We talk about the changes once a month in Continuing Education. The monthly Continuing Education helps you keep up with new features, security changes and more.

Permissions are designed with security in mind. Not everyone will understand the reasons why however there are very good reasons for the changes. You could skip the 'Restore Default' button but then you will have to carefully think about the permissions when you upgrade and decide which ones should be added to each group.

Adminer is a good tool but it was a security risk and took effort to maintain. You can use a desktop database tool and connect to the server over SSH.

 

[Davesworld]

If you want to understand more you could attend FusionPBX Continuing Education so you could see the reasons for the changes. We talk about the changes once a month in Continuing Education. The monthly Continuing Education helps you keep up with new features, security changes and more.

Permissions are designed with security in mind. Not everyone will understand the reasons why however there are very good reasons for the changes. You could skip the 'Restore Default' button but then you will have to carefully think about the permissions when you upgrade and decide which ones should be added to each group.

Adminer is a good tool but it was a security risk and took effort to maintain. You can use a desktop database tool and connect to the server over SSH.

I was wondering about the security implications of adminer and you answered that. As far as records, I was sure there were good reasons, I would imagine that in some use cases call records would need to be available for review without an easy way to cover tracks by simply deleting them.

In using another database tool I decided to look at the cdr, as it turns out there were over 100000 records that were bogus attempts that don't even show in the gui. I was easily able to take that down to the legitimate 399 records that I have that DO show in the gui by using the command line and using specifics that only apply to the bogus records (hack attempts). This took less than two years to accumulate all that garbage. I did a VACUUM after that. I'll look into the Continuing Education now, thanks Mark!

 

[asuresh77]

SELECT * FROM v_xml_cdr WHERE caller_id_name LIKE '701' AND caller_id_number LIKE '701' AND extension_uuid IS NULL;

DELETE FROM v_xml_cdr WHERE caller_id_name LIKE '701' AND caller_id_number LIKE '701' AND extension_uuid IS NULL;

#-------------------------------------------------
#--OK-- UNIVERSAL LINE TO REMOVE RECORDS -----
#-------------------------------------------------
DELETE FROM v_xml_cdr WHERE caller_id_name = caller_id_number AND extension_uuid IS NULL;

 

[asuresh77]

Use this script below or modify as you like. It did work for me. (I used this cript to remove HACKERS cdr records only.)


#!/bin/bash
# -----------------------------------------------------------------------------
echo "psql" >/tmp/Delete-CDR-records
echo "\c fusionpbx" >>/tmp/Delete-CDR-records
echo "DELETE FROM v_xml_cdr WHERE caller_id_name = caller_id_number AND extension_uuid IS NULL;" >>/tmp/Delete-CDR-records
echo "\q" >>/tmp/Delete-CDR-records
echo "exit" >>/tmp/Delete-CDR-records
# --------------------------------------
echo
cat /tmp/Delete-CDR-records
echo
#--------------------------------------
su postgres < /tmp/Delete-CDR-records
exit 0