Splunk Search

How to optimize search SPL Subsearch/Append?

dhirendra761
Contributor

HI Splunkers,

 

Requirement:

I have to create table for COUNT OF ERRORS based on text search in _raw data. I have created below query:

 

 

eventtype=XXX_AC_db ("Transaction (Process ID *) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.*" OR "Rest Api POST error. Database has timed out. (TT-000346)") 
| rex field=Exception "System(?<m>.*):\s(?<message>.*)\s+at" 
| eval message=if(like(message,"%Transaction (Process ID %) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.%"),"Transaction (Process ID XX) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.",message) 
| stats count by message 
| append 
    [ stats count 
    | where count=0 
    | eval message="Transaction (Process ID XX) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction."] 
| append 
    [| search eventtype=XXX_AC_db "Rest Api POST error. Database has timed out. (TT-000346)" 
    | stats count by Message 
    | rename Message as message] 
| append 
    [ stats count 
    | where count=0 
    | eval message="Rest Api POST error. Database has timed out. (MG-000346)"] 
| append 
    [| search eventtype=XXX_AC_db "*Database has timed out. (TT-000346)*" 
    | eval Message=if(like(Message,"%Database has timed out. (TT-000346)%"),"Database has timed out. (TT-000346)",Message) 
    | stats count by Message 
    | rename Message as message] ...................

 

 

 

This query is taking too much time to execute. Is there any other way so that we can include different search and get the result.

 

Thank you in advance.

Labels (4)
0 Karma
1 Solution

dhirendra761
Contributor

Hi @gcusello ,

Thank you for your response. The reason behind the Count=0, if there is no result then error-text having count 0. Hope your answer is match with this condition as well.

Anyway, I will check your answer in practically and accept it if it is good. 

Thank you.

View solution in original post

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

I think you have either made a typo in the example you posted or in your actual search - there is a different command which I think would be more useful - appendpipe - something like this

eventtype=XXX_AC_db ("Transaction (Process ID *) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.*" OR "Rest Api POST error. Database has timed out. (TT-000346)") 
| rex field=Exception "System(?<m>.*):\s(?<message>.*)\s+at" 
| eval message=if(like(message,"%Transaction (Process ID %) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.%"),"Transaction (Process ID XX) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.",message) 
| stats count by message 
| appendpipe 
    [ stats count 
    | where count=0 
    | eval message="Transaction (Process ID XX) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction."] 
| append 
    [| search eventtype=XXX_AC_db "Rest Api POST error. Database has timed out. (TT-000346)" 
    | stats count by Message 
    | rename Message as message] 
| appendpipe
    [ stats count 
    | where count=0 
    | eval message="Rest Api POST error. Database has timed out. (MG-000346)"] 
| append 
    [| search eventtype=XXX_AC_db "*Database has timed out. (TT-000346)*" 
    | eval Message=if(like(Message,"%Database has timed out. (TT-000346)%"),"Database has timed out. (TT-000346)",Message) 
    | stats count by Message 
    | rename Message as message] ...................

 

0 Karma

dhirendra761
Contributor

Hi @ITWhisperer ,

Thank you for suggestion. After applying the appendpipe we are loosing events.

Tags (1)
0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @dhirendra761,

I have some dubt that your search correctly runs because the subsearches with "where count=0" should have always no results.

So you should have a different approach (Splunk isn't a DB and SPL isn't SQL, even if they have a similar name!).

Try something like this. this is an approach to applicate to your situation:

eventtype=XXX_AC_db ("Transaction (Process ID *) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.*" OR "Rest Api POST error. Database has timed out. (TT-000346)" OR eventtype=XXX_AC_db "Rest Api POST error. Database has timed out. (TT-000346)" OR eventtype=XXX_AC_db "*Database has timed out. (TT-000346)*") 
| rex field=Exception "System(?<m>.*):\s(?<message>.*)\s+at" 
| eval message=if(like(message,"%Transaction (Process ID %) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.%"),"Transaction (Process ID XX) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.",message) 
| eval message=if(like(Message,"%Database has timed out. (TT-000346)%"),"Database has timed out. (TT-000346)",Message) 
| stats count by message 
| append [ | makeresults | eval message="Transaction (Process ID XX) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.", count=0 | fields message count ] 
| append [ | makeresults | eval message="Rest Api POST error. Database has timed out. (MG-000346)", count=0 | fields message count ] 
| stats sum(count) AS count BY message

In few words, I inserted in the main search all the conditions except the ones with count=0 and I calculated stats.

Then I appended the condition for count=0 and I recalculate the sum of count.

In this way you're sure to have also the conditions for count=0.

In addition in this way you have another advantage: there's the limit of 50,000 results for subsearches that in this way you don't have.

 Ciao.

Giuseppe

0 Karma

dhirendra761
Contributor

Hi @gcusello ,

Thank you for your response. The reason behind the Count=0, if there is no result then error-text having count 0. Hope your answer is match with this condition as well.

Anyway, I will check your answer in practically and accept it if it is good. 

Thank you.

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @dhirendra761 ,

good for you, see next time!

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated by all the Contributors 😉

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