topic Re: HELP! Extracting JSON rex not working... in Splunk Search

HELP! Extracting JSON rex not working...

leomedina — Thu, 20 Apr 2017 17:00:43 GMT

Hello all,

I am trying new things and expanding my palate but having a problem extracting JSON.

My Search:

index=tibco sourcetype="tibco:bw" environment=Prod "success":"*" "PartnerCheckSvc"

Parts of my output:

=-=-=-=-=-=-=-=-=-=-=-=-=
    <Output>
        <jsonString>{
      "success":"true",
      "resellerId":"36502703",
    }</jsonString>
    </Output>
=-=-=-=-=-=-=-=-=-=-=-=-=
<Output>
    <jsonString>{
  "success":"false",
  "errorCode":"RESELLERID_INVALID",
  "message":"Reseller ID not found. Please contact Reseller"
}</jsonString>
</Output> 
=-=-=-=-=-=-=-=-=-=-=-=-=

I've looked at the documentation but it's not displaying the way I'd like. What is the best way of displaying this in a table?

Success    resellerID      Message
True       36502703      
False                      Reseller ID not found. Please contact Reseller

Thanks!

Re: HELP! Extracting JSON rex not working...

skoelpin — Thu, 20 Apr 2017 18:29:51 GMT

You should use the KV_MODE = json attribute in your props.conf.. This will help with extracting fields

Anyways, here's your answer with rex.. This is untested

index=tibco sourcetype="tibco:bw" environment=Prod "success":"*" "PartnerCheckSvc"
| rex success\":\"(?<Success>\w+)
 | rex resellerId\":\"(?<ResellerID>\d+)
 | rex message\":\"(?<Message>.+)(?=\")
 | stats values(Success) AS "Success" values(ResellerID) AS "ResellerID" values(Message) AS "Message" by _time

Re: HELP! Extracting JSON rex not working...

leomedina — Thu, 20 Apr 2017 21:45:09 GMT

Hi Skoelpin,

It's not working... 😕 Is there another way of extracting this data? Maybe with spath? I can't make the edits to the props.conf as that is managed by another team and would take an eternity to get approvals...

Re: HELP! Extracting JSON rex not working...

skoelpin — Thu, 20 Apr 2017 22:25:23 GMT

Remove line 5 and run the search again in smart-mode.. Do you get the Success, resllerID, and message field sin the Interesting fields section?

Re: HELP! Extracting JSON rex not working...

leomedina — Thu, 20 Apr 2017 23:19:26 GMT

Yes , but

 | stats values(Success) AS "Success" values(ResellerID) AS ResellerID values(Message) AS Message

isn't displaying the data... I even tried doing a stats count but those didn't work. I attempted table but that didn't display any data.

Re: HELP! Extracting JSON rex not working...

skoelpin — Fri, 21 Apr 2017 01:45:07 GMT

Try this.. I just tested it and its working correctly

index=tibco sourcetype="tibco:bw" environment=Prod "success":"*" "PartnerCheckSvc"
| rex success\":\"(?<Success>\w+)
 | rex resellerId\":\"(?<ResellerID>\d+)
 | rex message\":\"(?<Message>.+)(?=\")
 | stats values(Success) AS "Success" values(ResellerID) AS "ResellerID" values(Message) AS "Message" by _time

Re: HELP! Extracting JSON rex not working...

leomedina — Fri, 21 Apr 2017 06:34:52 GMT

Thank you, it works!