How to populate FreePBX CDR with SIP Messages?

[craig24x (Craig Eustice)]

Trying to log SIP messages in the CDR , Below is what I think ‘should’ be working.
I have this setup in extensions_custom.conf and can send/receive messages fine, just no CDR items.

[pjsip-msg]
;3digit internal exten
exten => _XXX,1,MessageSend(pjsip:${EXTEN},"${CALLERID(name)}"${MESSAGE(from)})
exten => _XXX,n,Set(CDR(userfield)=${MESSAGE(body)})
;External numbers
exten => _XXXXXXXXXXX,1,Set(CALLERID(num)=12345678901)
exten => _XXXXXXXXXXX,n,System(Do some external stuff)
exten => _XXXXXXXXXXX,n,Set(CDR(userfield)=${MESSAGE(body)})

[tonyclewis (Tony Lewis)]

You would have legality issues storing those messages if you are based in the US. They passed laws on this in 2006.

[craig24x (Craig Eustice)]

Thanks for the tip, This would be CDR reference for billing purposes. Using message body in the UserField is just for testing , I would end up placing an account number or external text charge rate there.

So, legality aside… why wouldn’t the code work as written? Is there something else in freepbx preventing it?

[BlazeStudios (Tom Ray)]

In regards to your CDR issue, messages are not calls. The CDRs are for calls which means when you use MessageSend() it’s not going to trigger any CDRs for the message. You would need to track that on your own.

[craig24x (Craig Eustice)]

Thanks, understood.
I was hoping there was a way to initiate CDR for out of call usage, apps, etc… but sounds like there’s absolutely no way of doing that unless it’s in conjunction with a call.

[dicko]

The cdr record is started with a “dial” and ends when one party hangs-up , between those two events you can modify the CDR variables that are read/write-able

So, try wrapping your call to ‘messaging’ within perhaps a call to playing silence/5 but before the ‘playing’ and from an appropriate local channel/custom extension , if the channel is still up on its return, you can hangup the call after writing your variables.

[billsimon]

With func_odbc, you can write your own SQL functions that become available from the dialplan. FreePBX is already set up to access your CDR database with ODBC so it’s pretty easy to write a custom CDR generator that you can use anywhere, including in your messages context.

In /etc/asterisk/func_odbc.conf (this file is not managed by FreePBX, so you may edit it without worry), add a new section to define your SQL function. I wrote one that looks like this:

[MESSAGECDR]
dsn=asteriskcdrdb
writesql=INSERT INTO cdr SET calldate=NOW(), clid='${SQL_ESC(${VAL1})}', src='${SQL_ESC(${VAL2})}', dst='${SQL_ESC(${VAL3})}', lastapp='MessageSend', disposition='${VAL4}', userfield='${SQL_ESC(${VAL5})}'

The dsn=asteriskcdrdb is already configured for you by FreePBX. The writesql line defines the SQL query. I am inserting the most relevant fields. Anywhere you have user-generated data be sure to use the SQL_ESC function to sanitize the value.

Now in my dialplan I can call this function after doing a MessageSend:

(from /etc/asterisk/extensions_custom.conf; I’m using old chan_sip here but you can easily rework it for pjsip)

[messages]
; Deliver to local 4-digit extension
exten => _XXXX,1,MessageSend(sip:${EXTEN},${MESSAGE(from)})
same => n,Set(FROMUSER=${CUT(MESSAGE(from),<,2)})
same => n,Set(FROMUSER=${CUT(FROMUSER,@,1)})
same => n,Set(FROMUSER=${CUT(FROMUSER,:,2)})
same => n,Set(ODBC_MESSAGECDR()=${MESSAGE(from)},${FROMUSER},${EXTEN},${MESSAGE_SEND_STATUS},${MESSAGE(body)})
same => n,ExecIf($["${MESSAGE_SEND_STATUS}" == "FAILURE"]?Goto(messages,mail-${EXTEN},1))
same => n,Hangup()
...

After some manipulation to get the From user I put the values into CDR using the new ODBC function defined. It ends up looking like this in the CDR report:

Hovering over the caller ID field I get the full URI as a tooltip.

[craig24x (Craig Eustice)]

Thanks! working like a champ