Getting Data In

Extract Json and non-json data from single event

nareshinsvu
Builder

Hello Champions,

Need your help in extracting mixed data. Below is my sample data. I indexed it as a single event with SHOULD_LINEMERGE =TRUE. Now I want to extract "username" value only from "appliactionCredential" but not from "Response". Is there an easy way to extract JSON fileds without saving it as _json sourcetype? Because, I wanted to extract the timestamp and command as well from the 1st line.

2019-06-11_00:06:53.356 [https-openssl-apr-443-exec-70] DEBUG c.audit.AuditingAspect - Testing Command response is
{
"Response" : {
"status" : "ACCEPTED",
"credential" : {
"username" : "TestOnline",
"failedLoginAttempts" : 0,
"failedPwdResetAttempts" : 0,
"passwordSecurity" : 1,
"type" : "APPLICATION"
},
"appliactionCredential" : {
"username" : "Testlogin",
"failedLoginAttempts" : 0,
"failedPwdResetAttempts" : 0,
"passwordSecurity" : 1,
"type" : "APPLICATION"
},
"successful" : true,
"userId" : 00001
},
"code" : "00",
"description" : "ACCEPTED",
"auditId" : "audit123",
"messageId" : "message123",
"txnTime" : 1560175613200,
},
"Profile" : {
"systemItemStatus" : "ENABLED",
"environment" : "sandbox"
}
},
"Profile_2" : {
"systemItemStatus" : "ENABLED",
"environment" : "UAT",
"alwaysPINRequired" : "false"
}
}.

Tags (1)
0 Karma
1 Solution

niketn
Legend

@nareshinsvu can you try the following rex with your current data?

<yourCurrentSearch>
| rex "Command response is(?ms)(?<_raw>.*)"
| spath

Ideally, if you do not need the text before JSON, you can drop the same using Heavy Forwarder or Indexer layer, so that
1) You index only required data hence save license
2) Have indexed_extraction enabled for JSON data so that you can use tstast for better performance.

Following is a run anywhere example based on the sample data provided.

|  makeresults
|  eval _raw="2019-06-11_00:06:53.356 [https-openssl-apr-443-exec-70] DEBUG c.audit.AuditingAspect - Testing Command response is
{
\"Response\" : {
\"status\" : \"ACCEPTED\",
\"credential\" : {
\"username\" : \"TestOnline\",
\"failedLoginAttempts\" : 0,
\"failedPwdResetAttempts\" : 0,
\"passwordSecurity\" : 1,
\"type\" : \"APPLICATION\"
},
\"appliactionCredential\" : {
\"username\" : \"Testlogin\",
\"failedLoginAttempts\" : 0,
\"failedPwdResetAttempts\" : 0,
\"passwordSecurity\" : 1,
\"type\" : \"APPLICATION\"
},
\"successful\" : true,
\"userId\" : 00001
},
\"code\" : \"00\",
\"description\" : \"ACCEPTED\",
\"auditId\" : \"audit123\",
\"messageId\" : \"message123\",
\"txnTime\" : 1560175613200,
},
\"Profile\" : {
\"systemItemStatus\" : \"ENABLED\",
\"environment\" : \"sandbox\"
}
},
\"Profile_2\" : {
\"systemItemStatus\" : \"ENABLED\",
\"environment\" : \"UAT\",
\"alwaysPINRequired\" : \"false\"
}
}"
| rex "Command response is(?ms)(?<_raw>.*)"
| spath
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

0 Karma

niketn
Legend

@nareshinsvu can you try the following rex with your current data?

<yourCurrentSearch>
| rex "Command response is(?ms)(?<_raw>.*)"
| spath

Ideally, if you do not need the text before JSON, you can drop the same using Heavy Forwarder or Indexer layer, so that
1) You index only required data hence save license
2) Have indexed_extraction enabled for JSON data so that you can use tstast for better performance.

Following is a run anywhere example based on the sample data provided.

|  makeresults
|  eval _raw="2019-06-11_00:06:53.356 [https-openssl-apr-443-exec-70] DEBUG c.audit.AuditingAspect - Testing Command response is
{
\"Response\" : {
\"status\" : \"ACCEPTED\",
\"credential\" : {
\"username\" : \"TestOnline\",
\"failedLoginAttempts\" : 0,
\"failedPwdResetAttempts\" : 0,
\"passwordSecurity\" : 1,
\"type\" : \"APPLICATION\"
},
\"appliactionCredential\" : {
\"username\" : \"Testlogin\",
\"failedLoginAttempts\" : 0,
\"failedPwdResetAttempts\" : 0,
\"passwordSecurity\" : 1,
\"type\" : \"APPLICATION\"
},
\"successful\" : true,
\"userId\" : 00001
},
\"code\" : \"00\",
\"description\" : \"ACCEPTED\",
\"auditId\" : \"audit123\",
\"messageId\" : \"message123\",
\"txnTime\" : 1560175613200,
},
\"Profile\" : {
\"systemItemStatus\" : \"ENABLED\",
\"environment\" : \"sandbox\"
}
},
\"Profile_2\" : {
\"systemItemStatus\" : \"ENABLED\",
\"environment\" : \"UAT\",
\"alwaysPINRequired\" : \"false\"
}
}"
| rex "Command response is(?ms)(?<_raw>.*)"
| spath
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

nareshinsvu
Builder

@niketnilay - Awesome. It worked. Yes the requirement is bit wierd. I need to capture few data from this JSON and also I need to capture few more data from outside JSON (normal text lines). And I have to do a transaction command on all these lines.

So, I couldn't just extract JSON fileds extraction during forwarding.

You mentioned about 1) saving license. Is that by extracting only the json fields instead of full json?
2) tstats and saving performance - Yes, but complex - as i mentioned, I also need to capture "Testing" word before the JSON line. Any easy way to extract that and assign a column in the result?

0 Karma

niketn
Legend

@nareshinsvu

1) If you drop unwanted text you will be indexing less amount of data. Hence save license accordingly. However, seems like you need to extract Testing from regex pattern as well. So, may not be applicable for you.
2) Please try the following regex which extracts myField with Testing. Since regular expression will be strictly based on the pattern in your data, you would need to tweak this and test on regex101.com. I have saved the sample data at the following location: https://regex101.com/r/GCzdbZ/1

| rex "\] DEBUG ([^\s]+)\s+-\s+(?<myField>[^\s]+)\sCommand response is(?ms)(?<_raw>.*)" 
| spath
|  table myField *

Do accept/up-vote the answer if your issue is resolved!

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
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 ...