Getting Data In

display source which not getting any hits

logloganathan
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

niketn
Legend

[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

TISKAR
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

logloganathan
Motivator

thanks for your help.

it displaying "no result"

0 Karma

TISKAR
Builder

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

0 Karma

niketn
Legend

[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!!!"
0 Karma

logloganathan
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

niketn
Legend

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

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

logloganathan
Motivator

@Nikenilay,

Thanks for your help

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

your query not helping

0 Karma

niketn
Legend

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

logloganathan
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

niketn
Legend

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

logloganathan
Motivator

while take out it displaying two sources

0 Karma

niketn
Legend

@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

niketn
Legend

@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

logloganathan
Motivator

its having same sourcetype and same index

0 Karma

kmaron
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

logloganathan
Motivator

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

0 Karma

kmaron
Motivator

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

0 Karma

logloganathan
Motivator

it giving me three values but i have only one.

0 Karma

kmaron
Motivator

It's giving you three zero values?

0 Karma

logloganathan
Motivator

yes kmaron

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...