Splunk Search

merge events by common fields and rename other fields

clamarkv
Explorer

Hi,

Im trying to figure out how to merge these events

[{"event_type":"Metric","jobid":"1d622e4f-6a78-404a-9c40-d1eaf9d78005","name":"RemainingDisk","value":"223"},
{"event_type":"Metric","jobid":"1d622e4f-6a78-404a-9c40-d1eaf9d78005","name":"RemainingMem","value":"102"},
{"event_type":"Metric","jobid":"1346f0d4-fe9a-4d58-8934-131d87ac0277","name":"RemainingDisk","value":"343"},
{"event_type":"Metric","jobid":"1346f0d4-fe9a-4d58-8934-131d87ac0277","name":"RemainingMem","value":"744"}]

into something like this

[{"event_type":"Metric","jobid":"1d622e4f-6a78-404a-9c40-d1eaf9d78005","RemainingDisk":"223","RemainingMem":"102"},
{"event_type":"Metric","jobid":"1346f0d4-fe9a-4d58-8934-131d87ac0277","RemainingDisk":"343","RemainingMem":"744"}]

i just cant figure out how to remap the name/value fields.

ive had a seach already but have not been able to find an existing example of this (although im sure i cant be the first person doing this).

Thanks

0 Karma
1 Solution

Sukisen1981
Champion

hi @clamarkv
something like this:

| stats list(name),list(value) by jobid
| rename list(name) as name,list(value) as value
| eval x=mvzip(name,value,":")
| fields jobid,x
| nomv x
| rex field=x "\s+(?<field1>.*)"| rex field=x "(?<field2>.*?)\s+"
| eval combo="event_type:Metric" +"," +"jobid:"+jobid+","
+field1+","+field2 | fields combo

I have not added the complete json like format because I am too lazy :). First, check if the combo field represents what you want more or less, we can then add quotes with "\"" which is just more laborious than anything else.
But, firstly check if the combo field gives you the correct data

View solution in original post

clamarkv
Explorer

thanks @Sukisen1981, i expanded a little bit and came up with the following which gives me exactly what i need.

 index=pcf foundation=$foundation_token$ sourcetype=cf:valuemetric job_index="*" name=Capacity*
  | stats list(name),list(value) by _time,job_index
  | rename list(name) as name,list(value) as value
  | eval x=mvzip(name,value,":")
  | fields job_index,x
  | nomv x
  | rex field=x "CapacityTotalDisk:(?<CapacityTotalDisk>.*)\s+"
  | rex field=x "CapacityRemainingDisk:(?<CapacityRemainingDisk>.*)\s+"
  | rex field=x "CapacityTotalMemory:(?<CapacityTotalMemory>.*?)\s+"
  | rex field=x "CapacityAllocatedMemory:(?<CapacityAllocatedMemory>.*?)\s+"
  | rex field=x "CapacityRemainingMemory:(?<CapacityRemainingMemory>.*?)\s+"
  | rex field=x "CapacityTotalContainers:(?<CapacityTotalContainers>.*?)\s+"
  | rex field=x "CapacityRemainingContainers:(?<CapacityRemainingContainers>.*?)\s+"
  | eval CapacityAllocatedContainers=(CapacityTotalContainers-CapacityRemainingContainers)
  | fields _time,job_index,CapacityTotalMemory,CapacityAllocatedMemory,CapacityRemainingMemory,CapacityTotalDisk,CapacityRemainingDisk,CapacityTotalContainers,CapacityAllocatedContainers,CapacityRemainingContainers
  | stats avg(CapacityTotalMemory) as totalMemory,
          avg(CapacityAllocatedMemory) as allocatedMemory,
          avg(CapacityRemainingMemory) as remainingMemory,
          sparkline(avg(CapacityRemainingMemory)) as "remainingMemory trend",
          avg(CapacityTotalDisk) as totalDisk,
          avg(CapacityRemainingDisk) as remainingDisk,
          sparkline(avg(CapacityRemainingDisk)) as "remainingDisk trend",
          avg(CapacityTotalContainers) as totalContainers,
          avg(CapacityAllocatedContainers) as allocatedContainers,
          avg(CapacityRemainingContainers) as remainingContainers,
          sparkline(avg(CapacityRemainingContainers)) as "remainingContainers trend" by job_index
  | eval allocatedMemory=round(allocatedMemory,2),
         remainingMemory=round(remainingMemory,2),
         remainingDisk=round(remainingDisk,2),
         remainingContainers=round(remainingContainers,0),
         allocatedContainers=round(allocatedContainers,0)
  | sort +remainingMemory
0 Karma

Sukisen1981
Champion

hi @clamarkv
something like this:

| stats list(name),list(value) by jobid
| rename list(name) as name,list(value) as value
| eval x=mvzip(name,value,":")
| fields jobid,x
| nomv x
| rex field=x "\s+(?<field1>.*)"| rex field=x "(?<field2>.*?)\s+"
| eval combo="event_type:Metric" +"," +"jobid:"+jobid+","
+field1+","+field2 | fields combo

I have not added the complete json like format because I am too lazy :). First, check if the combo field represents what you want more or less, we can then add quotes with "\"" which is just more laborious than anything else.
But, firstly check if the combo field gives you the correct data

View solution in original post

Sukisen1981
Champion

hi @clamark

Did you try out the above?

0 Karma
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!