Filter Based on click

[athorat]

We have a query which is using join condition to filter data and we have a graph resulting into three columns
Fail|Succcess| Success_v_Invalid

When I click on the Fail bar it should display the events only for Failed Results.
Right now it shows the entire list of events.
How can we restrict or narrow it down.

Query Used

index="dpa" sourcetype="DP:PROD:SYSLOG" PROXYNAME="UBIAPI" URI="/vehicle/DeviceFullFillment"  TransactionStatus=FAIL   | join type=left CorrelationId [search index="dpa" sourcetype="DP:PROD:SYSLOG" PROXYNAME="UBIAPI" URI="/vehicle/DeviceFullFillment"  TransactionStatus !=FAIL]  | search TransactionStatus=FAIL  TransactionStatus=FAIL | append [search index="dpa" sourcetype="DP:PROD:SYSLOG" PROXYNAME="UBIAPI" URI="/vehicle/DeviceFullFillment"  TransactionStatus !=FAIL] 

[woodcock · Accepted solution]

Try ditching join like this:

index="dpa" sourcetype="DP:PROD:SYSLOG" PROXYNAME="UBIAPI" URI="/vehicle/DeviceFullFillment" | stats values(*) AS * dc(TransactionStatus) AS numTS latest(TransactionStatus) AS FinalStatus BY CorrelationId | eval myStatus=case(numTS==1 AND FinalStatus!="Fail", "Alls Good", FinalStatus!="Fail", "Ends Good", 1==1, "Fail") | stats count by myStatus

[athorat]

We have three statuses
Success, Success_Voucher_Invalid and Failed.
The query which you modified appears to be correct but it was adding Failed and Success Count

I have changed the query to the following and this appears to bring the correct count with status.

index="dpa" sourcetype="DP:PROD:SYSLOG" PROXYNAME="UBIAPI" URI="/vehicle/DeviceFullFillment" | stats values(*) AS * dc(TransactionStatus) AS numTS latest(TransactionStatus) AS FinalStatus BY CorrelationId | eval myStatus=case(numTS==1 AND FinalStatus="FAIL", "FAILED", FinalStatus="SUCCESS_VOUCHER_INVALID", "SUCCESS_VOUCHER_INVALID", FinalStatus="SUCCESS", "SUCCESS", 1==1, "Fail") | stats count by myStatus

Would be great if you can explain how that ditching join was worked out...

[woodcock]

Your original search was curiously over-complicated so I made some assumptions about why that was so but it seems thay maybe this much simpler search will do what you need:

index="dpa" sourcetype="DP:PROD:SYSLOG" PROXYNAME="UBIAPI" URI="/vehicle/DeviceFullFillment" | stats latest(TransactionStatus) AS FinalStatus BY CorrelationId | chart count BY FinalStatus

[woodcock]

When you use join (and other subsearch-based commands) Splunk usually (always?) loses the click/drilldown capability. I assume because there is no (reliable) way to figure out where to insert the clicked clause because the search ia composed of more than one search.

[athorat]

aaah, that query looks/sounds way to simple 😕
Well the logic was when the transaction for Insurance Device fails it is reprocessed manually and the same policy with the same corelationid turns to success
So we have two events with same policy and corelationid but 2 diff status. Hence we thought of joining data based on corelationid, filter the data out and list only the real/current failed status.

[athorat]

@woodcock

a followup question based on the query which you suggested.
We have a panel with the following query and it shows the Avg_Response_Time column just fine

index="dpa" sourcetype="DP:PROD:SYSLOG"  PROXYNAME="UBIAPI" URI="/vehicle/DeviceFullFillment"   | chart avg(Latency) as Avg_Response_Time count over TransactionStatus

When I used the query which you suggested and try to add the column for Avg_Response_Time it shows a blank column, any suggestions about integrating/overlaying Avg_Response_Time.

Your Query:
index="dpa" sourcetype="DP:PROD:SYSLOG" PROXYNAME="UBIAPI" URI="/vehicle/DeviceFullFillment" | stats latest(TransactionStatus) AS FinalStatus BY CorrelationId | chart count BY FinalStatus

[woodcock]

The only reason for Avg_Response_Time to not be created is because field Latency either does not exist (check spelling/capitalization) or it has non-numeric (NaN) values or the events where it does exist and have non-numeric values do not have a non-null TransactionStatus field.

[athorat]

It does exist and the spelling is correct. It gets results into some of the other panels.

May be this is not the right way?

index="dpa" sourcetype="DP:PROD:SYSLOG" PROXYNAME="UBIAPI" URI="/vehicle/DeviceFullFillment" | stats latest(TransactionStatus) AS FinalStatus BY CorrelationId |stats avg(Latency) as Avg_Response_Time count by FinalStatus

[woodcock]

I see the problem (I did not notice the stacked stats); try this:

index="dpa" sourcetype="DP:PROD:SYSLOG" PROXYNAME="UBIAPI" URI="/vehicle/DeviceFullFillment" | stats latest(TransactionStatus) AS FinalStatus avg(Latency) as Avg_Response_Time BY CorrelationId

[athorat]

I had tried a similar variation but and it gives the same error :
Error in 'stats' command. The argument 'stats' is invalid.

[woodcock]

Sorry, I had an extra word in there and have re-edited it. Try it now.

[athorat]

oh, Well I will stick to the original query for time being,

The original query solves the problem by creating two columns "TransactionStatus "and "count"

and wanted to add another column to this existing panel.
This one shows all transaction status instead of the count by correlation id.
I cannot add pictures here to make more sense to what I am trying to say.