Results 1 to 4 of 4

Thread: Rearrange raw Data set from columns to rows

  1. #1
    Contributor
    Join Date
    Dec 2011
    Location
    Nukualofa
    Posts
    9

    Default Rearrange raw Data set from columns to rows

    I have a data set from data source that I need to rearrange them from columns to rows after extracting them from the source.

    I also attached the sample of the source data and .brd file
    Attached Files Attached Files

  2. #2
    Contributor
    Join Date
    Dec 2011
    Location
    Nukualofa
    Posts
    9

    Default

    Here is what I already have but still got an error.

    node:Read_Raw_Internet_Extract
    bretype:core::Delimited File
    editor:Label=Read Raw Internet Extract
    editor:sortkey=4d2ce1fe3cac59ba
    input:4d2e7d6d04bb3fc1/out1=Directory_List.40fd2c747c2b1c0a
    output:@41e6c6cd11e613c6/=
    prop:FieldDelimiter=\n
    prop:FieldNames=Record
    prop:FilenameExpr=<<EOX
    FileName
    EOX
    prop:FilenameOutputField=filename
    prop:RecordDelimiter=\n
    editor:XY=260,220
    end:Read_Raw_Internet_Extract

    node:Filter_3
    bretype:core::Filter
    editor:sortkey=57267ac64a06175e_2
    input:@40fd2c74167f1ca2/=Read_Raw_Internet_Extract.41e6c6cd11e613c6
    output:@40fd2c7420761db6/=
    prop:Script=<<EOX


    # Get Fields: Date, Type, UId, CallingId, SessionSeconds, OctetIn,OctetsOut

    if Record.regexIsMatch("\\[[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}\\]") then
    {
    Date = Record.substr(1,10).date("CCYY-MM-DD")
    Time = Record.substr(12,8).time("HH:MM:SS")
    }
    else if Record.regexIsMatch("\\[\\]") then
    {
    Date = null
    Time = null
    }
    else if Record.split("=")[0].trim() == "Acc_Session_Id" then
    {
    Acc_Session_Id = Record.split("=")[1].trim()
    }
    else if Record.split("=")[0].trim() == "Frame_Protocol" then
    {
    Frame_Protocol = Record.split("=")[1].trim()
    }
    else if Record.split("=")[0].trim() == "Frame_IP_Address" then
    {
    Frame_IP_Address = Record.split("=")[1].trim()
    }
    else if Record.split("=")[0].trim() == "User_Id" then
    {
    User_Id = Record.split("=")[1].trim()
    }
    else if Record.split("=")[0].trim() == "Cisco_AVPair1" then
    {
    Cisco_AVPair1 = Record.split("=")[1].trim()
    }
    else if Record.split("=")[0].trim() == "Cisco_AVPair2" then
    {
    Cisco_AVPair2 = Record.split("=")[1].trim()
    }
    else if Record.split("=")[0].trim() == "Cisco_AVPair3" then
    {
    Cisco_AVPair3 = Record.split("=")[1].trim()
    }
    else if Record.split("=")[0].trim() == "Acct-SessionTime" then
    {
    Acct-SessionTime = Record.split("=")[1].trim().double()
    }
    else if Record.split("=")[0].trim() == "Acct-Input-Octets" then
    {
    Acct-Input-Octets = Record.split("=")[1].trim().str()
    }
    else if Record.split("=")[0].trim() == "Acct-Output-Octets" then
    {
    Acct-Output-Octets = Record.split("=")[1].trim().str()
    }
    else if Record.split("=")[0].trim() == "Acct-Input-Packets" then
    {
    Acct-Input-Packets = Record.split("=")[1].trim().str()
    }
    else if Record.split("=")[0].trim() == "Acct-Output-Packets" then
    {
    Acct-Output-Packets = Record.split("=")[1].trim().str()
    }
    else if Record.split("=")[0].trim() == "Acct-Authentic" then
    {
    Acct-Authentic = Record.split("=")[1].trim().str()
    }
    else if Record.split("=")[0].trim() == "Acct-Status-Type" then
    {
    Acct-Status-Type = Record.split("=")[1].trim().str()
    }
    else if Record.split("=")[0].trim() == "NAS-Port-Type" then
    {
    NAS-Port-Type = Record.split("=")[1].trim().str()
    }
    else if Record.split("=")[0].trim() == "NAS-Port" then
    {
    NAS-Port = Record.split("=")[1].trim().str()
    }
    else if Record.split("=")[0].trim() == "NAS-Port-Id" then
    {
    NAS-Port-Id = Record.split("=")[1].trim().str()
    }
    else if Record.split("=")[0].trim() == "Connect-Info" then
    {
    Connect-Info = Record.split("=")[1].trim().str()
    }
    else if Record.split("=")[0].trim() == "cisco_AVPair4" then
    {
    cisco_AVPair4 = Record.split("=")[1].trim()
    }
    else if Record.split("=")[0].trim() == "cisco_AVPair5" then
    {
    cisco_AVPair5 = Record.split("=")[1].trim()
    }
    else if Record.split("=")[0].trim() == "Service-Type" then
    {
    Service-Type = Record.split("=")[1].trim().str()
    }
    else if Record.split("=")[0].trim() == "NAS-IP-Address" then
    {
    NAS-IP-Address = Record.split("=")[1].trim().str()
    }
    else if Record.split("=")[0].trim() == "PMIP6-Home-HN-Prefix" then
    {
    PMIP6-Home-HN-Prefix = Record.split("=")[1].trim().str()
    }
    else if Record.regexIsMatch("\\[[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}\\]") then
    {
    Event_Date = Record.substr(1,10).date("CCYY-MM-DD")
    Event_Time = Record.substr(12,8).time("HH:MM:SS")
    }
    else if Record.split("=")[0].trim() == "NAS-Identifier" then
    {
    NAS-Identifier = Record.split("=")[1].trim().str()
    }
    else if Record.split("=")[0].trim() == "Acct-Delay-Time" then
    {
    Acct-Delay-Time = Record.split("=")[1].trim().str()
    }
    else if Record.split("=")[0].trim() == "Proxy-State" then
    {
    Proxy-State = Record.split("=")[1].trim().str()
    }
    else if Record.split("=")[0].trim() == "Acct-Input-Octets64" then
    {
    Acct-Input-Octets64 = Record.split("=")[1].trim().str()
    }
    else if Record.split("=")[0].trim() == "Acct-Output-Octets64" then
    {
    Acct-Output-Octets64 = Record.split("=")[1].trim().str()
    }
    else if Record.regexIsMatch("\\[[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}\\]") then
    {
    FreeRADIUS_Acct_Session_Start_Date = Record.substr(1,10).date("CCYY-MM-DD")
    FreeRADIUS_Acct_Session_Start_Time = Record.substr(12,8).time("HH:MM:SS")
    #FreeRADIUS-Acct-Session-Start-Time = Record.split("=")[1].trim().str()
    }
    else if Record.split("=")[0].trim() == "Tmp-String9" then
    {
    Tmp-String9 = Record.split("=")[1].trim().str()
    }
    else if Record.split("=")[0].trim() == "Acct_Unique-Session-Id" then
    {
    Acct_Unique-Session-Id = Record.split("=")[1].trim().str()
    }
    else if Record.split("=")[0].trim() == "Timestamp" then
    {
    Timestamp = Record.split("=")[1].trim().str()
    }


    emit Date, Time, Acc_Session_Id, Frame_Protocol, Frame_IP-Address, User_Id, Cisco_AVPair1, Cisco_AVPair2,
    Cisco_AVPair3,Acct-SessionTime,Acct-Input-Octets,Acct-Output-Octets,Acct-Input-Packets,Acct-Authentic,
    Acct-Status-Type,NAS-Port-Type,NAS-Port,NAS-Port-Id,Connect-Info,cisco_AVPair4,cisco_AVPair5,Service-Type,
    NAS-IP-Address,PMIP6-Home-HN-Prefix,Event_Date,Event_Time,NAS-Identifier,Acct-Delay-Time,Proxy-State,
    Acct-Input-Octets64,FreeRADIUS_Acct_Session_Start_Date,FreeRA DIUS_Acct_Session_Start_Time,Tmp-String9,
    Acct_Unique-Session-Id,Timestamp
    where execCount % 7 == 0


    EOX
    editor:XY=450,110
    end:Filter_3

  3. #3
    Lavastorm Employee
    Join Date
    Nov 2012
    Location
    Warrington, UK
    Posts
    244

    Default

    Here is a solution. There are multiple instances of the 'Cisco-AVPair' key in your data which would cause an issue as you can't have multiple fields with the same name. To overcome this the duplicate keys are de-duplicated by adding an incrementing suffix i.e. 'Cisco-AVPair_1', 'Cisco-AVPair_2', etc

    Rearrange_Columns_to_Rows--share.brg

    Note that the regex pattern for the datetime field is a little crude and could be improved if required.

  4. #4
    Contributor
    Join Date
    Dec 2011
    Location
    Nukualofa
    Posts
    9

    Default

    Appreciate your contribution @awilliams1024 but if the _key field arrange horizontal and all the values below that what I'm looking for.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •