Splitting an event into multiple events to conform to a data model

[thierry]

I have events already in an index looking like this:

{

   "location": "Paris",

   "temperature": 25,

   "humidity": 57

}

I have a data model looking like this:

{

   "location": string

   "name": string

  "value": number

}

and so I would need my event to show up as two events under this data model:

{

   "location":"Paris"

   "name": "temperature"

  "value": 25

}

and

{

   "location":"Paris"

   "name": "humidity"

  "value": 57

}

 What would be the best way to proceed? I have had no luck with field manipulation / tables so far.

[bowesmana · Accepted solution]

Aside from the data manipulation, which is the easy part and there are answers here, @PickleRick points out you can't get the data into the DM in this way, so if you have to get already indexed data into the DM, then you will probably need to manipulate the data as shown in the existing answers, but collect the data to a new index and then run the DM off that index rather than the primary index.

[thierry]

Yes sounds like I will need to proceed with a secondary index then. The key here is that I need to conform to this data model, not just build a search outputting data in this format. Thanks everybody for all these useful answers!

[PrewinThomas]

@thierry 

Is your field structure is fixed and small? Then we can make it simpler like below, else better to go with foreach.

| <your_search>
| eval name=mvappend("temperature", "humidity")
| eval value=mvappend(temperature, humidity)
| fields location name value
| mvexpand name
| mvexpand value
| eval value=tonumber(value)
| table location name value

Regards,
Prewin
Splunk Enthusiast | Always happy to help! If this answer helped you, please consider marking it as the solution or giving a Karma. Thanks!

[bowesmana]

@PrewinThomas you can't double mvexpand a result set where on two MV fields as you end up with 4 events

[livehybrid]

Hi @thierry 

How about this? Ultimately I think you need to get it into a multival field so you can expand into indiviaual events:

| foreach temperature humidity 
    [ eval contents=mvappend(contents,json_set("{}","name","<<FIELD>>","value", <<FIELD>>))
        ]
        |mvexpand contents
        | eval contents=json_set(contents,"location",location)
        | eval _raw=contents

🌟 Did this answer help you? If so, please consider:

• Adding karma to show it was useful
• Marking it as the solution if it resolved your issue
• Commenting if you need any clarification

Your feedback encourages the volunteers in this community to continue contributing

[PickleRick]

While you can go even more generic - 

| foreach * 
      [ | eval a=mvappend(a,if("<<FIELD>>"=="EventID",null(),json_object("location",location,"name","<<FIELD>>","value",'<<FIELD>>'))) ]
| fields - _raw
| mvexpand a
| fields a
| spath input=a
| fields - a

(Works but throws some exception about templatized search for a field; would have to investigate it deeper).

But it won't do in context of the datamodel. Datamodel constraints must be a single non-piped search.

[bowesmana]

@PickleRick - off topic from OP's original posting, but in response to the error:

That templatized field error I believe is due to how mvappend works - it does not like null() but null is ok, i.e. this is fine

| foreach * 
      [ | eval a=mvappend(a, if("<<FIELD>>"="location", null, json_object("location",'location',"name","<<FIELD>>","value",'<<FIELD>>'))) ]

and null() is fine if you take mvappend out of the equation, i.e.

      [ | eval a=if("<<FIELD>>"="location", null(), json_object("location",'location',"name","<<FIELD>>","value",'<<FIELD>>')) ]

See that this fails

| makeresults 
| eval a="a"
| eval a=mvappend(a, null())
| eval c=mvcount(a)

but null on its own works.

[PickleRick]

Hmm... Right. Which is surprising because null here is treated as a field name which simply turns out to be empty. If you assign a value to it, it will of course be used.

And of course you can't drop mvappend because you want to cover all fields.

So the way around is to not assign null() in this case but an empty string. Apparently it's ignored with multivalued fields.

[bowesmana]

Empty strings are counted

| makeresults 
| eval a="a", b="b", d=""
| eval a=mvappend(a, "", b, d)
| eval c=mvcount(a)

and the foreach would result in an empty row

| foreach * 
      [ | eval a=mvappend(a, if("<<FIELD>>"="location", "", json_object("location",'location',"name","<<FIELD>>","value",'<<FIELD>>'))) ]

and I'd always made the assumption that using null was because mvappend was a bit strange, but you're right it is just another field - have to go check some old searches 🙂

[PickleRick]

Indeed, you're right.

| makeresults 
| eval a=mvappend("","")
| eval c=mvcount(a)

And we have c=2. 

That's funny. One should really deliberately choose some non-existant field.