Getting Data In

Data PArsing json

nawazns5038
Builder

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 

Labels (3)
0 Karma
1 Solution

to4kawa
Ultra Champion

props.conf

SHOULD_LINEMERGE = false

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

KV_MODE = json

 

and SEDCMD-trim = as_you_like

View solution in original post

0 Karma

to4kawa
Ultra Champion
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
Builder

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

0 Karma

to4kawa
Ultra Champion

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

0 Karma

nawazns5038
Builder

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. 

0 Karma

to4kawa
Ultra Champion

props.conf

SHOULD_LINEMERGE = false

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

KV_MODE = json

 

and SEDCMD-trim = as_you_like

0 Karma

nawazns5038
Builder

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. 

0 Karma

to4kawa
Ultra Champion

LINE_BREAKER needs regex chapture()

. is one character.

at this case, "," or "["

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Just take it back to 1 spath

...
| spath certificates{} output=certificates
| mvexpand certificates
| table certificates
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...