Rearrange raw Data set from columns to rows
Originally posted by: tfainga
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
Attachments:
Sample.zip
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
Attachments:
Sample.zip
-
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 -
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.
Please sign in to leave a comment.
Comments
3 comments