stats query on JSON data

[luke_vamasoft]

Hi,

I've been struggling with spath attempts for this for a day or two, so reaching out for help! I have the following detail within an event:

07/03/2014 11:55:05 Debug [b9d94320-2915-4bbc-a96f-98fa125c4e27] [IC3QOE11OAB5318640POGM14VSURV5] Wallet3DSecureSetResult started with Result[{'paressyntaxok':'true','paresverified':'false','version':'2.0','merchantid':'455665864697','xid':'MDAwMDAwMDBPREhMMDUzNDc3NzE=','mdstatus':'1','mderrormsg':'Authenticated','txstatus':'Y','ireqcode':'','ireqdetail':'','vendorcode':'','eci':'02','cavv':'jOm+n8MEwOyKAREABwMDh/QP5n4=','cavvalgorithm':'3','md':'123,123','digest':'U2eto4Un1nUa3jdOlRuzQk59ERY=','sid':'2','veresenrolledstatus':'Y','parestxstatus':'Y','status':'ok','requestpage':'api_deposit.aspx'}] MPIKey[borgun] 

The detail I'm interested in is 'merchantid' and 'mdstatus'.

mdstatus can be a value between 0 and 8 - I want to be able to analyse per merchantid the count of each mdstatus.

Any help appreciated!

Cheers,
Luke

[martin_mueller · Accepted solution]

It seems to me as if spath is confused by single quotes in the JSON. Take a look at this:

| stats count | fields - count | eval _raw = "07/03/2014 11:55:05 Debug [b9d94320-2915-4bbc-a96f-98fa125c4e27] [IC3QOE11OAB5318640POGM14VSURV5] Wallet3DSecureSetResult started with Result[{'paressyntaxok':'true','paresverified':'false','version':'2.0','merchantid':'455665864697','xid':'MDAwMDAwMDBPREhMMDUzNDc3NzE=','mdstatus':'1','mderrormsg':'Authenticated','txstatus':'Y','ireqcode':'','ireqdetail':'','vendorcode':'','eci':'02','cavv':'jOm+n8MEwOyKAREABwMDh/QP5n4=','cavvalgorithm':'3','md':'123,123','digest':'U2eto4Un1nUa3jdOlRuzQk59ERY=','sid':'2','veresenrolledstatus':'Y','parestxstatus':'Y','status':'ok','requestpage':'api_deposit.aspx'}] MPIKey[borgun]" | rex "Result\[(?<result>.*?)\]" | eval result=replace(result, "'", "\"") | spath input=result | fields merchantid mdstatus | fields - _raw

This gives me your two interesting fields perfectly, but only after replacing the single quotes with double quotes.

According to http://www.json.org/ double quotes are the only valid way of enclosing strings in JSON. Consider fixing your source to output valid JSON.

[luke_vamasoft]

Thank you Martin - I can enjoy my weekend now!!!