Timechart with Failurepercentage and appendcols

[yuvrajsharma_13]

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 ?

[bowesmana]

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

[yuvrajsharma_13]

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)

[bowesmana · Accepted solution]

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