FreeRadius Accounting

data-raw.sql

CREATE TABLE IF NOT EXISTS `data_raw` (
  `acctsessionid` varchar(32) COLLATE utf8_czech_ci DEFAULT NULL,
  `username` varchar(64) COLLATE utf8_czech_ci DEFAULT NULL,
  `nasipaddress` varchar(15) COLLATE utf8_czech_ci DEFAULT NULL,
  `acctsessiontime` int(11) DEFAULT NULL,
  `acctinputoctets` bigint(20) DEFAULT NULL,
  `acctoutputoctets` bigint(20) DEFAULT NULL,
  `prev_acctsessiontime` int(11) DEFAULT NULL,
  `prev_acctinputoctets` bigint(20) DEFAULT NULL,
  `prev_acctoutputoctets` bigint(20) DEFAULT NULL,
  `diff_acctsessiontime` int(11) DEFAULT NULL,
  `diff_acctinputoctets` bigint(20) DEFAULT NULL,
  `diff_acctoutputoctets` bigint(20) DEFAULT NULL,
  `framedipaddress` varchar(15) COLLATE utf8_czech_ci DEFAULT NULL,
  `timenow` datetime DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`),
  KEY `data_raw_acctsessionid_index` (`acctsessionid`),
  KEY `data_raw_username_index` (`username`),
  KEY `data_raw_nasipaddress_index` (`nasipaddress`),
  KEY `data_raw_framedipaddress_index` (`framedipaddress`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;

trigger.sql

CREATE TRIGGER `trRadacctUpdateRow` BEFORE UPDATE ON `radacct`
 FOR EACH ROW insert into `data_raw` set 
                acctsessionid = old.acctsessionid,
                username = old.username, 
                nasipaddress = old.nasipaddress, 
                framedipaddress = new.framedipaddress, 
                acctsessiontime = new.acctsessiontime, 
                acctinputoctets = new.acctinputoctets, 
                acctoutputoctets = new.acctoutputoctets,
                prev_acctsessiontime = old.acctsessiontime, 
                prev_acctinputoctets = old.acctinputoctets, 
                prev_acctoutputoctets = old.acctoutputoctets,
                diff_acctsessiontime = (new.acctsessiontime - old.acctsessiontime),
                diff_acctinputoctets = (new.acctinputoctets - old.acctinputoctets),
                diff_acctoutputoctets = (new.acctoutputoctets - old.acctoutputoctets),
                timenow = now()