CODEWITHSUNDEEP
splunksplunk-query
mahi
mahi

Reputation: 532

Splunk search query to create a table from JSON search result

I am relatively new to Splunk search and I am trying to build a table from my splunk search results.

Can someone please help me to build a table using following JSON

My search results as follows

{ [-]
   docker: { [+]
   }
   kubernetes: { [+]
   }
   log: LOGGER {"name":"some text here","pathname":"/some/path","timestamp":"2023-05-03T20:35:06Z","action":"pageview","payload":{"category":"cloths","country":"US","appEnv":"production"},"uID":"0023493543"}
   stream: stdout
}

raw text:

{
  "stream": "stdout",
  "log": "LOGGER {\"name\":\"Some text here\",\"pathname\":\"/some/path\",\"timestamp\":\"2023-05-04T10:44:05Z\",\"action\":\"pageview\",\"payload\":{\"category\":\"cloths\",\"country\":\"US\",\"appEnv\":\"production\"},\"uID\":\"0023493543\"}",
  "docker": { "container_id": "xxxxxxxxxxxx" },
  "kubernetes": {
    "container_name": "xxxxxx",
    .....
    },
    "labels": {
      .....
    },
    "namespace_id": "xxxx-xxx-xxx-xxx",
    "namespace_labels": {
      "application-id": "48928423",
      "namespace": "849328932-243232xxxx",
        ........    }
  }
}

From this I would like draw the table as

| uID | pathname | category | eventName | country | timestamp |
| 0023493543   | /some/path   | cloths | some text here | US |
| ----   | ----   | ----   | ----   | ----   | ----   |

I have tried building table using spath, eval, extract commands but none of tries gives me any desired result. If its a plain JSON object in log field, I managed to build a query for few selected fields, but as its a text String and JSON in it, I am not sure, how to extract my fields.

I am expecting a table as shown above, later I can modify query for my complex result.

Update:

I tried following query,

BASE SEARCH | spath path=log | rex field=_raw max_match=0 "name\W+(?<name>[^\"]+)" | rex field=_raw max_match=0 "pathname\W+(?<pathname>[^\"]+)" | rex field=_raw max_match=0 "timestamp\W+(?<timestamp>[^\"]+)" | rex field=_raw max_match=0 "category\W+(?<category>[^\"]+)" | rex field=_raw max_match=0 "country\W+(?<country>[^\"]+)" | rex field=_raw max_match=0 "uID\W+(?<uID>\w+)" | table uID, pathname, category, name, country, timestamp

which gives me desired result, except name field. It gives me additional text as

some text here\
some/path
....

but I need only some text here

Upvotes: 0

Views: 528

Answers (1)

RichG
RichG

Reputation: 9926

Splunk's JSON parser doesn't like the sample data so the spath command can't be used to extract the desired fields. It can be used to extract the log field then the extract command can break out the remaining fields.

| makeresults | eval _raw="{
  \"stream\": \"stdout\",
  \"log\": \"LOGGER {\\\"name\\\":\\\"Some text here\\\",\\\"pathname\\\":\\\"/some/path\\\",\\\"timestamp\\\":\\\"2023-05-04T10:44:05Z\\\",\\\"action\\\":\\\"pageview\\\",\\\"payload\\\":{\\\"category\\\":\\\"cloths\\\",\\\"country\\\":\\\"US\\\",\\\"appEnv\\\":\\\"production\\\"},\\\"uID\\\":\\\"0023493543\\\"}\",
  \"docker\": { \"container_id\": \"xxxxxxxxxxxx\" },
  \"kubernetes\": {
    \"container_name\": \"xxxxxx\"
    },
    \"labels\": {
      \"foo\": \"bar\"
    },
    \"namespace_id\": \"xxxx-xxx-xxx-xxx\",
    \"namespace_labels\": {
      \"application-id\": \"48928423\",
      \"namespace\": \"849328932-243232xxxx\"
    }
  }
}"
``` Above created test data.  Delete IRL ```
``` Parse the JSON as much as we can ```
| spath
``` Replace _raw with log for field extraction ```
| eval _raw=log
``` Extract fields from log ```
| extract pairdelim=",}" kvdelim=":"
| table uID pathname category eventName country timestamp

Note, however, the desired category and eventName fields are not present in the sample event so they don't display in the output.

Upvotes: 0

Related Questions