- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I have JSON event data like this (it is shown as a collapsable tree structure in the event view):
{
"data": {
"192.168.1.1": {
"ip": "192.168.1.1",
"number": 0,
"list": [
{
"msg": "msg1",
"time": "2023-07-01T01:00:00",
},
{
"msg": "msg2",
"time": "2023-07-01T02:00:00",
},
{
"msg": "msg3",
"time": "2023-07-01T03:00:00",
}
]
},
"192.168.1.2": {
"ip": "192.168.1.2",
"number": 2,
"list": [
{
"msg": "msg1",
"time": "2023-07-02T01:00:00",
},
{
"msg": "msg2",
"time": "2023-07-02T02:00:00",
}
]
}
}
}
Please note:
- The key names under "data" are not known beforehand, but they are guaranteed to be IP addresses. That means they contain dots, which makes direct addressing such as "data.192.168.1.1.ip" difficult.
- The number of entries in "data.X.list" is variable.
- "data.X.number" is just any number, it does not contain the length of the list or so.
I want to flatten the structure into a tabular form, like this:
ip | number | msg | time |
"192.168.1.1" | 0 | "msg1" | "2023-07-01T01:00:00" |
"192.168.1.1" | 0 | "msg2" | "2023-07-01T02:00:00" |
"192.168.1.1" | 0 | "msg3" | "2023-07-01T03:00:00" |
"192.168.1.2" | 2 | "msg1" | "2023-07-02T01:00:00" |
"192.168.1.2" | 2 | "msg1" | "2023-07-02T02:00:00" |
My strategy so far was:
- In the raw data (_raw), replace all dots in IP addresses by underscores, to avoid the dot notation hassle
- Then use foreach to generate "iterator variables" for each ip entry
- Then iterate over all "iterator variables", use <<MATCHSTR>> as a placeholder for all spath operations within each IP address's sub-tree.
Something along the lines of
| rex field=_raw mode=sed "s/192.168.([0-9]{1,3}).([0-9]{1,3})/192_168_\1_\2/g"
| foreach data.*.ip [ eval iterator_<<MATCHSTR>>='<<FIELD>>']
| foreach iterator_* [
spath path=data.<<MATCHSTR>>.list{} output=<<MATCHSTR>>_json
| eval <<MATCHSTR>>_json=mvmap(<<MATCHSTR>>_json, <<MATCHSTR>>_json."##<<MATCHSTR>>")
| eval messages=mvappend(messages, <<MATCHSTR>>_json)
| fields - <<MATCHSTR>>_json ]
But the problems start with the fact that rex applied to _raw does not seem to have the desired effect. The closest I get are iterator variables still containing dotted IP addresses, such as "iterator_192.168.1.1". (This behaviour might be difficult to reproduce with makeresults sample data!)
What am I missing here?
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
![spavin spavin](https://community.splunk.com/legacyfs/online/avatars/544850.jpg)
Hi @rikinet.,
I've had a crack at this and I think I've found a working solution.
Splunk has a few JSON commands (see docs) that come in handy.
| makeresults | eval json="{\"data\":{\"192.168.1.1\":{\"ip\":\"192.168.1.1\",\"number\":0,\"list\":[{\"msg\":\"msg1\",\"time\":\"2023-07-01T01:00:00\"},{\"msg\":\"msg2\",\"time\":\"2023-07-01T02:00:00\"},{\"msg\":\"msg3\",\"time\":\"2023-07-01T03:00:00\"}]},\"192.168.1.2\":{\"ip\":\"192.168.1.2\",\"number\":2,\"list\":[{\"msg\":\"msg1\",\"time\":\"2023-07-02T01:00:00\"},{\"msg\":\"msg2\",\"time\":\"2023-07-02T02:00:00\"}]}}}"
| fromjson json
``` Above is just creating the test data ```
``` Get the "data" field from the JSON```
| eval ips = json_extract(json, "data")
| fields ips
``` Extract all the IPs - we'll use these later ```
| eval keys = json_keys(ips)
| eval keys= json_array_to_mv(keys)
| mvexpand keys
``` Now we have one IP address per row. Next we get each IPs specific data ```
| foreach keys [ | eval values = json_extract_exact(ips, <<FIELD>>)]
| fields keys, values
| fromjson values
| mvexpand list
| fromjson list
``` Now table it out```
| table ip, number, msg, time
That results in a table like this:
As per your notes:
- The key names are dynamically identified under "data". The fact that they have dots doesn't really matter for the json commands used.
- We mvxpand the "data.X.list" variable to make sure we get them all.
- "data.X.number" is just any number - it's just an extra field in the list.
Hopefully this query gets you closer to what you're looking for.
Cheers,
Daniel
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
![spavin spavin](https://community.splunk.com/legacyfs/online/avatars/544850.jpg)
Hi @rikinet.,
I've had a crack at this and I think I've found a working solution.
Splunk has a few JSON commands (see docs) that come in handy.
| makeresults | eval json="{\"data\":{\"192.168.1.1\":{\"ip\":\"192.168.1.1\",\"number\":0,\"list\":[{\"msg\":\"msg1\",\"time\":\"2023-07-01T01:00:00\"},{\"msg\":\"msg2\",\"time\":\"2023-07-01T02:00:00\"},{\"msg\":\"msg3\",\"time\":\"2023-07-01T03:00:00\"}]},\"192.168.1.2\":{\"ip\":\"192.168.1.2\",\"number\":2,\"list\":[{\"msg\":\"msg1\",\"time\":\"2023-07-02T01:00:00\"},{\"msg\":\"msg2\",\"time\":\"2023-07-02T02:00:00\"}]}}}"
| fromjson json
``` Above is just creating the test data ```
``` Get the "data" field from the JSON```
| eval ips = json_extract(json, "data")
| fields ips
``` Extract all the IPs - we'll use these later ```
| eval keys = json_keys(ips)
| eval keys= json_array_to_mv(keys)
| mvexpand keys
``` Now we have one IP address per row. Next we get each IPs specific data ```
| foreach keys [ | eval values = json_extract_exact(ips, <<FIELD>>)]
| fields keys, values
| fromjson values
| mvexpand list
| fromjson list
``` Now table it out```
| table ip, number, msg, time
That results in a table like this:
As per your notes:
- The key names are dynamically identified under "data". The fact that they have dots doesn't really matter for the json commands used.
- We mvxpand the "data.X.list" variable to make sure we get them all.
- "data.X.number" is just any number - it's just an extra field in the list.
Hopefully this query gets you closer to what you're looking for.
Cheers,
Daniel
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @spavin , thank you very much - that works! Great help!
Just a small addition: I think we can even skip the 'foreach' command and simply write
| eval values = json_extract_exact(ips, keys)
...because at this point after the 'mvexpand', 'keys' only contains a single key.
- 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)
Well spotted!
I had a couple of variations, and left this in but you're right, the foreach isn't needed here.
![](/skins/images/FE4825B2128CA5F641629E007E333890/responsive_peak/images/icon_anonymous_message.png)