Lookup Calls Against CDR

FreePBX Development
21

then noop() and DumpChan() are your friend

22

I’ve tested using Dynamic Routes using the SHELL method linked above and it’s working as epxected with this expression:

${SHELL(mysql -Ns asteriskcdrdb -e 'SELECT COUNT(*) FROM `cdr` WHERE `src` LIKE "[NUMBER]" AND (calldate  > NOW() - INTERVAL 5 MINUTE)')}
23

Wow! Thanks @lgaetz! Beautiful!

I’d still love to figure out why the dialplan version won’t work just for my own personal learning. I used your expression verbatim in the dialplan like this:

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

Your code works perfectly in my CLI. But this line of code refuses to set any value for the variable in dialplan code. I’ve tried NoOp, DumpChan, etc, and hours of googling and research — but am not able to come up with any reason for this behavior.

Do you have any pointers I could consider about why this might be, or how to rephrase this line of code to do what I want?

Thank you!

24

Someone suggested to me that this may be a permissions issue.

What user would this custom dialplan run as by default?

Are there permissions or user changes I would normally need to make to run a command like this?

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

Thanks!

25

Asterisk should be running as the asterisk user. You can test at the bash prompt with:

sudo -u asterisk mysql -Ns asteriskcdrdb -e 'SELECT   ... etc
26

Interesting. So I ssh’d into the server, elevated to root, and ran:

[root@PBX/]# sudo -u asterisk mysql -Ns asteriskcdrdb -e 'SELECT COUNT(*) + 1 FROM cdr WHERE src='my_number_redacted' AND (calldate  > NOW() - INTERVAL 2 MINUTE)'

And the result I got was:

ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'asteriskcdrdb'

So does this mean that the asterisk user doesn’t have access to the database?

27

You can use the freepbx mysql credentials which are in /etc/freepbx.conf for the sql query.

28

That was it!! It works now!! Thank you!!

29

So for anyone who wants to duplicate this, here’s the final code that’s working for me:

[identify_repeat_caller]
exten => s,1,answer()
exten => s,n,Set(PHONE=${FILTER(0123456789,${CALLERID(num)})})
exten => s,n,Set(NUMBEROFCALLS=${SHELL(mysql -Ns -u “username_here” -p”password_here" asteriskcdrdb -e 'SELECT COUNT(*) FROM cdr WHERE src LIKE "${PHONE}" AND (calldate  > NOW() - INTERVAL 1 MINUTE)')})
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)
1 Like
30

Here’s a script I made some time ago, helped me filter out all the robos

create a Custom Destination with a goto string of irobot-blocker,s,1
choose the Return option and set the destination to where ever you want legit calls to go
route calls through an Announcement saying something like “please enter the following digits”
then route to the Custom Destination.
add the following lines to the file /etc/asterisk/extensions_custom.conf
string: [irobot-blocker]

[irobot-blocker]
exten => s,1,Noop(Entering context irobot-blocker in extensions_custom.conf)
exten => s,n,Answer
exten => s,n,Set(Count=0)
exten => s,n(restart),set(goal=${RAND(0,9)}${RAND(0,9)}${RAND(0,9)}${RAND(0,9)}) ; generates a 4 digit number, modify as req’d
exten => s,n,SayDigits(${goal})
exten => s,n,Read(dtmf-in,${LEN(${goal})},10) ; 10 second timeout, modify as req’d
exten => s,n,execif($[“${dtmf-in}”=“${goal}”]?Return) ; if input matches, send back to FreePBX
; give caller multiple chances
exten => s,n,Set(Count=$[${Count}+1])
exten => s,n,gotoif($[“${Count}”=“3”]?finish) ; bail after 3rd attempt
exten => s,n,Playback(challenge_try_again) ; sub your own recording
exten => s,n,goto(restart)
; what do you want to do with calls that fail?
exten => s,n(finish),Set(DB(blacklist/${CALLERID(number)})=1)
exten => s,n,hangup

1 Like
31

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.