我想根据 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)