Lookup Calls Against CDR

Hi Everyone,

I’m creating a robo-caller blocking system whereby a caller is prompted to enter the sum of two numbers on their keypad in order to connect the call.

I want to also add an option whereby anyone who calls twice within 2 minutes gets through immediately on the second call without being prompted to sum the numbers. This is the part I need help with.

Is there any existing functionality in FPBX to lookup a callers number against the past two minutes of CDR data and made decisions based on that?

Thanks,
Randy

a shell script that would return the number of calls

mysql -Ns asteriskcdrdb -e 'SELECT COUNT(*) FROM cdr WHERE src='3235551212' AND (calldate  > NOW() - INTERVAL 2 MINUTES) '|tr -d '\n'

In practice, do you find this happening with legit calls where they hang up and call back immediately? I don’t experience it myself***, but I’ve seen reports where spammers will send two calls to a number at the same time so that while you are hanging up on the first one, they are leaving a message in VM with the second.

I’ve had a robocall filter for my main system for 3 years this month. It’s as simple as it gets, a prompt to enter ANY single dtmf tone and you’re in. I get a few live unsolicited callers a year and almost zero robots. I don’t think you need such an elaborate plan with adding numbers.

Robot challenges work well with the AllowList module. Once a CID is known to be good, it can bypass your challenge in the future.

*** I’ve since looked back thru calls that have been intercepted by the spam challenge and note that there are a fair number of instances where two or more inbound calls came in from the same CID in a very short time. Something like 5% of spam calls to me have this pattern.

Thank you @dicko ! This is great! You are a genius. I’ll work on a custom dialplan now, using your script. I appreciate your input so much!!

Thank you @lgaetz! My plan was to announce to callers that they can either: enter the sum of 1+1 on their keypad, or call back again within two minutes and be connected immediately. My reasoning for the within-2-minute-callback bypassing the phone captcha, was in case someone called in such an emergency that they were not able to pay attention to solving math problems, or if someone solved the problem correctly but still accidentally punched the wrong number on their keypad and their call was disconnected. Then at least if they called back right away, they wouldn’t have to be bothered with the captcha a second time.

It’s very useful to know your simpler prompt worked well. I was under the impression that most bots were intelligent enough to “effectively” press any key to continue. The fact that in your experience, most aren’t, is welcome news. I may simplify my robocall filter in that case.

1 Like

You can also look at @lgaetz’s old post on this subject Of Robocalls and Whitelists

Thank you all! Here’s what I have for dialplan code so far. Any improvements/suggestions?

[identify_repeat_caller]
exten => s,1,answer()

; 	output from mysql script always sets “numberOfCalls” equal to 0, unless caller has called in less than 2 min ago
Set(numberOfCalls=${SHELL(mysql -Ns asteriskcdrdb -e 'SELECT COUNT(*) FROM cdr WHERE src='${CALLERID(num)}' AND (calldate  > NOW() - INTERVAL 2 MINUTE)' | tr -d '\n')})

; 	if this is the callers 0th time to call, send them to the captcha IVR, otherwise send to welcome
exten => s,n,GotoIf($["${numberOfCalls}" = "0"]?Goto(ivr-12,s,1):Goto(app-announcement-8,s,1))
1 Like

Nothing wrong with what you’ve done, but it can be done in the Gui with dynroutes:

I need some help!
My latest iteration of the dialplan is like this:

[identify_repeat_caller]
exten => s,1,answer()

; 	sets “NUMBEROFCALLS” equal to 0, unless caller has called in less than 2 min ago
exten => s,n,Set(CALLERPHONENUMBER=${CALLERID(num)})
exten => s,n,NoOp(${CALLERPHONENUMBER})
exten => s,n,Set(NUMBEROFCALLS=${SHELL(mysql -Ns asteriskcdrdb -e 'SELECT COUNT(*) FROM cdr WHERE src="${CALLERPHONENUMBER}" AND (calldate  > NOW() - INTERVAL 2 MINUTE)')})
exten => s,n,NoOp(${NUMBEROFCALLS})

