topic Data PArsing json in Getting Data In

Data PArsing json

nawazns5038 — Tue, 25 Aug 2020 23:44:33 GMT

I am trying to parse json data in Splunk

This is the example data.

{ "certificates": [ { "NotAfter": "2020-09-06T15:34:22-07:00", "NotBefore": "2019-09-07T15:34:22-07:00", "allowedOperations": [ "certificate_show", "certificate_der_download" ], }, { "NotAfter": "2020-10-07T10:51:40-07:00", "NotBefore": "2019-10-08T10:51:40-07:00", "allowedOperations": [ "certificates_show" ], }

I want only the data between the tags before "NotAfter" into separate events , and the top part has to be ignored.

I have tried regex101 to identify to identify the breaking patters , it works there but not in Splunk.

Can you please guide.

Thanks.,

nawaz

Re: Data PArsing json

to4kawa — Wed, 26 Aug 2020 10:50:15 GMT

index=_internal | head 1 | fields _raw | eval _raw="{ \"certificates\":[ { \"NotAfter\":\"2020-09-06T15:34:22-07:00\", \"NotBefore\":\"2019-09-07T15:34:22-07:00\", \"allowedOperations\":[ \"certificate_show\", \"certificate_der_download\" ] }, { \"NotAfter\":\"2020-10-07T10:51:40-07:00\", \"NotBefore\":\"2019-10-08T10:51:40-07:00\", \"allowedOperations\":[ \"certificates_show\" ] } ] }"

what's tag?

... | spath certificates{} output=certificates | mvexpand certificates | spath input=certificates | table Not* allowed*

like this?

Re: Data PArsing json

nawazns5038 — Wed, 26 Aug 2020 17:11:41 GMT

Hi @to4kawa ,

It didn't work . I want two separate events like this., I tried LINE_BREAKER and break only before in props.conf, to parse the data into individual events but still didn't work.

I was able to use regex101 and find a regex to break the event and applied the same regex in Splunk but its not taking

{ "NotAfter": "2020-09-06T15:34:22-07:00", "NotBefore": "2019-09-07T15:34:22-07:00", "allowedOperations": [ "certificate_show", "certificate_der_download" ], },

{ "NotAfter": "2020-10-07T10:51:40-07:00", "NotBefore": "2019-10-08T10:51:40-07:00", "allowedOperations": [ "certificates_show" ], }

Thanks
Nawaz

Re: Data PArsing json

ITWhisperer — Wed, 26 Aug 2020 17:54:38 GMT

Just take it back to 1 spath

... | spath certificates{} output=certificates | mvexpand certificates | table certificates

Re: Data PArsing json

to4kawa — Wed, 26 Aug 2020 20:29:47 GMT

Don't confuse the SPL and the setting.

we make the query. not props.conf because you had not said the setting.

Have you tried the query?

> It didn't work
please provide the detail. for example, table or _raw

Re: Data PArsing json

nawazns5038 — Thu, 27 Aug 2020 19:32:32 GMT

yes , the SPL seems fine.

But in reality , each event contains 50 different certificate tags and comes to 4096 lines in each event.

The main drawback is if we do spath and mvexpand the raw events view would be the same and confusing, i.e, every event would have 4096 lines instead of just one certificate.,

And if we do spath and mvexpand on many events where each event has 50 different certificates , it would take a lot of time. and unclear.

That's why I was preferring to break the events at props level so that each certificate would go to each event and the raw events view would be easy to work with.

Anyways, thanks for the SPL you deserve points.

Re: Data PArsing json

to4kawa — Thu, 27 Aug 2020 20:11:44 GMT

props.conf

SHOULD_LINEMERGE = false

LINE_BREAKER = (.){\s*\"NotAfter

KV_MODE = json

and SEDCMD-trim = as_you_like

Re: Data PArsing json

nawazns5038 — Thu, 27 Aug 2020 22:48:03 GMT

Thanks @to4kawa ,

I was stuck at (.) part.

what does (.) mean in regex , how did you manage to figure it out ?

do we need to always capture something in the LINE_BREAKER before we write the regex.

Thanks

Nawaz.

Re: Data PArsing json

to4kawa — Fri, 28 Aug 2020 08:51:32 GMT

LINE_BREAKER needs regex chapture()

. is one character.

at this case, "," or "["