topic Re: How to create average time query in Dashboards & Visualizations

How to create average time search?

aditsss — Tue, 25 Jul 2023 16:06:26 GMT

Hi Team,

I am getting these two logs on daily basis:

2023-07-17 08:05:59.764 [INFO ] [Thread-3] TransformProcessor - Started ASSOCIATION process for BusDt=07/16/2023, & version=1

2023-07-17 08:52:44.484 [INFO ] [Thread-3] AssociationProcessor - Successfully completed ASSOCIATION process!! isAssociationBalanced?=true

2023-07-18 08:04:59.764 [INFO ] [Thread-3] TransformProcessor - Started ASSOCIATION process for BusDt=07/17/2023, & version=1

2023-07-18 08:52:44.484 [INFO ] [Thread-3] AssociationProcessor - Successfully completed ASSOCIATION process!! isAssociationBalanced?=true

I want to create one query where I can calculate average time between process start and complete

2023-07-17 08:05:59.764 [INFO ] [Thread-3] TransformProcessor - Started ASSOCIATION process for BusDt=07/16/2023, & version=1

2023-07-17 08:52:44.484 [INFO ] [Thread-3] AssociationProcessor - Successfully completed ASSOCIATION process!! isAssociationBalanced?=true

My current query is this :

index= "600000304_d_gridgain_idx*" sourcetype = "600000304_gg_abs_ipc2" source="/amex/app/gfp-settlement-transform/logs/gfp-settlement-transform.log"

Can someone guide me how to move forward and create average query.

Re: How to create average time query

ITWhisperer — Tue, 25 Jul 2023 11:36:03 GMT

As a guide, you could look at the transaction command

transaction - Splunk Documentation

Re: How to create average time query

aditsss — Tue, 25 Jul 2023 12:11:13 GMT

@ITWhisperer

Can you please suggest me with the query I am not able to find that if we take first time and last time how can we do that:

2023-07-17 08:05:59.764 [INFO ] [Thread-3] TransformProcessor - Started ASSOCIATION process for BusDt=07/16/2023, & version=1

2023-07-17 08:52:44.484 [INFO ] [Thread-3] AssociationProcessor - Successfully completed ASSOCIATION process!! isAssociationBalanced?=true

How can we take first time and last time and can show average of the time.How to fetch time from these two logs and how can we create average time.

@ITWhisperer could you please suggest

Re: How to create average time query

ITWhisperer — Tue, 25 Jul 2023 12:49:33 GMT

Use transaction starts with and ends with arguments to define the start and end of each transaction, then take an average of the durations of the transactions.

Re: How to create average time query

aditsss — Tue, 25 Jul 2023 13:27:02 GMT

@ITWhisperer

This two logs will appear on daily basis . Could you please help me the query here:

How I can fetch time and then calculate average:

2023-07-17 08:05:59.764 [INFO ] [Thread-3] TransformProcessor - Started ASSOCIATION process for BusDt=07/16/2023, & version=1

2023-07-17 08:52:44.484 [INFO ] [Thread-3] AssociationProcessor - Successfully completed ASSOCIATION process!! isAssociationBalanced?=true

Re: How to create average time query

ITWhisperer — Tue, 25 Jul 2023 13:37:36 GMT

Based on my suggestion to use the transaction command, what have you tried so far?

Re: How to create average time query

aditsss — Tue, 25 Jul 2023 13:40:14 GMT

@ITWhisperer

Can you guide me how I can fetch the time from both logs:

2023-07-17 08:05:59.764 [INFO ] [Thread-3] TransformProcessor - Started ASSOCIATION process for BusDt=07/16/2023, & version=1

2023-07-17 08:52:44.484 [INFO ] [Thread-3] AssociationProcessor - Successfully completed ASSOCIATION process!! isAssociationBalanced?=true

Re: How to create average time query

ITWhisperer — Tue, 25 Jul 2023 14:10:07 GMT

The logs should already have a timestamp in the _time field - does this match the time you are looking for?

Re: How to create average time query

aditsss — Thu, 27 Jul 2023 09:26:52 GMT

Hi @ITWhisperer

I have created one query like this for Start time:

index= "abc" sourcetype = "600000304_gg_abs_ipc2" " Started ASSOCIATION process for" source="/amex/app/gfp-settlement-transform/logs/gfp-settlement-transform.log" | rex "-\s+(?<message>.*)" | table message _time

I am getting below result:

message _time

Started ASSOCIATION process for BusDt=07/19/2023, & version=1	2023-07-20 10:49:53.777
Started ASSOCIATION process for BusDt=07/20/2023, & version=1	2023-07-21 10:39:08.819
Started ASSOCIATION process for BusDt=07/21/2023, & version=1	2023-07-22 08:24:19.919

Second query I have created like this for END TIME :

index= "abc*" sourcetype = "600000304_gg_abs_ipc2" "Successfully completed ASSOCIATION process" source="/amex/app/gfp-settlement-transform/logs/gfp-settlement-transform.log" |rex "-\s+(?<message1>.*)" | table message1 _time

