topic Re: Need Help Designing Total Outage Alerts in Splunk Search

Need Help Designing Total Outage Alerts

hollybross1219 — Sat, 13 Mar 2021 01:05:55 GMT

Hi there,

I'm having a really hard time creating an alert based of a search that detects the absence of events.

I have a list of total customers we monitor contained in a .csv in Splunk called Provider_Alert.csv

My goal is to create (in SQL terms) a "left" join where my "left" table is all the Providers from Provider_Alert.csv and the second joined table is based off of Splunk logged events (let's call this Search_A), where if there was no match the absence of events would be 0.

An additional challenge I'm having is that the mutual field that join Provider_Alert.csv and Search_A is one I have to derive with eval and coalesce statements (let's call this partner_idd)...since it's split in two fields in Search_A.

So TL;DR:

I'd like to join:

All entries in Provider_Alert.csv

JOIN WHATEVER EVENTS ARE AVAILABLE FROM

Search_A | eval partner_idd =coalesce(field1, field2) | JOIN ON partner_idd

And if there are no results from the JOIN, then it's 0.

Also happy to take recommendations, I've spent a whole afternoon on this so I'm desperate and open for any recommendations.

Re: Need Help Designing Total Outage Alerts

richgalloway — Sat, 13 Mar 2021 13:10:17 GMT

Finding something that is not there is not Splunk's strong suit. See this blog entry for a good write-up on it.

https://www.duanewaddle.com/proving-a-negative/

Re: Need Help Designing Total Outage Alerts

scelikok — Sat, 13 Mar 2021 16:00:09 GMT

Hi @hollybross1219,

You can get the missing partner_idds without using join, please try with below sample;

Re: Need Help Designing Total Outage Alerts

hollybross1219 — Sat, 13 Mar 2021 16:21:40 GMT

Hey @richgalloway , thanks for passing along. I followed the high level concept of the blog, but I'm stuck on my use case which is much more complex.

My file (Provider_Alert.csv) has several columns. So everything appended is blank and I'm not sure how to "join" the mutual fields together.

Based on the article, so far I have this:

splunk_server=indexer* index=wsi sourcetype=fdpwsiperf intuit_offeringid IN ("Intuit.tax.ice.ice", "Intuit.platform.turbotaxwindows","Intuit.tax.ctg.ice.109ximportwidget","Intuit.platform.turbotaxipad.turbotaxmac") api_version=*
| eval offering=if(in(intuit_offeringid,"Intuit.tax.ice.ice","Intuit.tax.ctg.ice.109ximportwidget"),"TTO","TTD")
| eval partner_idd=coalesce(partnerId, legacy_id)
| eval form_type=if(like(capability,"109%"), "109X",'capability')
| eval partner_name=coalesce(partnerId, partner_uid)
| search partner_name!=*test*
| eval combo='partner_idd'+"::"+'form_type'+"::"+'partner_name'+"::"+'api_version'
| chart dc(intuit_tid) as import_activity OVER combo BY offering
| eval partner_idd=mvindex(split(combo,"::"),0)
| eval api_version=mvindex(split(combo,"::"),3)
| fields partner_idd *
| inputlookup append=true Provider_Alert.csv

The resulting table is this:

Is there a way to associate partner_idd with the field called Provider_ID from Provider_Alert.csv before appending so that there's data filled in for mutual matches?

Re: Need Help Designing Total Outage Alerts

richgalloway — Sat, 13 Mar 2021 18:19:02 GMT

To associate two fields, rename one of them to match the other then use the stats command to regroup the results by that field.

