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!

Understanding Generative AI Techniques and Their Application in Cybersecurity

Watch On-Demand Artificial intelligence is the talk of the town nowadays, with industries of all kinds ...

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Using the Splunk Threat Research Team’s Latest Security Content

REGISTER HERE Tech Talk | Security Edition Did you know the Splunk Threat Research Team regularly releases ...