topic Wrong JSON value extracted by query in Getting Data In

Wrong JSON value extracted by query

user9025 — Tue, 26 Apr 2022 13:42:38 GMT

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

Re: Wrong JSON value extracted by query

kamlesh_vaghela — Tue, 26 Apr 2022 13:42:57 GMT

@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

Thanks
KV

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

Re: Wrong JSON value extracted by query

ITWhisperer — Tue, 26 Apr 2022 13:48:50 GMT

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

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

Re: Wrong JSON value extracted by query

user9025 — Tue, 26 Apr 2022 15:22:52 GMT

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

Re: Wrong JSON value extracted by query

user9025 — Tue, 26 Apr 2022 15:26:46 GMT

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?

Re: Wrong JSON value extracted by query

ITWhisperer — Tue, 26 Apr 2022 15:33:24 GMT

Try extracting the JSON and using spath

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

Re: Wrong JSON value extracted by query

isoutamo — Tue, 26 Apr 2022 15:50:43 GMT

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?

Re: Wrong JSON value extracted by query

user9025 — Tue, 26 Apr 2022 16:29:49 GMT

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?

Re: Wrong JSON value extracted by query

user9025 — Tue, 26 Apr 2022 16:31:58 GMT

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:

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?

Re: Wrong JSON value extracted by query

user9025 — Tue, 26 Apr 2022 16:56:15 GMT

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

Re: Wrong JSON value extracted by query

ITWhisperer — Tue, 26 Apr 2022 17:50:53 GMT

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

Re: Wrong JSON value extracted by query

user9025 — Tue, 26 Apr 2022 22:55:01 GMT

you are a saviour.thanks

Re: Wrong JSON value extracted by query

user9025 — Tue, 26 Apr 2022 23:01:49 GMT

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.