如何读取文本块的字段?

如何读取文本块的字段?

我想根据 Radius 日志字段创建 sql 插入。例如,我有这两种类型的会计日志,我想将每个文本块转换为插入,如下所示:

INSERT INTO accouting (date, User-Name, NAS-IP-Address,...)
VALUES ('Thu, 04 Feb 2016 00:21:55.600', [email protected], 200.200.200.200,...)

该脚本必须逐块读取,识别其字段并基于它创建插入。

如果可能的话,shell 脚本。

谢谢你!

Thu, 04 Feb 2016 00:21:55.600
        User-Name = [email protected]
        NAS-IP-Address = 200.200.200.200
        NAS-Port = 552296838
        Service-Type = Framed
        Framed-Protocol = PPP
        Framed-IP-Address = 14.14.14.14
        Framed-IP-Netmask = 255.255.248.0
        Vendor-Specific = 00:00:13:0a:38:10:66:63:37:35:2e:31:36:34:63:2e:33:36:34:65
        NAS-Identifier = c-bl-rt-router
        Acct-Status-Type = Start
        Acct-Delay-Time = 0
        Acct-Session-Id = 97022017
        Acct-Authentic = RADIUS
        Event-Timestamp = 1454552515
        NAS-Port-Type = Ethernet
        Unisphere-Egress-Policy-Name = block-out
        NAS-Port-ID = 1073994681.17180390:
        Unisphere-Ingress-Policy-Name = block-in

Thu, 04 Feb 2016 00:21:30.455
        User-Name = telco
        NAS-IP-Address = 172.23.10.11
        Service-Type = Framed
        Framed-Protocol = GPRS PDP Context
        Framed-IP-Address = 10.2.3.1
        3GPP-Charging-Id = 1239512257
        Called-Station-Id = telco.net
        Calling-Station-Id = 132333355555
        NAS-Identifier = 10.1.4.67
        Acct-Status-Type = Stop
        Acct-Input-Octets = 0
        Acct-Output-Octets = 0
        Acct-Session-Id = BB481C4649e174c1
        Acct-Session-Time = 0
        Acct-Input-Packets = 0
        Acct-Output-Packets = 0
        Acct-Terminate-Cause = User Request
        Acct-Input-Gigawords = 0
        Acct-Output-Gigawords = 0
        Event-Timestamp = 1454552490
        3GPP-CG-Address = 0.0.0.0
        NAS-Port-Type = Virtual
        3GPP-PDPType = IPv4
        3GPP-GGSN-MCC-MNC = 1414
        3GPP-Negotiated-DSCP = 14
        3GPP-SGSN-MCC-MNC = 1414
        3GPP-IMSI = 724340302052610
        3GPP-Selection-Mode = 0
        3GPP-NSAPI = 8
        3GPP-SGSN-Address = 45.32.55.24
        3GPP-User-Location-Info = 00:27:f4:43:75:52:57:83
        3GPP-Session-Stop-Indicator = ff
        3GPP-IMSI-MCC-MNC = 1414
        3GPP-Charging-Characteristics = 0800
        3GPP-MS-Timezone = 88:01
        3GPP-RAT-Type = 01
        3GPP-IMEISV = 90849320842309834
        3GPP-GGSN-Address = 45.32.55.24
        3GPP-GPRS-QoS-Profile = 08-02070000080000005208

答案1

解决方案在TXR:

@(repeat)
@date
@  (collect)
 @key = @value
@  (last)

@  (end)
@  (output)
INSERT INTO accouting (date, @{key ", "})
VALUES ('@date', @{value ", "})
@  (end)
@(end)

跑步:

$ txr 插入.txr 数据
INSERT INTO 会计(日期、用户名、NAS-IP-地址、NAS-端口、服务类型、帧协议、帧 IP 地址、帧 IP 网络掩码、供应商特定、NAS 标识符、帐户状态类型、计费延迟时间、计费会话 ID、计费验证、事件时间戳、NAS 端口类型、Unisphere 出口策略名称、NAS 端口 ID、Unisphere 入口策略-姓名)
值 ('2016 年 2 月 4 日星期四 00:21:55.600',[电子邮件受保护], 200.200.200.200, 552296838, 框架, PPP, 14.14.14.14, 255.255.248.0, 00:00:13:0a:38:10:66:63:37:35:2e:31:36:34:63:2e :33:36:34:65、c-bl-rt-router、开始、0、97022017、RADIUS、1454552515、以太网、封锁、1073994681.17180390:、封锁)
INSERT INTO 计费(日期、用户名、NAS-IP-地址、服务类型、帧协议、帧 IP-地址、3GPP-计费 ID、被叫站 ID、主叫站 ID、NAS-标识符、计费状态类型、计费输入八位字节、计费输出八位字节、计费会话 ID、计费会话时间、计费输入数据包、计费输出数据包、计费终止原因、帐户输入-千兆字、帐户输出-千兆字、事件时间戳、3GPP-CG-地址、NAS 端口类型、3GPP-PDP 类型、3GPP-GGSN-MCC-MNC、3GPP-协商-DSCP、3GPP-SGSN- MCC-MNC、3GPP-IMSI、3GPP-选择模式、3GPP-NSAPI、3GPP-SGSN-地址、3GPP-用户位置信息、3GPP-会话停止指示符、3GPP-IMSI-MCC-MNC、3GPP-计费特性、3GPP-MS-时区、3GPP-RAT-类型、3GPP-IMEISV、3GPP-GGSN-地址、3GPP-GPRS-QoS-配置文件)
值(“2016 年 2 月 4 日星期四 00:21:30.455”,电信公司,172.23.10.11,已帧,GPRS PDP 上下文,10.2.3.1、1239512257、telco.net、132333355555、10.1.4.67、停止、0、0、 BB481C4649e174c1 , 0, 0, 0, 用户请求, 0, 0, 1454552490, 0.0.0.0, 虚拟, IPv4, 1414, 14, 1414, 724340302052610, 0, 8, 45.32.55.24, 00:27:f4:43:75: 52:57:83、ff、1414、0800、88:01、01、90849320842309834、45.32.55.24、08-02070000080000005208)

相关内容