So I just saw a story on this list of robocallers the fcc is going to release every week. How could I automate importing the csv file into my blacklist?
https://consumercomplaints.fcc.gov/hc/en-us/articles/205239443-Data-on-Unwanted-Calls
This is not genericâŚ
So you need to download the list. The link changes weekly so you have to account for the range dates manually
https://consumercomplaints.fcc.gov/hc/theme_assets/513073/200051444/Telemarketing_RoboCall_Weekly_Data100115-102015.csv
assuming the base address is always the same you can simply change 100115 and 102015
Then you can load it to an array:
$file = fopen("Telemarketing_RoboCall_Weekly_Data100115-102015.csv","r");
$data = fgetcsv($file);
fclose($file);
This will give you an array called $data
If you are using bootstrap ( http://wiki.freepbx.org/display/FOP/Bootstrap ) and 13
you can
foreach ($data as $row){
/*normalize data to your environment. $row[3] is a npa-npx-xxxx format. */
/*$bldata should be array('description'=>$row[0], 'number' => $yourdata)*/
\FreePBX::Blacklist()->numberAdd($bldata);
}
I had some communication with the FCC about this data, and convinced them to make the data available at an unchanging url. Hereâs the url thatâll always have the most up-to-date data (which they plan to update weekly):
Was thinking about code something like this, any thoughts?:
sort -t, -k3,3 -u | awk -F"," '{print "/usr/sbin/asterisk -rx \"database put blacklist " $3 " \'" $0 "\'\""}'I would never try and put that many numbers in the ASTDB. it will bomb out. Its not designed to handle that many entires.
I would be nice if a CID Superfecta service would be created for the list and would be updated. There is a spam item on the list, I think, but if I remember correctly, it isnât very effective.
how many, ballpark, numbers would you say is too many for the blacklist?
Hi!
By bombing out you mean it will be slow, right?
Apparently since Asterisk 10 they use SQLite 3 which supports a file size of 140 GB and a theoretical maximum number of rows in a table of 2^64âŚ
They say nothing about performance thoughâŚ
The current FCC database has around 12 thousand entries currentlyâŚ
Thank you and have a nice day!
Nick
The current file has phone numbers in column 3, or 4 or both, and sometimes theyâre the same, and sometimes theyâre different. A valid telephone number from this file is nnn-nnn-nnnn.
cat blacklist.csv | awk -F"," '{print $3 "\n" $4}' | sort -u | grep "[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9]" | wc -l
5525
So, of all the numbers in the current file, there are only 5525 unique ones.
I started this discussion with the Superfecta folks, and the conclusion over there was that the spam functionality over there wasnât so good:
It would work, but the spam interception aspects of Superfecta have not been
well maintained, so that would have to be fixed before effort was put into
making the FCC list work. My gut feeling is that implementing the robocall list
would best be done independently of Superfecta.
It seems to me that the database lookup in Superfecta should certainly scale well into the several thousands of records⌠but I donât know what might be broken with the spam aspects of Superfecta. Does anyone here know?
Adam
Well,as to scaling, Asterisk has moved from Berkeley to sqlite3, (which is arguably faster than mysql for what it does, and would be a good candidate to replace mysql in FreePBX (JMHO) ) , then I would suggest that blacklist would be relatively easy and quick enough, even with 6k or even âlots ofâ entries, but you would need to purge every week the old ones to maintain those few K entries or they will grow . . ⌠, a CSV to import into astdb.sqlite3 (you can google for that) needs a unique identifier for the âvalueâ of the /blacklist/nnnnnnnnnn/ key to enable later purging , perhaps use column h âthe date discoveredâ, so a csv to import might be derived something like:-
â/blacklist/(column c)â,â(column h)â
be careful of ânon-digitsâ in your derived (column c) or it wonât work and in the NANP world it needs to be 10 digits. YMMV , I threw it up on a couple of machines to check, the intrinsic âAsterisk blacklistâ code delay was not noticeable, I think the âbomb outâ concept needs support before it is believable, so far no hits though 
I guess we are waiting for Trump or Bush to start doing it . . .
Whatâd you throw up? The full list? Whatâd you use to load it up?
the standard sqlite3 client, it is apparently not installed in the âdistroâ but perhaps it is available in their repos. ( I donât use them)
Any problems , google for âsqlite3 import csvâ
Oops, good pointâŚ
By the way, itâs looks like thereâs an easy way to do ODBC lookup from Asterisk is the astdb solution turns up to have problems, see http://www.voip-info.org/wiki/view/Asterisk+func+func_odbc .
Good luck and have a nice day!
Nick
@Marbled, please be careful. asterisk does NOT use mysql, it has an internal database, namely the sqlite3 database /var/lib/asterisk/astdb.sqlite3 (the old berkley astdb is no longer supported but importable). Asteriskâs blacklist is based on a key/value pair that is is slick as shit off a chrome shovel. the key is /blacklist/number/ the value can be anything.
Please donât confuse the databases, using ODBC to query a derivative MYSQL would indeed be seriously unnecessary . . . entries in astdbâs blacklist are very quick, populate and use it for best effect.
Hi dicko,
I knowâŚ
But tonyclewis warned against using sqlite earlier so thereâs always that fallback, essentially reimplementing the blacklist lookup that way⌠It wouldnât be much worse than using CID Superfecta for thatâŚ
Please read what I said above, I actually mentioned the use of sqLite for the astdb before youâŚ
Have a nice day,
Nick
I think I said I didnât agree with Tony, have you compared and contrasted? Have you even tried it?
I know you donât agree with him but he must have had his reasons to say thatâŚ
I was just suggesting another course of action in case it doesnât workâŚ
As to trying it I have no use for that as I am not in the US⌠The chances of those guys calling up north (I am Canadian) are there but not that greatâŚ
Just so you know I tend to agree with you actually (see my post above about the âlimitationsâ of sqlite 3) but Tony must have had his reasons to say that so until he explains them letâs not disregard other possible course of actionâŚ
Have a nice day,
Nick
I agree, we will have to wait for his supportive arguments to his statement as to why it would âbomb outâ
Here is my list of sticking points:
AstDB
Everything in asterisk runs in and out of ASTDB. Adding 6000+ entries will at a minimum slow everything down and at worse make things crash. When you are processing calls milliseconds matter. If you are running a beefy system this is probably less of a concern. If you are running a pi or some other low powered platform this matters a lot.
Formatting:
If you pump these numbers directly in to any database you have NPA-NPX-XXXX and it is unlikely your provider sends caller id that way. This means it will be pointless to add those 6000+ entries without massaging the data a bit. This is why a generic import solution is not really effective here. Your provider may do +1NPANPXXXXX or NPANPXXXX or 1NPANPXXXXX you need to make the data conform to the received data type.
The right way:
Load this data in to a database and throw together a basic api:
Fields: NPA, NPX,LASTFOUR all indexed (we want fast people)
http://localhost/fcc.php?get=[ANY FORMAT]
In the php script with the power of regex extract the segments from the number and
and search the database returning 1 or 0 to the API caller
Use superfecta to blacklist.
Final issue Futility:
These numbers are burners and may only get used for a day. companies know you are trying to block these calls and use random dids and cycle through them. It is unlikely any of these numbers will actually call you. Some places just use your area code and 7 random digits. Legal? No! Legal doesnât matter when enforcement is so bad they have to publish a csv and say do it yourself.
One-click installer is now available on the PIAF Forum in the Developer section. Requires Asterisk 11 or 13.
2 Likes
Be careful Ward, posting breadcrumbs to code here which is by privileged access only by any means here will get your ass bit surely Man up make it open source , that was your call originally , no? . . .