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.
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
This is the Sentinel Search
problem discussed (with solution) here:
https://conf.splunk.com/session/2015/conf2015-LookupTalk.pdf
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
@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)?
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
@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?
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
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
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
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
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
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
Hi @sureshkumaar,
if this answer solves your problem, please accept and/or upvote it.
Ciao and next time.
Giuseppe
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
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