topic Re: Filter results based on aggregates (Another question of Splunk's way to emulate SQL's "HAVING" ) in Splunk Search

Filter results based on aggregates (Another question of Splunk's way to emulate SQL's "HAVING" )

hollybross1219 — Fri, 15 May 2020 20:24:44 GMT

My goal is to design an alert that will populate a table of raw results, but only when certain evaluation aggregates apply. For example, if the total count of events in a time frame >100, post table of raw data. How do I achieve this limitation (similar to SQL "Having"), while reserving my desired table output?

My query so far, which reflects the table output I desire without the "Having" logic:

 splunk_server=indexer* index=wsi sourcetype=fdpwsiperf (channel_type=ofx2 OR agent_service=OfxAgent) domain=tax 
 api_version=v1 capability=* tax_year=2019 partnerId!=*test* partnerId="ADP"
 | lookup Provider_Alert.csv Provider_ID AS partnerId OUTPUT Tier Form_Type
 | search Tier=Tier1
 | eval capability=if(like(capability,"109%"),"1099",'capability')
 | eval error_category=case(like(http_status_code_host,"5%"), "5XX", like(http_status_code_host,"4%"),"4XX", http_error_host="Read 
 timed out", 'http_error_host', 1==1, "Other")
 | table _time, partnerId, intuit_tid, error_category, capability, tax_year, ofx_appid, host_base_url
 | rename intuit_tid as TRNUID

Do not direct me to "From SQL to Splunk SPL" documentation. I've reviewed it, and it's not helpful for my use case.

Thanks!

Re: Filter results based on aggregates (Another question of Splunk's way to emulate SQL's "HAVING" )

richgalloway — Fri, 15 May 2020 21:07:44 GMT

This question is confusing. The title says "limit results", but the body says "count > 100" (the opposite of limit). Which is desired?
Where in the example query would a limit/count be applied?
Is the idea to display/hide the table based on the number of results?

Re: Filter results based on aggregates (Another question of Splunk's way to emulate SQL's "HAVING" )

hollybross1219 — Fri, 15 May 2020 21:23:17 GMT

@richgalloway -- I totally see how that's confusing. Apologies. It's a complex question and it's hard to describe.

I want a solution that outputs raw data in a table... but will only do so if the aggregations of that raw data meet certain criteria (ie. "if total count is more than 10, spit out raw data")

Re: Filter results based on aggregates (Another question of Splunk's way to emulate SQL's "HAVING" )

hollybross1219 — Fri, 15 May 2020 21:26:31 GMT

I found a work-around with the sub-search in the beginning... but I am open to more graceful and creative ways of doing this... as this is incredibly clunky

splunk_server=indexer* index=wsi sourcetype=fdpwsiperf (channel_type=ofx2 OR agent_service=OfxAgent) domain=tax 
api_version=v1 capability=* tax_year=2019 partnerId!=*test* 
    [search splunk_server=indexer* index=wsi sourcetype=fdpwsiperf (channel_type=ofx2 OR agent_service=OfxAgent) domain=tax 
api_version=v1 capability=* tax_year=2019 partnerId!=*test* partnerId=*
     | lookup Provider_Alert.csv Provider_ID AS partnerId OUTPUT Tier Form_Type 
    | search Tier=Tier1 
    | eval error_category=case(like(http_status_code_host,"5%"), "5XX", like(http_status_code_host,"4%"),"4XX", 
http_error_host=timeout_event, 'http_error_host', 1==1, "Other") 
     | chart dc(intuit_tid) OVER partnerId by error_category 
    | addtotals fieldname="total_events" 
    | eval error_rate=round(((total_events-Other)/total_events)*100,2) 
    | where total_events >= 25 AND error_rate >= 40 
    | fields partnerId] 
| lookup Provider_Alert.csv Provider_ID AS partnerId OUTPUT Tier Form_Type 
| search Tier=Tier1 
| eval capability=if(like(capability,"109%"),"1099",'capability') 
| eval error_category=case(like(http_status_code_host,"5%"), "5XX", like(http_status_code_host,"4%"),"4XX", http_error_host="Read 
timed out", 'http_error_host', 1==1, "Other") 
| where error_category!="Other"
| table _time, partnerId, intuit_tid, error_category, capability, tax_year, ofx_appid, host_base_url 
| sort 10 - _time
| rename intuit_tid as TRNUID

Re: Filter results based on aggregates (Another question of Splunk's way to emulate SQL's "HAVING" )

to4kawa — Sat, 16 May 2020 00:54:02 GMT

the pertnerId of your results is only ADP?

Re: Filter results based on aggregates (Another question of Splunk's way to emulate SQL's "HAVING" )

hollybross1219 — Wed, 20 May 2020 18:33:37 GMT

@to4kawa -- sorry, that was just a test case against that partner. The result of that subsearch can be any partner where the where condition applies