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
[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.
Hello,
If you try by this:
source=ABC OR source=ABD OR source=ADC daysago=1 | stats count by source | where count=0
thanks for your help.
it displaying "no result"
if it displaying "no result" because all count are > 0 try by where count!=0
to verified
[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.
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
Based on previous comment | search count=0
was implied. Glad you figured it out. Good luck!
@Nikenilay,
Thanks for your help
i want to display the source which as count value as zero
your query not helping
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
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
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.
while take out it displaying two sources
@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.
@logloganathan do these three sources write data to same sourcetype or different? Also is the index same for these sources or different?
its having same sourcetype and same index
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
i am getting different value..actually i am doing for last 1 day..Could you please modify according to that
there's no time specified in the search so just change your time picker to last 1 day.
it giving me three values but i have only one.
It's giving you three zero values?
yes kmaron