; 	if this is the callers 0th time to call, send them to the captcha IVR, otherwise send to welcome IVR
exten => s,n,GotoIf($[${NUMBEROFCALLS}=0]?captcha:welcome)
exten => s,n(captcha),Goto(ivr-12,s,1)
exten => s,n(welcome),Goto(app-announcement-8,s,1)

everything works correctly except for the line:

exten => s,n,Set(NUMBEROFCALLS=${SHELL(mysql -Ns asteriskcdrdb -e 'SELECT COUNT(*) FROM cdr WHERE src="${CALLERPHONENUMBER}" AND (calldate  > NOW() - INTERVAL 2 MINUTE)' | tr -d '\n')})

The logs show this output:

It appears that the NUMBEROFCALLS variable is empty.

I know the mysql script works, I can run it as sudo on the server and it reliably returns a single number indicating how many times the caller has called in the past 2 minutes.

But storing the output of the script doesn’t appear to be working.

Any thoughts?

Please post the logs (not pictures of the log)

Absolutely. Here’s the logs:

[2022-01-18 14:48:44] VERBOSE[32038][C-00004335] pbx.c: Executing [[email protected]_repeat_caller:2] Set("PJSIP/fpbx-1-I2c7nuvRHnOq-0002e424", "CALLERPHONENUMBER= 1xxxxxxxx67") in new stack
[2022-01-18 14:48:44] VERBOSE[32038][C-00004335] pbx.c: Executing [[email protected]_repeat_caller:3] NoOp("PJSIP/fpbx-1-I2c7nuvRHnOq-0002e424", "1xxxxxxxx67") in new stack
[2022-01-18 14:48:44] VERBOSE[32038][C-00004335] pbx.c: Executing [[email protected]_repeat_caller:4] Set("PJSIP/fpbx-1-I2c7nuvRHnOq-0002e424", "NUMBEROFCALLS=") in new stack
[2022-01-18 14:48:44] VERBOSE[32038][C-00004335] pbx.c: Executing [[email protected]_repeat_caller:5] NoOp("PJSIP/fpbx-1-I2c7nuvRHnOq-0002e424", "") in new stack
[2022-01-18 14:48:44] WARNING[32038][C-00004335] ast_expr2.fl: ast_yyerror():  syntax error: syntax error, unexpected '=', expecting $end; Input:
=0
^
[2022-01-18 14:48:44] WARNING[32038][C-00004335] ast_expr2.fl: If you have questions, please refer to https://wiki.asterisk.org/wiki/display/AST/Channel+Variables
[2022-01-18 14:48:44] VERBOSE[32038][C-00004335] pbx.c: Executing [[email protected]_repeat_caller:6] GotoIf("PJSIP/fpbx-1-I2c7nuvRHnOq-0002e424", "?captcha:welcome") in new stack

Also of interest. This works perfectly, and sets NUMBEROFCALLS equal to 1, and produces no errors in the dialplan:

exten => s,n,Set(NUMBEROFCALLS=${SHELL(echo 1)})

However this does not work, it stores no value in the variable, even though the mysql part produces perfectly repeatable output of a 1 or 0 etc, every time it is run in the command line:

exten => s,n,Set(NUMBEROFCALLS=${SHELL(mysql -Ns asteriskcdrdb -e 'SELECT COUNT(*) FROM cdr WHERE src="${CALLERPHONENUMBER}" AND (calldate  > NOW() - INTERVAL 2 MINUTE)')})

without the |tr -d '\n' then there will be a linefeed in the value that will screw you up.

Yes, I tried it both ways - with the |tr -d ‘\n’ at the end like this:

exten => s,n,Set(NUMBEROFCALLS=${SHELL(mysql -Ns asteriskcdrdb -e 'SELECT COUNT(*) FROM cdr WHERE src="${CALLERPHONENUMBER}" AND (calldate  > NOW() - INTERVAL 2 MINUTE)' | |tr -d '\n')})

and

