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.
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.
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] ...................
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
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.
Hi @dhirendra761 ,
good for you, see next time!
Ciao and happy splunking
Giuseppe
P.S.: Karma Points are appreciated by all the Contributors 😉