Getting Data In

Wrong JSON value extracted by query

user9025
Path Finder

I am running following query  where in the last I would like to fetch value of "Client" key from json and count all such clients. My query goes as follow:

QUERY | rex ".*\"Client\":\"(?<Client>.*)\"," | stats count byClient

The events in query will definitely  has json as the one of the key, but order of the key may change. This extraction of Client from json is not working and I am getting Client as null .What is the problem here.My event looks as follow

 

Event type 1:

 

 

 

request-id : ABC  Executing following method: Class.RestClass ::: with values:

{
  "d1": "EU",
  "sn": "sn",
  "entityType": "USER",
  "email": "test@gmail.com",
  "id": [
    "123"
  ],
  "Client": "TEST",
  "time": "2020-01-01T01:01:01Z",
  "List": [
    {
      "Type": "Items1",
      "value": "-1",
      "match": "NO"
    }
  ]
}

 

 

Event type 2:

 

 

request-id : 234  Execute something ::: with param-values:
{
  "d1": "JP",
  "sn": "sn",
  "type": "USER",
  "user": "test1@gmail.com",
  "id": [
    "123"
  ],
  "source": "S1",
  "Client": "test_client",
  "initiate": "init_Name",
  "mode": "Test",
  "t1": "",
  "t2": "",
  "auto": true,
  "list": [
    {
      "type": "type_count",
      "value": "-1",
      "creteria": "skip"
    }
  ]
}​

 

 

How can I correct my query to get the correct results:.

Labels (2)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

The .* at the beginning is unnecessary - try delimiting Client by the next double quote "

QUERY | rex "\"Client\":\"(?<Client>[^\"]+)\"," | stats count byClient

 

View solution in original post

ITWhisperer
SplunkTrust
SplunkTrust

The .* at the beginning is unnecessary - try delimiting Client by the next double quote "

QUERY | rex "\"Client\":\"(?<Client>[^\"]+)\"," | stats count byClient

 

user9025
Path Finder

thanks for the reply but it did not work .Same problem.Statistics is empty.My data is 

request-id : ABC Executing following method: Class.RestClass ::: with values: 

and then json follows this

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Try extracting the JSON and using spath

QUERY 
| rex "(?ms)(?<json>\{.*\})$"
| spath input=json Client 
| stats count by Client

user9025
Path Finder

I got json from but it is giving lot of parameters and I am interested only in param-values in message .Following is result:

I ran the query given as:

Query | rex "(?ms)(?<json>\{.*\})$" | table json

The json I am getting is as follow:

 

{
  "timestamp": "2022-04-26T15:34:27.679Z",
  "logger_name": "c.a.s.c.c.r.s.RestServiceCommandExecutor",
  "thread_name": "http-exex-5",
  "level": "INFO",
  "serviceath": "LA",
  "processName": "LA",
  "message": "request-id : 123  Executing following method: Test.execute ::: with param-values: {\"DName\":\"EU\",\"service\":\"Test\",\"type\":\"USER\",\"key\":\"test@gmail.com\",\"Id\":[\"123\"],\"Client\":\"MY_TEST_CLIENT\",\"time\":\"2020-01-01T01:01:01Z\",\"list\":[{\"type\":\"COUNT\",\"value\":\"-1\",\"creteria\":\"SKIP\"}]},,",
  "requestId": "456",
  "x-correlation-id": "567",
  "trace_id": "987",
  "trace_flags": "09",
  "span_id": "123"
}

 

 

I am interested in the message parameters,There is json after param-values.In that Json there is Client which I am trying to extract.Can you please check this?

 
 
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
QUERY 
| rex "(?ms)(?<json>\{.*\})$"
| spath input=json message
| rex field=message "(?<values>\{.*\})"
| spath input=values Client
| stats count by Client

user9025
Path Finder

you are a saviour.thanks

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@user9025 

Can you please try this?

