Splunk query to retrieve value from json log event and get it in a table

Question

I have a log event getting in a json format like this

{
   "level":"level  name",
   "exception":"exception message",
   "logger":"com.log",
   "thread":"thread name",
   "message":"exception message",
   "properties":{
      "id":"1234",
      "process":"Process name,
      "host":"host name",
      "type":"type name"
   }
}

I need a splunk query to get host inside properties as a value to get it in a table. Please help me.

Answer 1

It would help to see what you've tried already so we don't suggest something that doesn't work.
There probably are a few ways to do that, but here's one of them.

... | rex "host\":\"(?<hostName>[^\"]+)"
| table hostName

Note I specifically did not call the field "host" to avoid conflict with the built-in field of the same name.

Answer 2

What have you tried already?

I suspect this (or similar) will work, presuming Splunk's identified this data as being in JSON format already:

index=ndx sourcetype=srctp properties{}.host=*
| rename properties{}.host as hostname
| stats count by hostname