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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...