YOUR_SEARCH
| rex field=_raw "\"Client\":\s\"(?<Client>.+?)\""
| stats count by Client

 

My Sample Search :

| makeresults 
| eval _raw="{\"d1\": \"EU\",\"sn\": \"sn\",\"entityType\": \"USER\",\"email\": \"test@gmail.com\",\"id\": [\"123\"],\"Client\": \"TEST\",\"time\": \"2020-01-01T01:01:01Z\",\"List\": [{\"Type\": \"Items1\",\"value\": \"-1\",\"match\": \"NO\"}]}" 
| append 
    [| makeresults 
    | eval _raw="{\"d1\": \"JP\",\"sn\": \"sn\",\"type\": \"USER\",\"user\": \"test1@gmail.com\",\"id\": [\"123\"],\"source\": \"S1\",\"Client\": \"test_client\",\"initiate\": \"init_Name\",\"mode\": \"Test\",\"t1\": \"\",\"t2\": \"\",\"auto\": true,\"list\": [{\"type\": \"USER_DRAFTS_COUNT\",\"value\": \"-1\",\"creteria\": \"skip\"}]}"
        ] 
| rex field=_raw "\"Client\":\s\"(?<Client>.+?)\""
| stats count by Client


 

Screenshot 2022-04-26 at 7.12.43 PM.png

 

Thanks
KV


If any of my reply helps you to solve the problem Or gain knowledge, an upvote would be appreciated.

user9025
Path Finder

Thanks for reply but it did not work. You have taken plain json only. Where I have some text info +json.

Example 

request-id : ABC  Executing following method: Class.RestClass ::: with values:

and then json as I mentioned.

Can you prepend this statement in you example and then try if it works?

0 Karma

isoutamo
SplunkTrust
SplunkTrust

It works with your examples also when I have added those text before JSON part. Probably there has change something when you have copy paste those examples?

0 Karma

user9025
Path Finder

I ran the query given as:

Query | rex "(?ms)(?<json>\{.*\})$" | table json

The json I am getting is as follow:

{
  "timestamp": "2022-04-26T15:34:27.679Z",
  "logger_name": "c.a.s.c.c.r.s.RestServiceCommandExecutor",
  "thread_name": "http-exex-5",
  "level": "INFO",
  "serviceath": "LA",
  "processName": "LA",
  "message": "request-id : 123  Executing following method: Test.execute ::: with param-values: {\"DName\":\"EU\",\"service\":\"Test\",\"type\":\"USER\",\"key\":\"test@gmail.com\",\"Id\":[\"123\"],\"Client\":\"MY_TEST_CLIENT\",\"time\":\"2020-01-01T01:01:01Z\",\"list\":[{\"type\":\"COUNT\",\"value\":\"-1\",\"creteria\":\"SKIP\"}]},,",
  "requestId": "456",
  "x-correlation-id": "567",
  "trace_id": "987",
  "trace_flags": "09",
  "span_id": "123"
}

 

I am interested in the message parameters,There is json after param-values.In that Json there is Client which I am trying to extract.Can you please check this?

0 Karma

user9025
Path Finder

If you see, the json i pasted in above comment, it has some weird double comma at then end.But this is what I have.Can I make it work.Cant I directly fetch client.?

0 Karma

user9025
Path Finder

The message is already coming as output from query, we just need to extract client from it.Can you try to run your answer against json i pasted above.

0 Karma
Get Updates on the Splunk Community!

Security Highlights: September 2022 Newsletter

 September 2022 The Splunk App for Fraud Analytics (SFA) is now Splunk SupportedUse your existing Splunk ...

Platform Highlights | September 2022 Newsletter

 September 2022 What’s New in 9.0 and How to UpgradeGet a walk through of what is new Splunk Enterprise 9.0 ...

Observability Highlights | September 2022 Newsletter

 September 2022 Splunk Observability SuiteAccess to "Classic" SignalFx Interface Will be Removed on Sept 30, ...