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

Sukisen1981
Champion

hi @clamark

Did you try out the above?

0 Karma
Get Updates on the Splunk Community!

What’s new on Splunk Lantern in August

This month’s Splunk Lantern update gives you the low-down on all of the articles we’ve published over the past ...

Welcome to the Future of Data Search & Exploration

You have more data coming at you than ever before. Over the next five years, the total amount of digital data ...

This Week's Community Digest - Splunk Community Happenings [8.3.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...