log_registrations.conf.xml

<!--

API commands provided by the module.

* reglog config reload                      # reloads configuration
* reglog config set logger <temp>           # change logger temporarolly
* reglog config show logger                 # show current logger
* reglog config show                        # show config for current logger
* reglog cache <event | all> expire         # expire records from all | specific event cache

-->



<configuration name="log_registrations.conf">
  <settings>
    <!--
      database dsn
        postgres = pgsql://
        sqllite  = sqlite://
        odbc     = odbc://
        core     = user:password:database
    -->
    <param name="db-dsn" value="freeswitch:freeswitch:freeswitch6396!@#"/>
    <param name="db-table" value="log_registrations"/>
    <!-- treat sip username and domain case sensitive -->
    <param name="sip-uri-case-sensitive" value="1"/>
    <param name="clean-cache" value="3600"/>
    <param name="logger" value="default"/>

   </settings>
   <loggers>
    <logger name="default">
        <!-- allowed values are, "register-attempt" "register" "pre-register" "expire" "unregister" -->
        <event name="register_attempt">
            <!-- a record will be inserted for each event the total number of records will not exceed this amount.
                0 = no limit,  Defaults to 0. -->
            <param name="max-records" value="0"/>
            <param name="max-unique-records" value="1"/>
            <!-- fields to make a record unique. comma seperated list
                 valid fields are "contact, user, realm"
                 eg. <param name="max-unique-records" value="user,realm"/>
                 Default = "contact"
            -->
            <param name="unique-key" value="contact"/>
            <!-- create index (if not exists) on the columns involving unique-key, this is recommended if on-max-reached='cycle'. defaults to true -->
            <param name="db-index-unique" value="1"/>
            <!-- interval (in seconds) between inserts -->
            <param name="record-interval" value="1800"/>
            <!-- update existing most recent record, if it is within 'record-interval' time. Default = false -->
            <param name="record-update-last" value="0"/>
            <!-- check in the database for existing records when the module loads
                this maybe usefull so it does not insert more records than configured above
                if the module is being reloaded. Defaults to true -->

            <!-- What to do when max limit has reached
                options are:
                    cycle   = update the oldest matching (which matches the unique-key) record.
                    update  = update the most recent record
                    pause   = stop logging
            -->
            <param name="on-max-reached" value="cycle"/>
            <param name="check-records-on-load" value="1"/>
            <param name="cache-expire" value="3600"/>
        </event>
        <event name="register">
            <param name="max-records" value="0"/>
            <param name="max-unique-records" value="1"/>
            <param name="record-interval" value="1800"/>
        </event>
        <event name="unregister">
            <param name="max-records" value="0"/>
            <param name="max-unique-records" value="1"/>
            <param name="record-interval" value="0"/>
        </event>
        <event name="expire">
            <param name="max-records" value="0"/>
            <param name="max-unique-records" value="1"/>
            <param name="record-interval" value="0"/>
        </event>
        <!--
        <event name="pre_register">

            </event>
            -->
        </logger>

        <logger name="temp">

        </logger>
    </loggers>
</configuration>

log_registrations.sql

CREATE TABLE log_registrations (
  id INT PRIMARY KEY AUTO_INCREMENT,
  profile VARCHAR(255) NOT NULL,
  user VARCHAR(255) NOT NULL,
  realm VARCHAR(255) NOT NULL,
  contact VARCHAR(255),
  callid VARCHAR(255),
  rpid VARCHAR(255)DEFAULT NULL,
  status VARCHAR(255),
  expires INT NOT NULL,
  user_agent VARCHAR(255),
  local_ip VARCHAR(255),
  local_port VARCHAR(255),
  public_ip VARCHAR(255),
  public_port VARCHAR(255),
  auth_result VARCHAR(255),
  event_type VARCHAR(255),
  core_uuid VARCHAR(255),
  time INT NOT NULL,
  KEY time_idx (time),
  KEY user_idx (user),
  KEY realm_idx (realm)
   );


DROP VIEW IF EXISTS registrations_view;
CREATE VIEW registrations_view AS (
	SELECT
	t1.id,
	t1.user,
	t1.realm,
	t1.contact,
	t1.public_ip,
	t1.public_port,
	t1.local_ip,
	FROM_UNIXTIME(t1.time + t1.expires) AS exp,
	t1.callid,
	t1.time,
	t1.user_agent,
	t1.core_uuid
	FROM log_registrations AS t1
	LEFT JOIN log_registrations AS t2 ON ((t1.callid = t2.callid) AND (t2.event_type IN ('unregister', 'expire') ))	
	WHERE (t1.event_type = 'register') AND (t1.time > t2.time OR t2.time IS NULL)
	);
   
   
DROP VIEW IF EXISTS location;   
CREATE VIEW location AS (
	SELECT
	id AS id,
	user AS username,
	realm AS domain,
	contact AS contact,
	concat(public_ip, ':', public_port, ' ', COALESCE(local_ip, 'N/A')) AS received,
	NULL AS path,
	exp AS expires,
	1.00 AS q,
	callid AS callid,
	13 AS cseq,
	FROM_UNIXTIME(time) AS last_modified,
	0 AS flags,
	NULL AS cflags,
	user_agent AS user_agent,
	NULL AS socket,
	NULL AS methods,
	core_uuid AS sip_instance,
	NULL AS attr
	FROM 
	registrations_view AS t
	WHERE t.exp > NOW()
);