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!
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
the pertnerId
of your results is only ADP?
@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
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?
@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")