Splunk Search

Extract json key as a column value for TOP command.

jasneet
New Member

I have a JSONs which have the following structure:

{
  "fieldA": "valueA",
  "fieldB": "valueB",
  "fieldC": "valueC",
  "fieldD": {
    "keyA": 1,
    "keyB": 1,
  }
}

And, I am executing a TOP command as follows:

sourcetype=MySource | top limit=30000 "fieldA" "fieldB" by "fieldC"

This command will give me an output as follows:

+--------+--------+--------+--------+-----------+
| fieldC | fieldA | fieldB | count | percent |
+--------+--------+--------+--------+-----------+
| valueC | valueA | valueB | 1 | 100.00 |
+--------+--------+--------+--------+-----------+

How can I get the key of fieldD as the value of another column so that I can produce a table as follows:

+--------+--------+--------+--------+--------+-----------+
| fieldC | fieldA | fieldB | fieldD | count | percent |
+--------+--------+--------+--------+--------+-----------+
| valueC | valueA | valueB | keyA;keyB | 1 | 100.00 |
+--------+--------+--------+--------+--------+-----------+

0 Karma

maciep
Champion

I'm not exactly sure how things are parsed out for you already, but I think you can get at this with rex...but might not be best solution.

[your base search]
|  rex "\"fieldD\":{(?<my_field_d>[^}]+)"
| rex max_match=0 field=my_field_d "\"(?<my_field_d>[^\"]+)\":"
| eval my_field_d = mvjoin(my_field_d,";")

So essentially, use rex to put all of fieldD in a field, then use rex again to grab all of the keys from there, which will create a multi-value field. So then use mvjoin to put them into a single value. So now each event would have a field of those keys concatenated together with semi-colons.

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

Do this and tell us what you see ...

sourcetype=MySource  | head 10

Specifically, is fieldD already present, and if so what does its value look like? is keyA present, is keyB present, and are keyA and keyB the same for all records, or are the key field names variable for each record?

my expectation is that you would do

sourcetype=MySource | top limit=30000 "fieldA" "fieldB" "fieldD" by "fieldC"

and then do an spath on fieldD to extract the fields... and maybe muck around with untable and xyseries to put them together again, if the key field names are what you want rather than the key field values.....

0 Karma

jasneet
New Member

Thanks for your comment. fieldD is always present but the number of key-value pairs in fieldD can very with each record. The keys in fieldD are not fixed variable names so in each batch there can be thousands of different keys.

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...