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

scelikok
SplunkTrust
SplunkTrust

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 and "Accept as Solution" 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, Karma 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, Karma 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
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...