Can you help me use the rex command to parse API's JSON?

[gcescatto]

I have the following JSON, but I'm not really familiar with Splunk's rex function.
I tried this command without success: | rex "(?{[^}]+})" | mvexpand json_field | spath input=json_field

[
  {
    "Eqpt Class Description": null,
    "Eqpt Criticality": "D",
    "Eqpt Criticality Desc": "Non Essential",
    "Eqpt Description": null,
    "Eqpt Type Description": "Instrumentation",
    "Maint Plant Caption": null,
    "Maint Plant Filter Code": null,
    "Maint Plant ID": null
  },
  {
    "Eqpt Class Description": null,
    "Eqpt Criticality": "D",
    "Eqpt Criticality Desc": "Non Essential",
    "Eqpt Description": null,
    "Eqpt Type Description": "Instrumentation",
    "Maint Plant Caption": null,
    "Maint Plant Filter Code": null,
    "Maint Plant ID": null
  },
  {
    "Eqpt Class Description": null,
    "Eqpt Criticality": "D",
    "Eqpt Criticality Desc": "Non Essential",
    "Eqpt Description": null,
    "Eqpt Type Description": "Instrumentation",
    "Maint Plant Caption": null,
    "Maint Plant Filter Code": null,
    "Maint Plant ID": null
  }
]

For me to be able to build a dashboard with it, I need that to be displayed similar as in:

Could someone please help me to parse this on "}, {"?

[harsmarvania57 · Accepted solution]

Hi @gcescatto,

There are 2 ways to achieve this. The first is to ingest JSON data with correct extractions before data indexing and another way is to achieve this via search query.

• To achieve this with correct field extractions before data indexing.

Here you can assign below configuration on a universal forwarder and then restart Splunk on universal forwarder.

props.conf

[yoursourcetype]
INDEXED_EXTRACTIONS = JSON

• If you do not want to change any configuration on universal forwarder, and you don't have too much data, then you can achieve it via search query. (Why I am saying "not too much data" because search query will generate extra load to do parsing at search time if you have more data rather; I prefer correct field extraction before data indexing)

Below is search query to parse correct data

<yourBaseSearch> | spath input=_raw | rename {}.* AS *

Above query will extract many fields like Eqpt Class Description , Eqpt Criticality etc.

Here is a run anywhere search to test on any Splunk instance.

| makeresults
| eval _raw="[
   {
     \"Eqpt Class Description\": null,
     \"Eqpt Criticality\": \"D\",
     \"Eqpt Criticality Desc\": \"Non Essential\",
     \"Eqpt Description\": null,
     \"Eqpt Type Description\": \"Instrumentation\",
     \"Maint Plant Caption\": null,
     \"Maint Plant Filter Code\": null,
     \"Maint Plant ID\": null
   },
   {
     \"Eqpt Class Description\": null,
     \"Eqpt Criticality\": \"D\",
     \"Eqpt Criticality Desc\": \"Non Essential\",
     \"Eqpt Description\": null,
     \"Eqpt Type Description\": \"Instrumentation\",
     \"Maint Plant Caption\": null,
     \"Maint Plant Filter Code\": null,
     \"Maint Plant ID\": null
   },
   {
     \"Eqpt Class Description\": null,
     \"Eqpt Criticality\": \"D\",
     \"Eqpt Criticality Desc\": \"Non Essential\",
     \"Eqpt Description\": null,
     \"Eqpt Type Description\": \"Instrumentation\",
     \"Maint Plant Caption\": null,
     \"Maint Plant Filter Code\": null,
     \"Maint Plant ID\": null
   }
 ]" 
 | spath input=_raw
 | rename {}.* AS *