Dashboards & Visualizations

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

anooshac
Communicator

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.

Labels (3)
Tags (3)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

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

View solution in original post

ITWhisperer
SplunkTrust
SplunkTrust

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?

0 Karma

anooshac
Communicator

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.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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?

0 Karma

anooshac
Communicator

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.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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
0 Karma

anooshac
Communicator

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.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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
Communicator

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.

0 Karma
Get Updates on the Splunk Community!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...