topic Re: How to extract integer value in search from string JSON in log event in Splunk Search

How to extract integer value in search from string JSON in log event

soshua — Wed, 12 Feb 2020 18:11:02 GMT

I am trying to extract 'timeTaken' value from json inside a log event string in order to build a dashboard.

Example log value:

2020-02-12 17:50:15.228  INFO 1 --- [io-8080-exec-45] c.m.v.c.RequestInterceptor         : {"logType":"RESPONSE","traceId":"03927a51-23d6-4530-a0e6-112b6d4b5539","timestamp":"Feb 12, 2020 5:50:15 PM","requestMethod":"GET","requestUrl":"http://my.url","responseStatus":500,"timeTaken":28}

Search example :

index = "my_cluster_name" "kubernetes.labels.app"=my.app | spath log | search log="*"timeTaken"" | rex field=_raw ""timeTaken":(?<timeTaken>.*)}" | timechart span=60s avg(timeTaken)

I also tried:

index = "my_cluster_name" "kubernetes.labels.app"=my.app | spath log | search log="*"timeTaken"" | rex field=_raw "timeTaken\":(?<timeTaken>.*)}" | timechart span=60s avg(timeTaken)

It appears that the value for timeTaken is not populated. I would be grateful for any guidance. Thanks!

Re: How to extract integer value in search from string JSON in log event

somesoni2 — Wed, 12 Feb 2020 19:58:40 GMT

Your log is not in pure json format (some header text are available before json starts). Give this a try (extracting json part and running spath on that)

index = "my_cluster_name" "kubernetes.labels.app"=my.app  | rex "^([^\{]+)(?<jsonpart>.+)" | spath input=jsonpart | timechart span=60s avg(timeTaken)

Re: How to extract integer value in search from string JSON in log event

soshua — Wed, 12 Feb 2020 20:07:36 GMT

Still doesn't work.

timechart span=60s avg(timeTaken) -- is this how I would reference jsonpart.timeTaken?

I tried timechart span=60s avg(timeTaken) as well as timechart span=60s avg(jsonpart.timeTaken)

Re: How to extract integer value in search from string JSON in log event

to4kawa — Wed, 12 Feb 2020 21:33:50 GMT

index = "my_cluster_name" "kubernetes.labels.app"=my.app "timeTaken"
| rex "timeTaken.*?(?<timeTaken>\d+)" 
| timechart span=60s avg(timeTaken)

spath is not need.

Re: How to extract integer value in search from string JSON in log event

soshua — Wed, 12 Feb 2020 21:51:37 GMT

Solution:
index = "my_cluster_name" "kubernetes.labels.app"=my.app | spath log | search log="*"timeTaken"" | rex field=log "(?<time>(?<=timeTaken\":)(.\d?))" | timechart span=60s avg(time) as avgTime

Re: How to extract integer value in search from string JSON in log event

to4kawa — Wed, 12 Feb 2020 22:13:29 GMT

have you try my answer?

Re: How to extract integer value in search from string JSON in log event

Esky73 — Sun, 08 Mar 2020 14:20:09 GMT

you timechart should work ok on the timeTaken field ?

 | makeresults count=1 
    | eval data="2020-02-12 17:50:15.228  INFO 1 --- [io-8080-exec-45] c.m.v.c.RequestInterceptor         : {\"logType\":\"RESPONS\",\"traceId\":\"03927a51-23d6-4530-a0e6-112b6d4b5539\",\"timestamp\":\"Feb 12, 2020 5:50:15 PM\",\"requestMethod\":\"GET\",\"requestUrl\":\"http://my.url\",\"responseStatus\":500,\"timeTaken\":28}\""
    | rex field=data "^([^{]+)(?P<json>.+})\"$"
    | spath input=json