I'm currently working on a Splunk query and need some assistance with converting nested JSON data into a specific string format. Below is an example of the JSON data I'm dealing with:

{
  "info1": "val1",
  "info2": "val2",
  "UsagePerUser": {
    "user1": 0.1,
    "user2": 0.2
  }
}

I want to convert the UsagePerUser field into a string with the following format:

Current Output:

info1  info2  UsagePerUser
===========================
val1   val2   {"user1":0.1,"user2":0.2}
---------------------------
val3   val4   {"user3":0.3,"user4":0.4}

Desired Output:

info1  info2  UsagePerUser
===========================
val1   val2   user1    10.0
              user2    20.0
---------------------------
val3   val4   user3    30.0
              user4    40.0

Here's the Splunk command I'm currently using:

index="my_index" source="my_source"
| sort - _time
| dedup Path
| spath output=UsagePerUser path=UsagePerUser
| eval UsagePerUser = json(UsagePerUser)
| table info1, info2, UsagePerUser

This command only extracts the UsagePerUser sub-dictionary, but I need to transform it into the desired string format. Any suggestions or guidance on how to modify this command to achieve the desired output would be greatly appreciated

I'm currently working on a Splunk query and need some assistance with converting nested JSON data into a specific string format. Below is an example of the JSON data I'm dealing with:

{
  "info1": "val1",
  "info2": "val2",
  "UsagePerUser": {
    "user1": 0.1,
    "user2": 0.2
  }
}

I want to convert the UsagePerUser field into a string with the following format:

Current Output:

info1  info2  UsagePerUser
===========================
val1   val2   {"user1":0.1,"user2":0.2}
---------------------------
val3   val4   {"user3":0.3,"user4":0.4}

Desired Output:

info1  info2  UsagePerUser
===========================
val1   val2   user1    10.0
              user2    20.0
---------------------------
val3   val4   user3    30.0
              user4    40.0

Here's the Splunk command I'm currently using:

index="my_index" source="my_source"
| sort - _time
| dedup Path
| spath output=UsagePerUser path=UsagePerUser
| eval UsagePerUser = json(UsagePerUser)
| table info1, info2, UsagePerUser

This command only extracts the UsagePerUser sub-dictionary, but I need to transform it into the desired string format. Any suggestions or guidance on how to modify this command to achieve the desired output would be greatly appreciated

• splunk
• splunk-query

1 Answer 1

Try this run-anywhere SPL; find the explanation in the code:

| makeresults ```start mock data```
    format=json 
    data="
        [
{
  \"info1\": \"val1\",
  \"info2\": \"val2\",
  \"UsagePerUser\": {
    \"user1\": 0.1,
    \"user2\": 0.2
  }
},
{
  \"info1\": \"val1\",
  \"info2\": \"val2\",
  \"UsagePerUser\": {
    \"user3\": 0.3,
    \"user4\": 0.4
  }
}
        ]
    "
```start your query```
| spath output=UsagePerUser path=UsagePerUser
| eval UsagePerUser = json(UsagePerUser)
| table info1, info2, UsagePerUser
```end your query```
```extract usernames (un) and usage from json into multivalue fields```
| rex field=UsagePerUser max_match=100 "\"(?P<un>[^\"]+)\":(?P<usage>[\d.]+)"
| eval 
````fix usage magnitude```
    usage=mvmap(usage,usage*100),
```zip together usernaem and usage```
    UsagePerUser=mvzip(un,usage," ")
| fields - un usage