Como ler os campos de um bloco de texto?

Question

Eu gostaria de criar inserções sql com base nos campos de log do Radius. Por exemplo, eu tenho esses dois tipos de registros contábeis e gostaria de converter cada bloco de texto em uma inserção como abaixo:

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

O script precisa ler o bloco por bloco, identificar seus campos e criar inserções com base nele.

Se possível, script de shell.

Obrigado!

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

    

Answer 1

Solução em TXR :

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

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

Executar:

$ txr inserts.txr data
INSERT INTO accouting (date, User-Name, NAS-IP-Address, NAS-Port, Service-Type, Framed-Protocol, Framed-IP-Address, Framed-IP-Netmask, Vendor-Specific, NAS-Identifier, Acct-Status-Type, Acct-Delay-Time, Acct-Session-Id, Acct-Authentic, Event-Timestamp, NAS-Port-Type, Unisphere-Egress-Policy-Name, NAS-Port-ID, Unisphere-Ingress-Policy-Name)
VALUES ('Thu, 04 Feb 2016 00:21:55.600', [email protected], 200.200.200.200, 552296838, Framed, 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, Start, 0, 97022017, RADIUS, 1454552515, Ethernet, block-out, 1073994681.17180390:, block-in)
INSERT INTO accouting (date, User-Name, NAS-IP-Address, Service-Type, Framed-Protocol, Framed-IP-Address, 3GPP-Charging-Id, Called-Station-Id, Calling-Station-Id, NAS-Identifier, Acct-Status-Type, Acct-Input-Octets, Acct-Output-Octets, Acct-Session-Id, Acct-Session-Time, Acct-Input-Packets, Acct-Output-Packets, Acct-Terminate-Cause, Acct-Input-Gigawords, Acct-Output-Gigawords, Event-Timestamp, 3GPP-CG-Address, NAS-Port-Type, 3GPP-PDPType, 3GPP-GGSN-MCC-MNC, 3GPP-Negotiated-DSCP, 3GPP-SGSN-MCC-MNC, 3GPP-IMSI, 3GPP-Selection-Mode, 3GPP-NSAPI, 3GPP-SGSN-Address, 3GPP-User-Location-Info, 3GPP-Session-Stop-Indicator, 3GPP-IMSI-MCC-MNC, 3GPP-Charging-Characteristics, 3GPP-MS-Timezone, 3GPP-RAT-Type, 3GPP-IMEISV, 3GPP-GGSN-Address, 3GPP-GPRS-QoS-Profile)
VALUES ('Thu, 04 Feb 2016 00:21:30.455', telco, 172.23.10.11, Framed, GPRS PDP Context, 10.2.3.1, 1239512257, telco.net, 132333355555, 10.1.4.67, Stop, 0, 0, BB481C4649e174c1, 0, 0, 0, User Request, 0, 0, 1454552490, 0.0.0.0, Virtual, 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)