topic Re: Splunk query in Splunk Search

Can these Splunk queries be modified using tstats?

mahesh27 — Thu, 08 Sep 2022 19:55:09 GMT

Hi all,

I have few queries to be modified using tstats:
I am new to splunk, please let me know whether these queries can be converted into tstats.

Query1:
index=abc "NEW" "/resource/page" appname=ui OR appname=uz |stats avg(response_time).

Query2:
index=abc sourcetype=abc host=ghjy "transaction" NOT "user" |stats avg(ResponseTime)

Query3:
index=abc iru=/resiurce/page appname=ui NOT 1234 NOT 1991 NOT 2022 "Bank status" |stats count

Re: Splunk query

richgalloway — Sat, 03 Sep 2022 00:21:10 GMT

It's unlikely any of those queries can use tstats. The tstats commands uses indexed fields for its searches, which means the 'appname' field would have to be extracted at index-time. The bigger issue, however, is the searches for string literals ("transaction", for example). Such a search requires the _raw field be in the tsidx files, but it is not.

Re: Splunk query

mahesh27 — Sat, 03 Sep 2022 16:40:56 GMT

Hi @richgalloway , As u said "The tstats commands uses indexed fields for its searches, which means the 'appname' field would have to be extracted at index-time." thats true.

but if u see my Query2:
index=abc sourcetype=abc host=ghjy "transaction" NOT "user" |stats avg(ResponseTime)

for this we have "transaction" and "user" keyword in the raw data.

So, i tried using tstats here like below:

|tstats count where index=abc sourcetype=abc host=ghjy TERM(transaction) NOT TERM(user)

i am getting the results.

but when i tried adding stats avg(ResponseTime) i am not getting results like below:

|tstats count where index=abc sourcetype=abc host=ghjy TERM(transaction) NOT TERM(user)
|stats avg(ResponseTime)

here just i want to get the average response time from the above query.

please let me know, how can i do that

Re: Splunk query

richgalloway — Sat, 03 Sep 2022 19:42:10 GMT

It's good that tstats was able to work with the transaction and user fields. That wasn't clear from the OP.

However, like stats, tstats is a transforming command so the only fields available to later commands are those mentioned in tstats. Since tstats does not use ResponseTime it's not available to stats.

Re: Splunk query

mahesh27 — Sun, 04 Sep 2022 02:47:14 GMT

Sorry to ask you this question... So in this case we cannot use tstats ????

Re: Splunk query

richgalloway — Sun, 04 Sep 2022 13:19:30 GMT

Either don't use tstats or somehow include ResponseTime in the tstats command.

|tstats count avg(ResponseTime) where index=abc sourcetype=abc host=ghjy TERM(transaction) NOT TERM(user)

Re: Splunk query

mahesh27 — Wed, 07 Sep 2022 16:54:32 GMT

But this query is not working if we include avg. And the keywords are taken from raw index

Re: Splunk query

mahesh27 — Wed, 07 Sep 2022 16:54:59 GMT

Re: Splunk query

mahesh27 — Wed, 07 Sep 2022 16:55:38 GMT

I am not sure tstats work here

Re: Splunk query

richgalloway — Wed, 07 Sep 2022 18:01:49 GMT

Please share the query you are using.

You can use the walklex command to see which fields are available to tstats.

| walklex type=term index=abc

Re: Splunk query

Vani_26 — Thu, 08 Sep 2022 17:15:57 GMT

orginal query:
index=abc sourcetype=abc host=ghjy transaction NOT user |stats avg(ResponseTime)

Sample events:

logevent: 76:2022-09-08 13:07:12,768:RF :ca.alto.serv.transaction::time:<timestamp> (1) 2022-09-08 13:07:12,768 to 4:09:896 6 ms products()

but i need the same query using tstats.

|tstats count where index=abc sourcetype=abc host=ghjy TERM(transaction) NOT TERM(user)

i am getting the results.

but when i tried adding stats avg(ResponseTime) i am not getting results.

i hope this info helps you...

Re: Splunk query

Vani_26 — Thu, 08 Sep 2022 18:03:56 GMT

one more point here responsetime is extracted field.

Re: Splunk query

richgalloway — Thu, 08 Sep 2022 19:26:44 GMT

I was hoping to see the complete query that is failing, not one that works followed by "then I added this and it didn't work".

That stats avg(ResponseTime) returned no results is expected because the tstats command output did not include a ResponseTime field. I explained that in my Saturday reply. It doesn't matter if ResponseTime is extracted or not - after the tstats command the only fields available are those produced or grouped by tstats.

Re: Splunk query

yuanliu — Thu, 08 Sep 2022 20:19:53 GMT

@mahesh27 wrote:
|tstats count where index=abc sourcetype=abc host=ghjy TERM(transaction) NOT TERM(user)
i am getting the results.
but when i tried adding stats avg(ResponseTime) i am not getting results like below:

|tstats count where index=abc sourcetype=abc host=ghjy TERM(transaction) NOT TERM(user)
|stats avg(ResponseTime)

Thanks for showing the use of TERM() in tstats. (I have used Splunk for very long but also just beginning to learn tstats.)

The reason why the second search won't work is because your tstats does not output any information about ResponseTime. Besides, tstats performs all kinds of stats including avg. Try this

|tstats count avg(ResponseTime) where index=abc sourcetype=abc host=ghjy TERM(transaction) NOT TERM(user)

Re: Splunk query

Vani_26 — Thu, 08 Sep 2022 21:48:13 GMT

This is the complete query:
orginal query:
index=abc sourcetype=abc host=ghjy transaction NOT user |stats avg(ResponseTime)

i want to use using tstats

|tstats count where index=abc sourcetype=abc host=ghjy TERM(transaction) NOT TERM(user)

--after the tstats command the only fields available are those produced or grouped by tstats-- i dont know how to check on this.
I think u gave me other command(| walklex type=term index=abc) to see but i could not able to find it.

Re: Splunk query

yuanliu — Thu, 08 Sep 2022 22:23:52 GMT

@Vani_26 wrote:
one more point here responsetime is extracted field.

ResponsTime (or responsetime as is typed above - which is it?) is the field you need to check with walklex. (Thanks @richgalloway for pointing to this command.)

After richgalloway posted this (I didn't see that before my earlier post)

|tstats count avg(ResponseTime) where index=abc sourcetype=abc host=ghjy TERM(transaction) NOT TERM(user)

you answered

@mahesh27 wrote:
But this query is not working if we include avg. And the keywords are taken from raw index

This statement is inconsistent with the assertion that ResponseTime is extracted at index time. Note, if ResponseTime is extracted at search time (e.g., transformation, in-line extraction, calculated field, etc.), it won't work with tstats.

Maybe you can clarify what "is not working." (Try avoid extremely vague terms such as "not working".) Is it that richgallaway's above code doesn't return any value even though

|tstats count where index=abc sourcetype=abc host=ghjy TERM(transaction) NOT TERM(user)

returns proper values, or is it that avg(ResponseTime) contain no value but count is proper? (The former is quite impossible.)