How do I write a query to list all the keys in a json log?

Follow

Donald McKinnon

• August 13, 2018 22:31
• Edited

If I have some log { "key1" : "value1", "key2" : "value2", "key3" : "value3" } then how do I write a query that returns key1, key2, key3 when the number and content of the keys is different between logs and I do not know the entire set of possible keys?

 

1

• 

  Kevin

  • August 13, 2018 23:05

  The  JSON Auto function should be able to automatically parse out all the keys from your JSON message. This will create a field for each key name and its value. However, note there is a limit of 100 key fields with this operation. 

  Simple example:

  | json auto

  1

  Comment actions Permalink
• 

  Donald McKinnon

  • August 14, 2018 15:09
  • Edited

  Thanks, but I'm not sure how to get from that json value to just the list of keys. I'm really looking for something like

  | json auto listkeys

  Does that make sense?

  0

  Comment actions Permalink
• 

  Nick

  • August 14, 2018 15:16

  Hi Donald,

  So you're looking to return literally a field that says:

  key1, key2, key3...

  Is that correct?

  I'm curious what it is you're trying to do with this information? That might help us give a bit more guidance here.

  Thanks!
  Nick
  Sumo Logic - Customer Success

  0

  Comment actions Permalink
• 

  Donald McKinnon

  • August 14, 2018 15:20

  Hello Nick,

  Yes, that's correct. I'm trying to check all of the parameters that have been sent to our API endpoints. For example, if we have an API endpoint that accepts a foo parameter but authorized requestors have also been sending a bar parameter (maybe one that we ignore), then I want to know about it. I want to know all of these unexpected parameters.

  Does that make sense?

  Thank you for the quick response.

  Don

  0

  Comment actions Permalink
• 

  Nick

  • August 14, 2018 16:30
  • Edited

  Hi Don,

  Yes, that does make sense, thank you. I agree, I'm not sure the parse json operator is right for this.

  Someone here might have a better idea, but one way you could potentially do this is through regex parse with the "multi" option. A basic example would look something like this:

  *
  | parse regex "(?<delimiter>\?|\&)(?<param_names>[^=]+)\=(?<param_values>[^&]+)" multi
  | count by param_names

  Does this help?

  Thanks,
  Nick
  Sumo Logic - Customer Success

  0

  Comment actions Permalink
• 

  Donald McKinnon

  • August 14, 2018 16:06

  Hey Nick,

  Yes this helps. I suspected this was heading towards a fun regex. I can go with this approach, but if you or someone else comes up with a nifty way to do this without a regex, then that'd be ideal. If not, then no worries!

  Thanks again Nick and Kevin,

  Don

  0

  Comment actions Permalink
• 

  Nick

  • August 14, 2018 16:30

  No prob Don! Glad it helped.

  0

  Comment actions Permalink
• 

  Donald McKinnon

  • August 14, 2018 16:57

  Hey, sorry – I'm back. I spoke too soon. That regex actually doesn't work for me. It doesn't actually return anything for me. Does it return the list of keys when run on valid json logs for you?

  0

  Comment actions Permalink
• 

  Nick

  • August 14, 2018 17:14

  Oops...

  I got too caught up in the whole API endpoint thing, I forgot this was in JSON.

  I found this pattern on Stack Overflow and it works, at least with a simple example:

  *
  | parse regex "(?<key>[^"]+?)"\s*:" multi
  | count by param_names

  Let me know if that gives you a better start.

  Thanks,
  Nick
  Sumo Logic - Customer Success

  0

  Comment actions Permalink
• 

  Donald McKinnon

  • August 14, 2018 17:27

  No worries! I reached the same post.

   

  Thanks,

  Don

  0

  Comment actions Permalink
• 

  Nick

  • August 14, 2018 19:27

  And yet it was down-voted :-/ Anyway no problem Don, glad to help.

  0

  Comment actions Permalink
• 

  Daniel Olshansky

  • August 10, 2019 12:29

  I have followup question related to this and would really appreciate your help: https://support.sumologic.com/hc/en-us/community/posts/360033738173-Stacked-column-chart-for-average-values-of-arbitrary-keysAverage-timeslice

  0

  Comment actions Permalink

Please sign in to leave a comment.