Splunk Search

Need Help Designing Total Outage Alerts

hollybross1219
Path Finder

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.

Labels (1)
0 Karma
1 Solution

hollybross1219
Path Finder

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

View solution in original post

hollybross1219
Path Finder

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

View solution in original post

scelikok
Champion

Hi @hollybross1219,

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

Search_A
| eval partner_idd=colaesce(field1, field2)
| eval partner_active=1
| inputlookup append=t Provider_alert.csv
| stats max(partner_active) as partner_active by partner_idd
| where isnull(partner_active)
If this reply helps you an upvote is appreciated.

richgalloway
SplunkTrust
SplunkTrust

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/

---
If this reply helps you, an upvote would be appreciated.

hollybross1219
Path Finder

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:

blank_fields.JPG

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?

 

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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
---
If this reply helps you, an upvote would be appreciated.

hollybross1219
Path Finder

@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

0 Karma

hollybross1219
Path Finder

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

0 Karma
.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!