Calculate time taken from each failed task to next successful task(MTTR) from a list of tasks

[anooshac]

Hello all,

I have a dashboard and the source is json files.

{
"ID": "123",
"TIME": "Jul 11, 2021, 08:55:54 AM",
"STATUS": "FAIL",
"DURATION": "4 hours, 32 minutes"

}

I have many tasks with ID and each task has json files. I want to plot a graph for MTTR( taken from each failed task to next successful task) for these tasks. Previously i was collecting data separately for the MTTR and the graph was plotted direclty from it. But now i have to calucalte MTTR from the above json files.(failed to passed task) and later i want to plot a graph for it. I tried writing a query for it but it's not working.

source="*cc_as_sw_cx_kpi_GEEA_iB2_PSW_Int_csw_build_beta2_*" index="testing" sourcetype="_json"
| transaction STATUS startswith="Status=FAIL" endswith="Status=SUCCESS"|stats avg(Duration) as avg_duration by STATUS | eval MTTR=tostring(avg_duration,"Duration") |timechart dc(MTTR)

I know this not the proper query for it. can anyone please help me in this. I am trying this from few days and this was all i got. Thanks in advance.

[richgalloway]

The query fails in part because the timechart command does not have the _time field available to it.  That's because it's not passed on by stats.  Collecting stats by STATUS doesn't make a lot of sense since all transactions will have both FAIL and SUCCESS status values.  See if this query helps at all.

source="*cc_as_sw_cx_kpi_GEEA_iB2_PSW_Int_csw_build_beta2_*" index="testing" sourcetype="_json"
| transaction ID startswith="Status=FAIL" endswith="Status=SUCCESS"
| bin span=1h _time
| stats avg(Duration) as avg_duration by _time
| eval MTTR=tostring(avg_duration,"Duration") 
| timechart span=1h dc(MTTR)

[anooshac]

Hi, thanks for the response..

I tried your solution but it didn't work. My requirement is to get duration of every failed task to a passed task. How much time the tool took to get a successful  task after a failed task. Every failed task should have a time to recover.  Previously i was doing everything manually. But now i am trying it to do by the Splunk query.Can you suggest anything to implement this?

Thanks in advance.

[richgalloway]

"It didn't work" is not a problem statement.  What results did you get?  What results did you expect to get?

Have you tried running the query incrementally, that is one statement at a time until it breaks?  That will tell you where things are going wrong.

[anooshac]

Hi , Thanks for your response.

I am sorry.. I tried running the query step by step, It is showing as no results. What i exactly want is the list of failed task and its immediate next successful task.  And from that details i want to plot a graph. I think the approach im following is wrong.

[ITWhisperer]

"I tried running the query step by step, It is showing as no results." Does this mean that even just the first line has no results? If not, at what point do you get no results?

[anooshac]

Hi Sir, Thanks for the reply.

The first line itself doesn't give anything. I don't know if i am following the right path or not. Is there any different method exists to find the failed tasks and their respective immediate successful task?

[ITWhisperer]

If the first line (which is taken from your example) isn't returning anything, how have you ingested the logs, what fields are extracted automatically, do you have any searches which return your data?

[anooshac]

I have another search for different requirement. The search is as follows

and the result i get is as follows.

But for the above query even the first line didn't generate anything.

Not sure i'm doing it properly or not.

[ITWhisperer]

Try something like this

source="abc_data1_*" index="testing" sourcetype="_json"
| transaction ID startswith=(STATUS="FAIL") endswith=(STATUS="SUCCESS")

[anooshac]

Hi Sir,

Thanks for the response.

I tried like this.

source="abc_data1_*" index="testing" sourcetype="_json"
| transaction startswith=(STATUS="FAIL") endswith=(STATUS="SUCCESS")

And i am able to get the events. But the search is not considering the data from the beginning, instead it is considering latest data first. It affects the results. How to make the transaction to read the data from the beginning? Like the order should be from old to new. I tried to sort by _time but its not changing.