Splunk Search

Unable to build query using append/appendpipe to get desired percentage of stats

bennythedroid
Engager

Given:

index=log category=Price
| eval PriceStatus=case(activity=="approve" AND event=="complete", "Price Approved", activity!="approve" AND event!="complete", "Approval not Complete")
| stats dc(reqId) as reqCount by PriceStatus
| appendpipe [delta reqCount as ActualReqInPricing | eval PriceStatus=null, reqCount=null]
| append [search index=other_log | rsp=500 | stats dc(reqId) as AffectedReqIdCount ]

alt text

index=log is the location of workflow events that have various activity and event types. I need to excluded approval events from the result set to accurately determine which IDs are still 'active' for this process. Therein lies the first potential problem; I couldn't figure out a way to compare event statuses by IDs between all the events within a single search, so I went for this approach of adding an additional status for approved, and 'not approved' for everything else (there are many different activities and events within each category), getting the distinct count by ID for each status, and subtracting the count of "approved" from "everything else" (which is thus still 'active') by using appendpipe to take the delta after stats have been created.

The append I'm using is to bring in search results from a separate index that contains access events for a tool that manages the approval process. I want to track failures in the tool as a distinct count of IDs to get an overall sense of how many IDs are having their workflow blocked by failures in the tool during a given time frame. What I'm trying to do now is append a calculated percentage of AffectedReqIdCount/ActualReqInPricing, but haven't been able to use stats or eval and get it appended to my stats table. I'm new to the majority of the functions I'm trying to use here as of today so I figure this query is super ugly as it stands. If anyone could help me understand how to get rid of the blank rows I'm generating, as well as how to add a clean percentage of affected IDs, it would make my Friday night. Thanks!

0 Karma
1 Solution

niketn
Legend

@bennythedroid try the following search and confirm!

index=log category=Price
| fields activity event reqId
| eval PriceStatus=case(activity=="approve" AND event=="complete", "Price Approved", activity!="approve" AND event!="complete", "Approval not Complete")
| stats dc(reqId) as "Request Count" by PriceStatus 
| transpose header_field=PriceStatus column_name="Price Status"
| eval ActualReqInPricing='Price Approved'-'Approval not Complete' 
| appendcols 
    [| search index=other_log rsp=500
     | fields reqId
     | stats dc(reqId) as AffectedReqIdCount]

Following is a run anywhere example based on Splunk's _internal index which mocks up query/fields as per your use case.

index=_internal sourcetype=splunkd log_level=* 
| eval reqId=component 
| eval PriceStatus=case(log_level=="INFO", "Price Approved", log_level=="WARN" OR log_level=="ERROR", "Approval not Complete") 
| stats dc(reqId) as "Request Count" by PriceStatus 
| transpose header_field=PriceStatus column_name="Price Status"
| eval ActualReqInPricing='Price Approved'-'Approval not Complete' 
| appendcols 
    [| search index=_internal sourcetype=splunkd_ui_access status!=200
    | stats dc(uri) as AffectedReqIdCount]
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

niketn
Legend

@bennythedroid try the following search and confirm!

index=log category=Price
| fields activity event reqId
| eval PriceStatus=case(activity=="approve" AND event=="complete", "Price Approved", activity!="approve" AND event!="complete", "Approval not Complete")
| stats dc(reqId) as "Request Count" by PriceStatus 
| transpose header_field=PriceStatus column_name="Price Status"
| eval ActualReqInPricing='Price Approved'-'Approval not Complete' 
| appendcols 
    [| search index=other_log rsp=500
     | fields reqId
     | stats dc(reqId) as AffectedReqIdCount]

Following is a run anywhere example based on Splunk's _internal index which mocks up query/fields as per your use case.

index=_internal sourcetype=splunkd log_level=* 
| eval reqId=component 
| eval PriceStatus=case(log_level=="INFO", "Price Approved", log_level=="WARN" OR log_level=="ERROR", "Approval not Complete") 
| stats dc(reqId) as "Request Count" by PriceStatus 
| transpose header_field=PriceStatus column_name="Price Status"
| eval ActualReqInPricing='Price Approved'-'Approval not Complete' 
| appendcols 
    [| search index=_internal sourcetype=splunkd_ui_access status!=200
    | stats dc(uri) as AffectedReqIdCount]
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

bennythedroid
Engager

I slapped an abs on ActualReqInPricing and was able to use this to build out exactly what I needed. Thanks so much @niketnilay

niketn
Legend

Awesome! Glad you got it working.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...