exten => s,n,Set(NUMBEROFCALLS=${SHELL(mysql -Ns asteriskcdrdb -e 'SELECT COUNT(*) FROM cdr WHERE src="${CALLERPHONENUMBER}" AND (calldate  > NOW() - INTERVAL 2 MINUTE)')})

Neither version stores any value in the variable. It stays completely empty! And the log output is completely identical with or without the |tr -d ‘\n’.

Yet this reliably sets the variable to 1:

exten => s,n,Set(NUMBEROFCALLS=${SHELL(echo 1)})

Weird!

noop(${NUMBEROFCALLS})

Even it there was a call in the last 2 minutes?, perhaps add 1 to the mysql query and subtract it later in the math.

Yes! Even if there were several calls in the last 2 minutes! Completely odd. I’ll try adding 1 to the query - good idea.

Ok this is unbelievably weird.

When I run the updated query on the server’s CLI:

mysql -Ns asteriskcdrdb -e 'SELECT COUNT(*)+1 FROM cdr WHERE src="1xxxxxxxx67" AND (calldate  > NOW() - INTERVAL 2 MINUTE)' |tr -d '\n'

I get an output of 1,2, or 3, etc. However many times I called in the last 2 minutes, plus 1, exactly as expected.

I so changed the operative part of dialplan to this:

exten => s,n,Set(NUMBEROFCALLS=${SHELL(mysql -Ns asteriskcdrdb -e 'SELECT COUNT(*)+1 FROM cdr WHERE src="1xxxxxxxx67" AND (calldate  > NOW() - INTERVAL 2 MINUTE)' |tr -d '\n')})
exten => s,n,NoOp(${NUMBEROFCALLS})
exten => s,n,Set(NUMBEROFCALLS=$[${NUMBEROFCALLS} - 1])
exten => s,n,NoOp(${NUMBEROFCALLS})

And line by line as that part of dialplan code executes, this is the output:

3578391	[2022-01-18 16:36:53] VERBOSE[5119][C-0000442f] pbx.c: Executing [[email protected]_repeat_caller:3] NoOp("PJSIP/fpbx-1-I2c7nuvRHnOq-0002eb66", "1xxxxxxxx67") in new stack	
3578392	[2022-01-18 16:36:53] VERBOSE[5119][C-0000442f] pbx.c: Executing [[email protected]_repeat_caller:4] Set("PJSIP/fpbx-1-I2c7nuvRHnOq-0002eb66", "NUMBEROFCALLS=") in new stack	
3578393	[2022-01-18 16:36:53] VERBOSE[5119][C-0000442f] pbx.c: Executing [[email protected]_repeat_caller:5] NoOp("PJSIP/fpbx-1-I2c7nuvRHnOq-0002eb66", "") in new stack	
3578394	[2022-01-18 16:36:53] VERBOSE[5119][C-0000442f] pbx.c: Executing [[email protected]_repeat_caller:6] Set("PJSIP/fpbx-1-I2c7nuvRHnOq-0002eb66", "NUMBEROFCALLS=-1") in new stack	
3578395	[2022-01-18 16:36:53] VERBOSE[5119][C-0000442f] pbx.c: Executing [[email protected]_repeat_caller:7] NoOp("PJSIP/fpbx-1-I2c7nuvRHnOq-0002eb66", "-1") in new stack	
3578396	[2022-01-18 16:36:53] VERBOSE[5119][C-0000442f] pbx.c: Executing [[email protected]_repeat_caller:8] GotoIf("PJSIP/fpbx-1-I2c7nuvRHnOq-0002eb66", "0?captcha:welcome") in new stack

The NUMBEROFCALLS variable is STILL empty despite adding 1 to the mysql query, and confirming the query yields expected output when run directly in the CLI!

not a useful log 'cos you schargled it, mysql will not parse ‘xxxxx’ . wild cards would use ‘1%67’ to match such a string

Oh not to worry, I used my own real phone number there in the actual dial plan!

I just put the x’s here on the forum to mask my number. I didn’t do that in the dialplan!