index=wsi sourcetype=fdpwsiperf intuit_offeringid IN ("Intuit.tax.ice.ice", "Intuit.platform.turbotaxwindows","Intuit.tax.ctg.ice.109ximportwidget","Intuit.platform.turbotaxipad.turbotaxmac") api_version=* | eval offering=if(in(intuit_offeringid,"Intuit.tax.ice.ice","Intuit.tax.ctg.ice.109ximportwidget"),"TTO","TTD") | eval partner_idd=coalesce(partnerId, legacy_id) | eval form_type=if(like(capability,"109%"), "109X",'capability') | eval partner_name=coalesce(partnerId, partner_uid) | search partner_name!=*test* | eval combo='partner_idd'+"::"+'form_type'+"::"+'partner_name'+"::"+'api_version' | chart dc(intuit_tid) as import_activity OVER combo BY offering | eval partner_idd=mvindex(split(combo,"::"),0) | eval api_version=mvindex(split(combo,"::"),3) | fields partner_idd * | inputlookup append=true Provider_Alert.csv | rename partner_idd as Provider_ID | stats values(*) as * by Provider_ID

Re: Need Help Designing Total Outage Alerts

hollybross1219 — Sun, 14 Mar 2021 21:48:40 GMT

@richgalloway -- so this solves the join problem between mutual values in Provider_Alert.csv and my data from Search_A, however, I still can't detect outage situations 😞

Values from Provider_Alert.csv that are not in Search_A are gone.

For example, I know we had a service outage at a very specific time...so I'm looking for that Provider_ID (676) from Provider_Alert.csv present in my stats results with 0.

The query below with your suggestions doesn't yield any results 😞

index=wsi sourcetype=fdpwsiperf intuit_offeringid IN ("Intuit.tax.ice.ice", "Intuit.platform.turbotaxwindows","Intuit.tax.ctg.ice.109ximportwidget","Intuit.platform.turbotaxipad.turbotaxmac") api_version=* tier=Tier1
| eval offering=if(in(intuit_offeringid,"Intuit.tax.ice.ice","Intuit.tax.ctg.ice.109ximportwidget"),"TTO","TTD")
| eval partner_idd=coalesce(partnerId, legacy_id)
| eval form_type=if(like(capability,"109%"), "109X",'capability')
| eval partner_name=coalesce(partnerId, partner_uid)
| search partner_name!=*test*
| eval combo='partner_idd'+"::"+'form_type'+"::"+'partner_name'+"::"+'api_version'
| chart dc(intuit_tid) as import_activity OVER combo BY offering
| eval partner_idd=mvindex(split(combo,"::"),0)
| eval api_version=mvindex(split(combo,"::"),3)
| fields partner_idd *
| inputlookup append=true Provider_Alert.csv
| rename partner_idd as Provider_ID
| stats values(*) as * by Provider_ID api_version
| where Provider_ID=676

Re: Need Help Designing Total Outage Alerts

hollybross1219 — Sun, 14 Mar 2021 21:55:12 GMT

Tinkered around a little...

| rename partner_idd as Provider_ID

This achieves renaming partner_idd based on splunk events and wiping away Provider_ID from Provider_Alert.csv

| rename Provider_ID as partner_idd

Wiped away partner_idd from actual splunk events and renamed data in Provider_Alert.csv

Re: Need Help Designing Total Outage Alerts

hollybross1219 — Sun, 14 Mar 2021 22:29:23 GMT

Figured it out...

| inputlookup append=true Provider_Alert.csv where Tier=Tier1 Active="TRUE"
| join type=outer
[ search index=wsi sourcetype=fdpwsiperf intuit_offeringid IN ("Intuit.tax.ice.ice", "Intuit.platform.turbotaxwindows","Intuit.tax.ctg.ice.109ximportwidget","Intuit.platform.turbotaxipad.turbotaxmac") api_version=* tier=Tier1
| eval offering=if(in(intuit_offeringid,"Intuit.tax.ice.ice","Intuit.tax.ctg.ice.109ximportwidget"),"TTO","TTD")
| eval Provider_ID=coalesce(partnerId, legacy_id)
| search Provider_ID!=*test*
| chart dc(intuit_tid) as import_activity OVER Provider_ID BY offering
| fields Provider_ID *]
| fillnull