We recommend switching to the latest versions of Edge, Firefox, Chrome or Safari. Using Internet Explorer will result in a loss of website functionality.

Date and Time Reformat

Comments

3 comments

  • Avatar
    Adrian Williams

    Note, you will need to either ensure there are no Null values in the input data, or modify the above script to handle the Null values.

    0
    Comment actions Permalink
  • Avatar
    Tevita Fainga

    Thank you Adrian...here is the complete data that i'm trying to split them into different column name according to each field name but at this time they all under one column name "Record".

    Thu Mar 12 00:00:03 2020
    Acct-Session-Id = "0/0/3/320_032848C5"
    Framed-IP-Address = 43.255.148.191
    Framed-Protocol = PPP
    User-Name = "waterfro1@kalianet.to"
    Cisco-AVPair = "connect-progress=LAN Ses Up"
    Cisco-AVPair = "nas-tx-speed=1000000000"
    Cisco-AVPair = "nas-rx-speed=1000000000"
    Acct-Session-Time = 5382
    Acct-Input-Octets = 71721063
    Acct-Output-Octets = 256278499
    Acct-Input-Packets = 175185
    Acct-Output-Packets = 238067
    Acct-Authentic = RADIUS
    Acct-Status-Type = Interim-Update
    NAS-Port-Type = Ethernet
    NAS-Port = 50331968
    NAS-Port-Id = "0/0/3/320"
    Connect-Info = "ACCESS-DYNAMIC"
    Cisco-AVPair = "client-mac-address=c471.5457.9edd"
    Cisco-AVPair = "circuit-id-tag=1 atm 02/34:0.34"
    Service-Type = Framed-User
    NAS-IP-Address = 10.254.32.14
    PMIP6-Home-HN-Prefix = 3546:3732:3343::/50
    Event-Timestamp = "Mar 12 2020 00:00:03 +13"
    NAS-Identifier = "ha_bng2.kalianet.to"
    Acct-Delay-Time = 0
    Proxy-State = 0x3836
    Acct-Input-Octets64 = 71721063
    Acct-Output-Octets64 = 256278499
    FreeRADIUS-Acct-Session-Start-Time = "Mar 11 2020 22:30:21 +13"
    Tmp-String-9 = "ai:"
    Acct-Unique-Session-Id = "242a5d975725a2e3cb54058f74c10900"
    Timestamp = 1583924403

    0
    Comment actions Permalink
  • Avatar
    Adrian Williams

    Assuming each input data record comprises the structure in your example above you can use a FIlter node to separate out the record datetime element and split out the embedded key value pairs:

     

    The output of the node would be as follows:

     

    However, the above view of the output data has been sorted by the 'key' field to highlight that each input record contains multiple elements with the same key, Typically, the Pivot Data to Names node would be used to transpose the data so that the values of the key fields could be used as field names and the 'value' field's contents could be used as the corresponding value for the new field. However, in this case the node would generate an error as you cannot have multiple output fields with the same name (Cisco-AVPair in this case).  

     

    You would need to disambiguate the key field's value in line with your requirements before the data could be transposed using the Pivot Data to Names node.

     

    The example is attached as a text file (copy the contents onto the canvas) and also as a .brg file (for LAE 6.x).

     

    Attached files

    Output_Embedded_Key_Value_Pairs.txt
    Output_Embedded_Key_Value_Pairs.brg

     

    0
    Comment actions Permalink

Please sign in to leave a comment.