We recommend switching to the latest versions of Edge, Firefox, Chrome or Safari. Using Internet Explorer will result in a loss of website functionality.
Our Support systems migrated on Saturday, May 21. We'll automatically forward you to the new location for this content.

Rearrange raw Data set from columns to rows

Comments

3 comments

  • Avatar
    Infogix Support
    Originally posted by: tfainga

    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
    0
    Comment actions Permalink
  • Avatar
    Infogix Support
    Originally posted by: awilliams1024

    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.
    0
    Comment actions Permalink
  • Avatar
    Infogix Support
    Originally posted by: tfainga

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

Please sign in to leave a comment.