Getting Data In

display source which not getting any hits

Motivator

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

Tags (2)
0 Karma
1 Solution

SplunkTrust
SplunkTrust

[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.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

0 Karma

Builder

Hello,

If you try by this:

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

Motivator

thanks for your help.

it displaying "no result"

0 Karma

Builder

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

0 Karma

SplunkTrust
SplunkTrust

[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.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

0 Karma

Motivator

Thanks for your help!!
yes..its working but small correction we have to add | where count=0
Final Query:
| tstats count where index="" 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 | where count=0

SplunkTrust
SplunkTrust

Based on previous comment | search count=0 was implied. Glad you figured it out. Good luck!

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

Motivator

@Nikenilay,

Thanks for your help

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

your query not helping

0 Karma

SplunkTrust
SplunkTrust

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
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

Motivator

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

0 Karma

SplunkTrust
SplunkTrust

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.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

Motivator

while take out it displaying two sources

0 Karma

SplunkTrust
SplunkTrust

@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.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

SplunkTrust
SplunkTrust

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

Motivator

its having same sourcetype and same index

0 Karma

Motivator

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
0 Karma

Motivator

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

0 Karma

Motivator

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

0 Karma

Motivator

it giving me three values but i have only one.

0 Karma

Motivator

It's giving you three zero values?

0 Karma

Motivator

yes kmaron

0 Karma