I am using below query to get search result and calculate the failure percentage but not getting the expected result.
index=dl* ("Error_MongoDB") | timechart span 1d count as Failure | appendcols [search index=dl* ("inserted Record") | timechart span=1d count as Success | eval (FailurePercentage = Failure/Sucess)*100 | field _time,Failure,Sucess,FailurePercentage
I am getting all the values except FailurePercentage. What could be the reason ?
So, try the suggestion - you only need the single search as I posted earlier, but with your updated search it should be like this
index=dl* ("Error_MongoDB") OR ("Record_Inserted")
| eval Status=if(match(_raw, "Error_MongoDB"), "Failure", "Success")
| rename msg.attribute.ticketId as ticketId
| timechart span=1d dc(ticketId) by Status
| eval FailurePercentage = (Failure/Success)*100
| fillnull FailurePercentage
You don't need to use all the fields/table commands - the timechart will remove all the unnecessary fields anyway
There are lots of errors in that query if cut/pasted to a Splunk search, but if you are not getting FailurePercentage, that's because the statement
| eval (FailurePercentage = Failure/Sucess)*100
is not a valid Splunk eval statement and Sucess is also spelt incorrectly compared to the calculation in your stats command
Note that your approach to appendcols if not a good way to approach this problem and can be done more efficiently like this
index=dl* ("Error_MongoDB") OR ("inserted Record")
| eval Status=if(match(_raw, "Error_MongoDB"), "Failure", "Success")
| timechart span=1d count as Total by Status
| eval FailurePercentage = (Failure/Success)*100
| fillnull FailurePercentage
so you don't need a subsearch and can do it in one timechart and the fillnull will take care if the value of Success is 0
Note that the eval Status line may be improved if you have a field that can indicate success/failure better than by matching _raw
Thank you @bowesmana for quick response. I am writing down the exact query here.
I have to combine both the queries to get Failure % using timechart.
Query 1 ( Success ) :
index=dl* ("Record_Inserted") | fields msg.attribute.ticketId
| rename msg.attribute.ticketId as ticketId
| table ticketId,_time
| timechart span=1d dc(ticketId)
Query 2 ( Failure ) :
index=dl* ("Error_MongoDB") | fields msg.attribute.ticketId | rename msg.attribute.ticketId as ticketId
| table ticketId,_time
| timechart span=1d dc(ticketId)
So, try the suggestion - you only need the single search as I posted earlier, but with your updated search it should be like this
index=dl* ("Error_MongoDB") OR ("Record_Inserted")
| eval Status=if(match(_raw, "Error_MongoDB"), "Failure", "Success")
| rename msg.attribute.ticketId as ticketId
| timechart span=1d dc(ticketId) by Status
| eval FailurePercentage = (Failure/Success)*100
| fillnull FailurePercentage
You don't need to use all the fields/table commands - the timechart will remove all the unnecessary fields anyway