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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...