Query Needed Fields

Source Data Queries

Queries to get at the source data from radactt* that we use to populate unbound_cdr.

Related Docs

Draft Queries

server_id

servicetype = transit

select distinct substring_index(siptranslatedrequesturi, '@', 1),
  substring_index(siptranslatedrequesturi, '.', 1)
  from radacct201005 where servicetype = 'transit'
;

servicetype = outgoing

select distinct substring_index(callingstationid, '@', 1)
  from radacct201005 where servicetype = 'outgoing'
;

unique_id

We’ll concatentate three pieces into a string that will be unique across radacct* tables across mutiple SIP proxies

select concat('syd1', '_2010_05_', radacctid) from radacct201005;

calldate

select acctstarttime from radacct201005;

did

select substring_index(calledstationid, '@', 1)
  from radacct201005
;

ani

servicetype = transit

select distinct substring_index(callingstationid, '@', 1)
  from radacct201005 where servicetype = 'transit'
;

dialed_number

select substring_index(calledstationid, '@', 1)
  from radacct201005
;

is_mobile

derive from destinationid?

duration

select acctsessiontime from radacct201005;

billable_duration

select acctsessiontime from radacct201005;

billed_amount

select price from radacct201005;

customer_billed_amount

select price from radacct201005;

disposition

use acctterminatecause

select acctterminatecause from radacct201005;

country

Determine the country from the destinationid which we’ll use to look the destination name in the rate_sheets table

select destinationid from radacct201005;

provider_type

select 'symbio' from radacct201005;

direction

select
(case servicetype
 when 'outgoing' then 'outbound'
 else 'inbound'
 end
) from radacct201005;

international

determine from destinationid and rate_sheets table

call_type

ditto

My tags:

 

Popular tags: