Can these Splunk queries be modified using tstats?

[mahesh27]

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

[richgalloway]

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.

[mahesh27]

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

[yuanliu]

---

@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)

[richgalloway]

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.

[mahesh27]

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

[richgalloway]

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)  

[mahesh27]

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

I

[richgalloway]

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

[Vani_26]

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...

[richgalloway]

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.

[Vani_26]

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.

[yuanliu]

@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.) 

[Vani_26]

one more point here responsetime is extracted field.

[mahesh27]

I am not sure tstats work here

[mahesh27]

I