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!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...