topic Re: breaking multiple mv fields into single events based on array index in Splunk Search

breaking multiple mv fields into single events based on array index

pmdba — Wed, 29 Jan 2025 23:16:43 GMT

I have data that looks something like this, coming in as JSON:

time, application, feature, username, hostname

The problem is that username and hostname are nested arrays, like this:

{ application: app1 feature: feature1 timestamp: 01/29/2025 23:02:00 +0000 users: [ { userhost: client1 username: user1 } { userhost: client2 username: user2 } ] }

and when the event shows up in splunk, userhost and username are converted to multi-value fields.

_time	application	feature	users{}.username	users{}.userhost
01/29/2025 23:02:00	app1	feature1	user1 user2	client1 client2

I need an SPL method to convert these into individual events for the purposes of a search, so that I can perform ldap lookups on each hostname. mvexpand only works on one field at a time and doesn't recognize users or users{} as valid input, which loses the relationship between user1:client1 and user2:client2. How can I convert both arrays to individual events by array index, so that I preserve the relationship between username and hostname, like this:

_time	application	feature	users{}.username	users{}.userhost
01/29/2025 23:02:00	app1	feature1	user1	client1
01/29/2025 23:02:00	app1	feature1	user2	client2

Re: breaking multiple mv fields into single events based on array index

bowesmana — Thu, 30 Jan 2025 02:58:48 GMT

If you want to do this at search time, create a composite field and expand that, e.g.

| eval composite_field=mvzip('users{}.userhost', 'users{}.username', "###") | fields - users{}* | mvexpand composite_field | rex field=composite_field "(?<userhost>.*)###(?<username>.*)" | fields - composite_field

it will only zip correctly if there are exactly equal elements in each of the MV fields.

Re: breaking multiple mv fields into single events based on array index

yuanliu — Thu, 30 Jan 2025 03:38:48 GMT

First, when illustrating structured data, please post compliant raw text. In your case, a compliant JSON should be

{ "application": "app1", "feature": "feature1", "timestamp": "01/29/2025 23:02:00 +0000", "users": [ { "userhost": "client1", "username": "user1" }, { "userhost": "client2", "username": "user2" } ] }

The trick here is to reach into the JSON array to perform mvexpand and ignore Splunk's default flattening of array.

| spath path=users{} | mvexpand users{} | spath input=users{}

Your sample data will give

application	feature	timestamp	userhost	username	users{}
app1	feature1	01/29/2025 23:02:00 +0000	client1	user1	{ "userhost": "client1", "username": "user1" }
app1	feature1	01/29/2025 23:02:00 +0000	client2	user2	{ "userhost": "client2", "username": "user2" }

Here is an emulation for you to play with and compare with real data

| makeresults | eval _raw = "{ \"application\": \"app1\", \"feature\": \"feature1\", \"timestamp\": \"01/29/2025 23:02:00 +0000\", \"users\": [ { \"userhost\": \"client1\", \"username\": \"user1\" }, { \"userhost\": \"client2\", \"username\": \"user2\" } ] }" | spath ``` data emulation above ```