Cisco CDR Hex2Ip mysql format

Hi all,

just my note about hex 2 ip conversion for Cisco UCM CDR log.



DELIMITER ;;

DROP FUNCTION IF EXISTS chex2ip;;
CREATE FUNCTION chex2ip (x INTEGER)
  RETURNS varchar(15)
  DETERMINISTIC NO SQL
  BEGIN
    DECLARE x1 varchar(2);
    DECLARE x2 varchar(2);
    DECLARE x3 varchar(2);
    DECLARE x4 varchar(2);
    DECLARE temp varchar(8);
    IF (x<0) THEN
       set temp = SUBSTR(CAST(HEX(x) AS CHAR),9,8);
    ELSE
       set temp = CAST(HEX(x) AS CHAR);
    END IF;
    set x1=substring(temp,1,2);
    set x2=substring(temp,3,2);
    set x3=substring(temp,5,2);
    set x4=substring(temp,7,2);
    RETURN concat(conv(x4,16,10),'.',conv(x3,16,10),'.',conv(x2,16,10),'.',conv(x1,16,10));
  END;;

DELIMITER ;

for other timestamp field, which is use unix, you can convert to mysql-format by :

SELECT from_unixtime(timestamp, '%Y %D %M %h:%i:%s')


here's some refs :

Cisco
Cisco CDR Header

..show all:.

.

Hi all,

just my note about hex 2 ip conversion for Cisco UCM CDR log.



DELIMITER ;;

DROP FUNCTION IF EXISTS chex2ip;;
CREATE FUNCTION chex2ip (x INTEGER)
  RETURNS varchar(15)
  DETERMINISTIC NO SQL
  BEGIN
    DECLARE x1 varchar(2);
    DECLARE x2 varchar(2);
    DECLARE x3 varchar(2);
    DECLARE x4 varchar(2);
    DECLARE temp varchar(8);
    IF (x<0) THEN
       set temp = SUBSTR(CAST(HEX(x) AS CHAR),9,8);
    ELSE
       set temp = CAST(HEX(x) AS CHAR);
    END IF;
    set x1=substring(temp,1,2);
    set x2=substring(temp,3,2);
    set x3=substring(temp,5,2);
    set x4=substring(temp,7,2);
    RETURN concat(conv(x4,16,10),'.',conv(x3,16,10),'.',conv(x2,16,10),'.',conv(x1,16,10));
  END;;

DELIMITER ;

for other timestamp field, which is use unix, you can convert to mysql-format by :

SELECT from_unixtime(timestamp, '%Y %D %M %h:%i:%s')


here's some refs :

Cisco
Cisco CDR Header