How to read fields of a text block?

Question

I would like to create sql inserts based on Radius log fields. For example, I have these two types of accounting logs and I'd like to convert each text block to an insert like below:

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

The script has to read the block by block, identify its fields and create inserts based on it.

If possible, shell script.

Thank you!

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

score 1 · Answer 1 · answered Sep 04 '16 at 15:11

Solution in TXR:

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

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

Run:

$ 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)

How to read fields of a text block?

1 Answers1