- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
breaking multiple mv fields into single events based on array index
![pmdba pmdba](https://community.splunk.com/legacyfs/online/avatars/179924.jpg)
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 |
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
![SplunkTrust SplunkTrust](/html/@E48BE65924041B382F8C3220FF058B38/rank_icons/splunk-trust-16.png)
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 ```
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
![SplunkTrust SplunkTrust](/html/@E48BE65924041B382F8C3220FF058B38/rank_icons/splunk-trust-16.png)
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.
![](/skins/images/53C7C94B4DD15F7CACC6D77B9B4D55BF/responsive_peak/images/icon_anonymous_message.png)