Hello guys and girls,
I encountered a situation where i need to extract data from two log types that have just 3 common field names and lots of uncommon ones, but all in a table output.
So
Log1:
Name=Name1
Process=Process1
Hash=Hash1
Uncommon1=Value1
Uncommon2=Value2
Log2:
Name=Name2
Process=Process2
Hash=Hash2
Uncommon3=Value3
Uncommon4=Value4
Uncommon5=Value5
The desired output would look like:
Name Process Hash Attributes
Name1 Process1 Hash1 Uncommon1=Value1
Uncommon2=Value2
Name2 Process2 Hash2 Uncommon3=Value3
Uncommon4=Value4
Uncommon5=Value5
I tried multiple combinations using table and fields but i couldn't figure out how to group the uncommon fields and their values in a single column.
Thank you for the help.
@claudiuu, okie since we don't have any methods to identify between first few fields from others, give this a try
your search |stats values(*) as * ,latest(_time) as _time by event_simpleName,FileName,CommandLine,UserName
|eval args=""
|foreach * [eval args=if("<<FIELD>>"!="event_simpleName" AND "<<FIELD>>"!="FileName" AND "<<FIELD>>"!="CommandLine" AND "<<FIELD>>"!="UserName",mvappend(args,"<<FIELD>>=".<<FIELD>>),args)]
|table _time,event_simpleName,FileName,CommandLine,UserName,args
@claudiuu, okie since we don't have any methods to identify between first few fields from others, give this a try
your search |stats values(*) as * ,latest(_time) as _time by event_simpleName,FileName,CommandLine,UserName
|eval args=""
|foreach * [eval args=if("<<FIELD>>"!="event_simpleName" AND "<<FIELD>>"!="FileName" AND "<<FIELD>>"!="CommandLine" AND "<<FIELD>>"!="UserName",mvappend(args,"<<FIELD>>=".<<FIELD>>),args)]
|table _time,event_simpleName,FileName,CommandLine,UserName,args
WOW, this is perfect.
Thank you Nair!
@claudiuu, glad that worked. Please accept as answer 🙂
@claudiuu, are these uncommon values have a pattern -like starting with a particular word? If not, how is your sample event look like? Are these delimited fields or extracted? It would be helpful to see a sample event.
Hello Nair,
The fields are extracted for each event type. For each event type, they have a similar field name with different values. Two event examples would be:
EVENT 1
Agent IP:
ComputerName:
ConfigBuild: 1007.3.0007702.1
ConfigStateHash_decimal: 2693441101
ConnectionDirection_decimal: 0
ConnectionFlags_decimal: 0
ContextProcessId_decimal: 1902826736335
ContextThreadId_decimal: 3976186904410882
ContextTimeStamp_decimal: 1539660458.789
EffectiveTransmissionClass_decimal: 3
Entitlements_decimal: 15
InContext_decimal: 0
LPort: 49584
LocalAddressIP4: 10.110.126.246
LocalIP: 10.110.126.246
LocalPort_decimal: 49584
MAC:
ProductType: 1
Protocol_decimal: 6
RPort: 60845
RemoteAddressIP4: 10.244.76.154
RemoteIP: 10.244.76.154
RemotePort_decimal: 60845
aid: 9b1868e751c84f4272fa22110764f060
aip: 185.89.151.81
cid: 3d156917ad3b4b3a9d1c6fe67e95db4b
company:
eid: 319
esize: 131
event_err: false
event_platform: Win
event_simpleName: NetworkConnectIP4
event_version: 5
eventtype: eam
host: localhost:
id: 4db95d20-d0f3-11e8-a0e9-020f46cbb5d4
index: main
name: NetworkConnectIP4V5
source: main
sourcetype: NetworkConnectIP4V5-v02
tid: 2572288
timestamp: 1539660403442
EVENT 2
Agent IP:
ComputerName:
ConfigBuild: 1007.3.0007702.1
ConfigStateHash_decimal: 2693441101
ContextProcessId_decimal: 1902826736335
ContextThreadId_decimal: 3981387462596668
ContextTimeStamp_decimal: 1539660398.845
DnsRequestCount_decimal: 1
DomainName:
DualRequest_decimal: 0
EffectiveTransmissionClass_decimal: 3
Entitlements_decimal: 15
InterfaceIndex_decimal: 0
LocalAddressIP4: 172.17.9.182
MAC:
ProductType: 1
RequestType_decimal: 1
aid: 9b1868e751c84f4272fa22110764f060
aip: 185.89.151.81
cid: 3d156917ad3b4b3a9d1c6fe67e95db4b
company:
eid: 382
esize: 125
event_err: false
event_platform: Win
event_simpleName: DnsRequest
event_version: 3
host: localhost:
id: 4db95e6b-d0f3-11e8-a0e9-020f46cbb5d4
index: main
name: DnsRequestV3
source: main
sourcetype: DnsRequestV3-v02
tid: 2572544
timestamp: 1539660403442
My initial query would look like:
ContextProcessId_decimal:1902826736335| table _time event_simpleName FileName CommandLine UserName DomainName SourceFileName TargetFileName RemoteAddressIP4 RemotePort_decimal LocalPort_decimal CommandLineParameters RegObjectName RegStringValue RegValueName ServiceDescription ServiceDisplayName ServiceImagePath ServiceStart_decimal ImageFileName InjectedDll SourceThreadModule TaskName TaskExecCommand TaskExecArguments | sort + _time
I would like that the output table to contain the columns:
_time
event_simpleName
FileName
CommandLine
UserName
and a last column named "Attributes" that would contain only the existing field names and their value of the rest of the fields enumerated in the query:
DomainName SourceFileName TargetFileName RemoteAddressIP4 RemotePort_decimal LocalPort_decimal CommandLineParameters RegObjectName RegStringValue RegValueName ServiceDescription ServiceDisplayName ServiceImagePath ServiceStart_decimal ImageFileName InjectedDll SourceThreadModule TaskName TaskExecCommand TaskExecArguments