Is it possible to pass value of column as token to another column of same table

[anooshac]

Hi all,

I am not sure if this is possible. Is there any method that can be used to pass a value of one column as token to another column. Actually i want to use that value passed to calculate some data which should be displayed in the second column. Not sure if this is possible.

[ITWhisperer]

Use eval to create another field (which would be shown in a table as a column).

If this doesn't help, can you provide a more detailed explanation of what you are trying to do?

[anooshac]

Actually this is related to my previous question.

https://community.splunk.com/t5/Dashboards-Visualizations/How-to-make-transaction-starts-with-and-en... 

I used this query to find MTTR. I have many URLs in my data. Now i want to create the table which has columns URL, Status, Date, MTTR etc. I am not able to calculate the MTTR at once for all the URLs. So i was thinking about passing the value of URL from the first column to the query of MTTR.

index=abc|dedup URL|join type=left URL[index=abc URL="The value present in the particular column"| where status!="ABORTED"
| streamstats count as start reset_on_change=true by status
| where start=1
| streamstats count(eval(status=="FAILURE")) as fails by status
| eval fails=if(fails=0,null(),fails)
| filldown fails
| stats list(*) as * by fails| where mvcount(status) = 2|"MTTR calucaltion"]|table URL Status Time MTTR 

I tried calculating MTTR for all the URLs. But the query is grouping the FAIL and SUCCESS of different URLs.

[ITWhisperer]

Subsearches e.g. in a join, are executed before the outer search so there is no way to pass fields from the outer search to the subsearch.

Having said that, if I understand correctly, what you are trying to do is, for each URL, calculate the mean time between failure for that URL and the next success for that URL, right?

[anooshac]

Oh i didn't know that sub searches will execute first.

Yes i am doing the same. Using Transaction command i was not able to get the proper grouping of the FAIL and SUCCESS URL. But the solution which you gave works well for the MTTR calculation. But i am not able to calculate it for many URLs at once. I have a table for all the URL details and i want to include this MTTR toova. But i was not able to group the values properly.

[ITWhisperer]

Assuming the events are ascending time order (if not, insert sort 0 _time), you could try something like this:

| where status!="ABORTED"
| streamstats last(status) as previous global=f current=f by URL
| streamstats range(_time) as ttr global=f window=2 by URL
| where status="SUCCESS" AND previous="FAILURE"
| stats avg(ttr) as MTTR by URL

[anooshac]

I tried this and this and the results does not match with the previous implementations result. Is it possible to modify previous query?

| where status!="ABORTED"
| streamstats count as start reset_on_change=true by status URL
| where start=1
| streamstats count(eval(status=="FAILURE")) as fails by status URL
| eval fails=if(fails=0,null(),fails)
| filldown fails
| stats list(*) as * by fails URL| where mvcount(status) = 2

I tried like this but the results were different.

[ITWhisperer · Accepted solution]

One of the reasons this does not work is that the count is reset if either the URL or the status changes. By adding a sort, this can be avoided.

| where status!="ABORTED"
| sort 0 URL _time
| streamstats count as start reset_on_change=true by status URL
| where start=1
| streamstats first(status) as previous range(_time) as ttr global=f window=2 by URL
| where status="SUCCESS" AND previous="FAILURE"
| stats avg(ttr) as MTTR by URL

[anooshac]

Thank you so much for the info. Thanks for the solution too. It is working fine and i am now able to add the MTTR to the table.