Extract Json and non-json data from single event

[nareshinsvu]

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"
}
}.

[niketn · Accepted solution]

@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

[nareshinsvu]

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

[niketn]

@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!