Splunk Dashboard to show the missing sources

[sureshkumaar]

host=ABC OR host=DEF sourcetype=111 source=abcdef* OR source=ghijkl* OR source=mnopq* | dedup source | table source

Looking for a query to show the only missing source among the list of sources available from the query.

[woodcock]

This is the Sentinel Search problem discussed (with solution) here:
https://conf.splunk.com/session/2015/conf2015-LookupTalk.pdf

[gcusello · Accepted solution]

Hi @sureshkumaar,
what's the difference with your question
https://answers.splunk.com/answers/789866/splunk-dashboard-to-show-the-missing-sources.html#answer-7... ?
I already answered to it.
you could try something like this:

  host = ABC* OR host = DEF* sourcetype=111 source=abcedef* OR source=ghijkl* OR source=mnopqr* 
 | rex field=source "(?<check_source>abcedef)"
 | rex field=source "(?<check_source>ghijkl)"
 | rex field=source "(?<check_source>mnopqr)"
 | append [ | makeresults | eval check_source="abcedef", count=0 | fields check_source count ]
 | append [ | makeresults | eval check_source="ghijkl", count=0 | fields check_source count ]
 | append [ | makeresults | eval check_source="mnopqr", count=0 | fields check_source count ]
 | stats sum(count) AS total BY check_source
 | where total=0

if you have more values to check it's better to put them in a lookup and try something like this:

 host = ABC* OR host = DEF* sourcetype=111 source=abcedef* OR source=ghijkl* OR source=mnopqr* 
 | rex field=source "(?<check_source>abcedef)"
 | rex field=source "(?<check_source>ghijkl)"
 | rex field=source "(?<check_source>mnopqr)"
 | append [ | inputlookup my_lookup.csv | eval  count=0 | fields check_source count ]
 | stats sum(count) AS total BY check_source
 | where total=0

Ciao.
Giuseppe

[sureshkumaar]

@gcusello - Both are same questions only.

@gcusello - Thanks for your valuable answer, does this query works sends that particular source whenever a traffic isn't flowing during a certain time range (for ex: last 15 minutes)?

[gcusello]

Hi @sureshkumaar,
yes, this search match the sources results with your fixed patterns.

then, in your alert, you configure the time period you want (e.g. 5-15 minutes).

The most importanti issue is that if you have only three -for values, you have to insert them in yur search, otherwise you have to maintain an updated lookup.

ciao.
Giuseppe

[sureshkumaar]

@gcusello - i tried the same by having 2 sources only, but still i got alert though both sources are showing GOOD flow of traffic. I am looking alert triggering when the traffic isn't flowing for a particular source

Can you please explain what does count=0 and total=0 in the query does?

[gcusello]

Hi @sureshkumaar,
could you share your search? probably the regexes aren't correct.

Anyway, the meaning of the search is that you need to have a numeric value to all the search terms, also the ones that aren't in the search results, for this reason you have count=0 in the append.
total=0 is the result of the sum of the saerch results and the appends and is the condition to check:
total=0 means that you haven't any result and the value is from the append,
total>0 means that you have values from the append (0) and from the search.

Ciao.
Giuseppe

[sureshkumaar]

@gcusello

Below is the search

host = ABC* OR host = DEF* sourcetype=111 source=123 or source=456 | rex field=source "(?123)" | rex field=source "(?456)" | append [ | makeresults | eval check_source="123", count=0 | fields check_source count ] | append [ | makeresults | eval check_source="456", count=0 | fields check_source count ] | stats sum(count) AS total BY check_source | where total=0

[gcusello]

Hi @sureshkumaar,
when you share a search or a regex, please use always the Code Sample button (1010101) otherwise it's difficoult to read you code.
Anyway, I try to interprete:

host = ABC* OR host = DEF* sourcetype=111 source=123 or source=456 
| rex field=source "(?<check_source>123)" 
| rex field=source "(?<check_source>456)" 
| append [ | makeresults | eval check_source="123", count=0 | fields check_source count ] 
| append [ | makeresults | eval check_source="456", count=0 | fields check_source count ] 
| stats sum(count) AS total BY check_source 
| where total=0

Ciao.
Giuseppe

[sureshkumaar]

@gcusello

I modified the query according to my hosts and source i am using in my client environment, on last week Friday 1:30 am - 2:00 am EST, one of the source traffic didn't flow, but when i am trying below query both the sources it's showing as 0 only
Can you please let me know that result of this query will be showing as "0" for the source where traffic isn't flowing?

host = ABC* OR host = DEF* sourcetype=111 source=123 or source=456
| rex field=source "(?123)"
| rex field=source "(?456)"
| append [ | makeresults | eval check_source="123", count=0 | fields check_source count ]
| append [ | makeresults | eval check_source="456", count=0 | fields check_source count ]
| stats sum(count) AS total BY check_source
| where total=0

[gcusello]

Hi @sureshkumaar,
Sorry! my error: a statement is missing, try this:

host = ABC* OR host = DEF* sourcetype=111 source=123 or source=456 
| rex field=source "(?<check_source>123)" 
| rex field=source "(?<check_source>456)" 
| stats count BY check_source
| append [ | makeresults | eval check_source="123", count=0 | fields check_source count ] 
| append [ | makeresults | eval check_source="456", count=0 | fields check_source count ] 
| stats sum(count) AS total BY check_source 
| where total=0

This is my test:

index=_internal
| rex field=source "(?<check_source>metrics)" 
| rex field=source "(?<check_source>access)" 
| rex field=source "(?<check_source>source_missing_for_test)" 
| stats count BY check_source
| append [ | makeresults | eval check_source="metrics", count=0 | fields check_source count ] 
| append [ | makeresults | eval check_source="access", count=0 | fields check_source count ] 
| append [ | makeresults | eval check_source="source_missing_for_test", count=0 | fields check_source count ] 
| stats sum(count) AS total BY check_source

Ciao.
Giuseppe

[sureshkumaar]

Thanks @gcusello

Below one worked successfully

host = ABC* OR host = DEF* sourcetype=111 source=123 or source=456
| rex field=source "(?123)"
| rex field=source "(?456)"
| stats count BY check_source
| append [ | makeresults | eval check_source="123", count=0 | fields check_source count ]
| append [ | makeresults | eval check_source="456", count=0 | fields check_source count ]
| stats sum(count) AS total BY check_source
| where total=0

[gcusello]

Hi @sureshkumaar,
if this answer solves your problem, please accept and/or upvote it.
Ciao and next time.
Giuseppe

[sureshkumaar]

@gcusello

If i have more values to check, you provided the below line for the query.

| append [ | inputlookup my_lookup.csv | eval count=0 | fields check_source count ]

In the CSV file i have to provide only the source or the whole line naming the column name as check_source like below with column header named as "check_source"

| append [ | makeresults | eval check_source="abcedef", count=0 | fields check_source count ]
| append [ | makeresults | eval check_source="ghijkl", count=0 | fields check_source count ]
| append [ | makeresults | eval check_source="mnopqr", count=0 | fields check_source count ]

Kindly suggest

[gcusello]

Hi @sureshkumaar,
yes, in your lookup you have to put at least une column called "check_source".
and as values all the values to search: abcedef, ghijkl, mnopqr.
Remember to create also the Lookup Definition [Settings -- Lookups -- Lookup Definitions].

Ciao.
Giuseppe