Getting below result:

Successfully completed ASSOCIATION process!! isAssociationBalanced?=true 2023-07-20 11:42:53.835
Successfully completed ASSOCIATION process!! isAssociationBalanced?=true 2023-07-21 11:29:00.247
Successfully completed ASSOCIATION process!! isAssociationBalanced?=true 2023-07-22 09:18:20.100

How can I create one single query to show the average time (START and END TIME) Average day wise:

START TIME FOR DAY 2023-07-20

-message _time
Started ASSOCIATION process for BusDt=07/19/2023, & version=1 2023-07-20 10:49:53.777

END TIME FOR DAY 2023-07-20

Successfully completed ASSOCIATION process!! isAssociationBalanced?=true 2023-07-20 11:42:53.835

@ITWhisperer Can you please guide me guide me.

Re: How to create average time query

ITWhisperer — Thu, 27 Jul 2023 10:28:32 GMT

I am trying to guide you but you don't appear to be taking any notice - try using the transaction command

index= "abc" sourcetype = "600000304_gg_abs_ipc2" | rex "\[(?<thread>Thread[^\]]+)\]" | transaction thread startswith=" Started ASSOCIATION process for" endswith="Successfully completed ASSOCIATION process"

You might need a reverse command or perhaps switch the starts with and ends with strings

Re: How to create average time query

aditsss — Thu, 27 Jul 2023 12:09:17 GMT

@ITWhisperer

I tried this query but no result:

index= "abc*" sourcetype = "600000304_gg_abs_ipc2" source="/amex/app/gfp-settlement-transform/logs/gfp-settlement-transform.log" | rex "\[(?<thread>Thread[^\]]+)\]"
| transaction thread startswith=" Started ASSOCIATION process for BusDt" endswith="Successfully completed ASSOCIATION process" | table thread

Can you please guide.

Re: How to create average time query

ITWhisperer — Thu, 27 Jul 2023 15:28:40 GMT

Where did table thread come from? What do you get without that?

Re: How to create average time query

aditsss — Thu, 27 Jul 2023 15:46:36 GMT

Hi @ITWhisperer

With the below query I am not getting any statistics:

index="600000304_d_gridgain_idx*" sourcetype=600000304_gg_abs_ipc2 | rex "\[(?<thread>Thread[^\]]+)\]"
| transaction thread startswith=" Started ASSOCIATION process for" endswith="Successfully completed ASSOCIATION process"

I have attached the screenshot .

How can I calculate average time?

Re: How to create average time query

ITWhisperer — Thu, 27 Jul 2023 17:05:32 GMT

One of the fields you probably get from transaction is duration. Try getting an average of this field.

Re: How to create average time query

aditsss — Thu, 27 Jul 2023 17:50:39 GMT

@ITWhisperer

How can I get the duration from these two loggers:

Started ASSOCIATION process for BusDt=07/25/2023, & version=1 - 2023-07-26 07:52:36.011

Successfully completed ASSOCIATION process!! isAssociationBalanced?=true - 2023-07-26 08:44:07.507

Please guide how I can use transaction command to get average.

Re: How to create average time query

ITWhisperer — Thu, 27 Jul 2023 17:58:28 GMT

ndex="600000304_d_gridgain_idx*" sourcetype=600000304_gg_abs_ipc2 | rex "\[(?<thread>Thread[^\]]+)\]" | transaction thread startswith=" Started ASSOCIATION process for" endswith="Successfully completed ASSOCIATION process" | stats avg(duration)

Re: How to create average time query

aditsss — Thu, 27 Jul 2023 18:32:25 GMT

@ITWhisperer

Thanks the below query work but is it possible to show in hour/min/sec/ms format currently its showing only in seconds.

Re: How to create average time query

ITWhisperer — Thu, 27 Jul 2023 21:44:04 GMT

index="600000304_d_gridgain_idx*" sourcetype=600000304_gg_abs_ipc2 | rex "\[(?<thread>Thread[^\]]+)\]" | transaction thread startswith=" Started ASSOCIATION process for" endswith="Successfully completed ASSOCIATION process" | stats avg(duration) as duration | eval duration=tostring(duration, "duration")

Re: How to create average time query

aditsss — Fri, 28 Jul 2023 05:46:26 GMT

@ITWhisperer

If we want to see each day separately like for last 7 days each day separately should we use table then.

Re: How to create average time query

ITWhisperer — Fri, 28 Jul 2023 09:29:59 GMT

index="600000304_d_gridgain_idx*" sourcetype=600000304_gg_abs_ipc2 | rex "\[(?<thread>Thread[^\]]+)\]" | transaction thread startswith=" Started ASSOCIATION process for" endswith="Successfully completed ASSOCIATION process" | timechart avg(duration) as duration span=1d | eval duration=tostring(duration, "duration")