Splunk Search

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

gcescatto
New Member

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
  }
]

alt text

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

alt text

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

Tags (3)
0 Karma
1 Solution

harsmarvania57
Ultra Champion

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 *

View solution in original post

0 Karma

harsmarvania57
Ultra Champion

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 *
0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...