Splunk Search

Can these Splunk queries be modified using tstats?

mahesh27
Communicator

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

 

Labels (1)
Tags (1)
0 Karma

richgalloway
SplunkTrust
SplunkTrust

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.

---
If this reply helps you, Karma would be appreciated.
0 Karma

mahesh27
Communicator

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

 

Tags (1)

yuanliu
SplunkTrust
SplunkTrust

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

 

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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.

---
If this reply helps you, Karma would be appreciated.
0 Karma

mahesh27
Communicator

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

 

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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)  

 

---
If this reply helps you, Karma would be appreciated.
0 Karma

mahesh27
Communicator

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

I

 

 

 

 

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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

 

---
If this reply helps you, Karma would be appreciated.
Tags (1)

Vani_26
Path Finder

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

 

 

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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.

---
If this reply helps you, Karma would be appreciated.
0 Karma

Vani_26
Path Finder

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.

0 Karma

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

0 Karma

Vani_26
Path Finder

one more point here responsetime is extracted field.

0 Karma

mahesh27
Communicator

I am not sure tstats work here

 

0 Karma

mahesh27
Communicator

I

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...