HELP! Extracting JSON rex not working...

[leomedina]

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!

[skoelpin · Accepted solution]

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

[leomedina]

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

[skoelpin]

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?

[leomedina]

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.

[skoelpin]

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

[leomedina]

Thank you, it works!