Extract json key as a column value for TOP command.

[jasneet]

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 |
+--------+--------+--------+--------+--------+-----------+

[maciep]

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.

[DalJeanis]

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.....

[jasneet]

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.