topic display source which not getting any hits in Getting Data In

display source which not getting any hits

logloganathan — Tue, 10 Apr 2018 13:18:59 GMT

i have different source and want to display source which not getting any hits

I have the following query

source=ABC OR source=ABD OR source=ADC | stats count by source

time frame: last 1 day

i need the result like this

Source count
ADC 0

Re: display source which not getting any hits

kmaron — Tue, 10 Apr 2018 13:23:49 GMT

This is how I do it

| stats count 
| eval source="ABC,ABD,ADC" 
| makemv delim="," source 
| mvexpand source 
| append 
    [ search source=ABC OR source=ABD OR source=ADC ] 
| stats sum(eval(if(isnull(_time),0,1))) as count by source 
| where count = 0

Re: display source which not getting any hits

logloganathan — Tue, 10 Apr 2018 13:39:46 GMT

i am getting different value..actually i am doing for last 1 day..Could you please modify according to that

Re: display source which not getting any hits

kmaron — Tue, 10 Apr 2018 13:44:07 GMT

there's no time specified in the search so just change your time picker to last 1 day.

Re: display source which not getting any hits

logloganathan — Tue, 10 Apr 2018 13:48:01 GMT

it giving me three values but i have only one.

Re: display source which not getting any hits

kmaron — Tue, 10 Apr 2018 13:56:45 GMT

It's giving you three zero values?

Re: display source which not getting any hits

logloganathan — Tue, 10 Apr 2018 15:20:48 GMT

yes kmaron

Re: display source which not getting any hits

niketn — Tue, 10 Apr 2018 15:26:22 GMT

@logloganathan do these three sources write data to same sourcetype or different? Also is the index same for these sources or different?

Re: display source which not getting any hits

niketn — Tue, 10 Apr 2018 15:38:26 GMT

[UPDATED ANSWER]

The following search should give 0 count for a sourcetype with no events. Run anywhere example from Splunk's _internal index is also added for testing. A very small time window should be picked to ensure that there is no event from specific source to test.

|  tstats count where index="<yourIndexName>" AND (source="ABC" OR source="ABD" OR source="ADC") by source
|  append [ | makeresults
                   | fields - _time 
                   | eval source="ABC,ABD,ADC"
                   | makemv source delim=","
                   | mvexpand source
                   | eval count=0 ]
|  dedup source

Following is a run anywhere search based on Splunk's _internal index (since it always writes time window should be really small like last 5 seconds to get 0 event count).

| tstats count where index=_internal AND (sourcetype="splunk_web_access" OR sourcetype="splunkd_ui_access" OR sourcetype="splunkd") by sourcetype 
| append 
    [| makeresults 
    | fields - _time 
    | eval sourcetype="splunk_web_access,splunkd_ui_access,splunkd" 
    | makemv sourcetype delim="," 
    | mvexpand sourcetype 
    | eval count=0]
| dedup sourcetype

@logloganathan, for scenario like these where you have to perform stats on metadata fields, ideally tstats or metadata command should be used. Following is a run anywhere search based on Splunk's _internal index for three source patterns. Please try out and adjust as per your need.

|  tstats count as totalCount earliest(_time) as earliestEventTime latest(_time) as _time where index="_*" AND (source="*kvstore.log" OR source="*metrics.log" OR source="*license_usage_summary.log") by source
|  fieldformat earliestEventTime=strftime(earliestEventTime,"%Y-%m-%d %H:%M:%S")
|  reltime

PS: reltime command applies on _time field (which in this case is last time event was written on specific source), to give the relative time.

Re: display source which not getting any hits

TISKAR — Tue, 10 Apr 2018 16:27:31 GMT

Hello,

If you try by this:

source=ABC OR source=ABD OR source=ADC daysago=1  | stats count by source | where count=0

Re: display source which not getting any hits

logloganathan — Wed, 11 Apr 2018 15:18:50 GMT

@Nikenilay,

Thanks for your help

i want to display the source which as count value as zero

your query not helping

Re: display source which not getting any hits

logloganathan — Wed, 11 Apr 2018 15:21:01 GMT

thanks for your help.

it displaying "no result"

Re: display source which not getting any hits

logloganathan — Wed, 11 Apr 2018 15:22:12 GMT

its having same sourcetype and same index

Re: display source which not getting any hits

TISKAR — Wed, 11 Apr 2018 15:33:35 GMT

if it displaying "no result" because all count are > 0 try by where count!=0 to verified

Re: display source which not getting any hits

niketn — Wed, 11 Apr 2018 15:36:11 GMT

My Previous query was for you to test out and understand the working of tstats command with run anywhere example. For getting the count by source as 0, you need to add the following filter in the end | search totalCount=0 and also provide your index name index="<yourIndexName>"

 |  tstats count as totalCount earliest(_time) as earliestEventTime latest(_time) as _time where index="<yourIndexName>" AND (source="ABC" OR source="ABD" OR source="ADC") by source
 |  fieldformat earliestEventTime=strftime(earliestEventTime,"%Y-%m-%d %H:%M:%S")
 |  reltime
 | search totalCount=0

Re: display source which not getting any hits

logloganathan — Wed, 11 Apr 2018 15:40:53 GMT

i have provided my index name in the query but it displaying "no result" now

earlier query produced the result of two source with some value

Re: display source which not getting any hits

niketn — Wed, 11 Apr 2018 15:48:47 GMT

No Results means there is not source with 0 count. Take out final search totalCount=0, you will see how many events you have received in last 1 day. Also when was first event and last event received from the source in last 1 day.

Re: display source which not getting any hits

logloganathan — Wed, 11 Apr 2018 15:53:42 GMT

while take out it displaying two sources

Re: display source which not getting any hits

niketn — Wed, 11 Apr 2018 20:00:37 GMT

@logloganathan, I have updated the answer to reflect 0 count in case the source does not have any event for selected time duration. Test with smaller duration to confirm 0 count before switching to last 1 day.

Please try out and confirm.

Re: display source which not getting any hits

logloganathan — Thu, 12 Apr 2018 08:37:30 GMT