Data PArsing json

[nawazns5038]

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 

[to4kawa]

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?

[nawazns5038]

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

[to4kawa]

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

[nawazns5038]

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. 

[to4kawa · Accepted solution]

props.conf

SHOULD_LINEMERGE = false

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

KV_MODE = json

and SEDCMD-trim = as_you_like

[nawazns5038]

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. 

[to4kawa]

LINE_BREAKER needs regex chapture()

. is one character.

at this case, "," or "["

[ITWhisperer]

Just take it back to 1 spath

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