topic Re: Table view in dashboard in Splunk Search

Table view in dashboard

venkat0896 — Mon, 19 Aug 2019 11:05:22 GMT

i am trying to create a Dashboard. i need some assistance on creating a table format.
i have some executions like
1. XXX All rules has been executed in 1068 ms.
2. YYY All rules has been executed in 900 ms.
3. ZZZ All rules has been executed in 1300 ms.

i want to view this as a table with total count of occurrence and Average response time
the table should look like

Request Count Avg response time
XXX 1 1068 ms
YYY 1 900 ms
ZZZ 1 1300 ms

Please help on this. thanks in advance !

Re: Table view in dashboard

richgalloway — Mon, 19 Aug 2019 13:09:16 GMT

This should get you started.

... | rex "(?<Request>\w+).*?in (?<ms>\d+)" 
| stats count as Count avg(ms) as "Avg response time" by Request
| table Request Count "Avg response time"

Re: Table view in dashboard

venkat0896 — Mon, 19 Aug 2019 15:26:14 GMT

@richgalloway
I am getting validation issues "unexpected close tag"
please take a look at my query tag
source="/server.log"
"All rules has been executed in"
| rex "(?\w+).*?in (?\d+)"
| stats count as Count, avg(ms) as "Avg response time" by Request

any suggestions ? thanks in advance !!

Re: Table view in dashboard

ansusabu — Mon, 19 Aug 2019 16:00:17 GMT

it is rex field='fieldname'(regex)

Re: Table view in dashboard

Sukisen1981 — Mon, 19 Aug 2019 16:03:45 GMT

hi @venkat0896
I think your format got corrupted while pasting as a comment, which happens.
@richgalloway 's rex is correct and giving the right output, do you see any issues with using his rex?

Re: Table view in dashboard

venkat0896 — Tue, 20 Aug 2019 09:17:06 GMT

@richgalloway @Sukisen1981
yes looks like the format got corrupted. now i did used the query in search command but the output is not what i was expecting.
My expected format:
Request Count Avg response time
XXX 1 1068 ms
YYY 1 900 ms
ZZZ 1 1300 ms

but was:
Request Count Avg response time
2019 29 169.13793103448276

whats actually happening is the query which you provided is giving the overall count and average response time.
basically i am using 3 different services i want the result to be displayed individually for each service instead of as a whole.
Secondly this query is not appending the service name.
this is my logger
2019-08-20 13:55:47,054 INFO package name XXX All rules has been executed in 540 ms
expected
Request Count Average response
XXX 1 540 ms

but in request it is appending as 2019 as it is picking the first word from the sentence.
appreciate your help on this. thanks

Re: Table view in dashboard

ansusabu — Tue, 20 Aug 2019 10:10:04 GMT

The problem was with the regex, since the log format which you gave was like: XXX All rules has been executed in 1068 ms.

Try using the regex for '2019-08-20 13:55:47,054 INFO package name XXX All rules has been executed in 540 ms'
\)\s(?\w+).*?in (?\d+)

"""... | rex "\)\s(?\w+).*?in (?\d+)"
| stats count as Count avg(ms) as "Avg response time" by request
| table request Count "Avg response time""""

Re: Table view in dashboard

venkat0896 — Tue, 20 Aug 2019 10:21:38 GMT

@ansusabu
Since my code calls multiple services over period of time the time stamp keeps changing. the above mentioned way might work only for the explicitly given time in the expression

Re: Table view in dashboard

ansusabu — Tue, 20 Aug 2019 10:26:45 GMT

@venkat0896 If the logs always follow the below pattern
2019-08-20 13:55:47,054 INFO package name XXX All rules has been executed in 540 ms
(date),ddd INFO package name XXX All rules has been executed in 540 ms

then you can use the following regex,include '\)\s' at the start of regex which richgalloway has mentioned and use the same query

Re: Table view in dashboard

ansusabu — Tue, 20 Aug 2019 10:28:08 GMT

... | rex "\)\s(?<Request>\w+).*?in (?<ms>\d+)" 
 | stats count as Count avg(ms) as "Avg response time" by Request
 | table Request Count "Avg response time"

Re: Table view in dashboard

venkat0896 — Tue, 20 Aug 2019 10:40:25 GMT

@ansusabu Thanks it worked 🙂 🙂