Displaying full list of server names

[jsmith39]

I'm querying a log file on 50+ servers looking for the number of records processed in a given time frame. The problem I'm running in to is that if one or more of those servers did not process any records during that time frame they do not show up in the output. So if I have another tech unfamiliar with the system run the query they wouldn't notice missing records.

earliest=-12h sourcetype=excludelist | stats count by tag::source, host, source | rename tag::source as County | rename host as "Server Name" | rename source as "File Path" | rename count as "Feeds Processed"

How could I modify this to have a 0 displayed in the count (Feeds Processed) field if there were no results?

[mcrawford44]

To clarify the current solution to those who don't know;

The stats command will not return a record for a field, if that field is null.

ColName, ColData1, ColData2, Coldata3
Name1, data1, data2, data3
Name2, data1, data2, data3
Name3, data1, NULL, data3

'stats count by data2' will omit the last record. This happens outside of counts with lists and values as well.

To your join question, yes it defaults to left. There is also no full outer, and it has to be "faked" via an append.

[Ayn]

I just want to add that this query is pretty inefficient considering you have this kind of stats at your disposal in the internal metrics. There's per_sourcetype_thruput, per_host_thruput among other things, so you can very quickly grab data like event count and volume of indexed data split by these parameters. Probably MUCH faster than the approach you're using right now.

[jsmith39]

Thank you for the input, I will try and fine tune the query with your suggestions.

[somesoni2 · Accepted solution]

How about this...

|metasearch index=* sourcetype=excludelist  | stats count by host, source | fields - count | join type=left host, source [search sourcetype=excludelist earliest=-12h@h | stats count by tag::source, host, source] | fillnull count | rename tag::source as County | rename host as "Server Name" | rename source as "File Path" | rename count as "Feeds Processed"

[jsmith39]

Thank you very much, as far as I can tell that worked perfectly. I just need to go through it a few more times so I understand what was written. Plus I'd like to move some of the columns around. Would you happen to have a link to a join tutorial, the splunk documentation I've found doesn't cover join type=left and I'm unsure what all it is doing.

[MuS]

Hi jsmith39,

you can use the fillnull search command for that.

hope this helps ...

cheers, MuS

[jsmith39]

Morning, I tried the second command you sent, and the results are the same. It's literally as if the nullfill command is being ignored. It doesn't cause any errors being there, but doesn't affect the outcome in any way. If I shrink the time zone from 12 hours to say 3 hours when I know only a few records were created, only those records created are showing in output. The other servers don't appear.

[araitz]

earliest=-12h sourcetype=excludelist | fillnull host | fillnull source | stats count by tag::source, host, source | rename tag::source

[jsmith39]

Hi, thank you for the suggestion, unfortunately it's not working, at least the way I understand the command.

After earliest=-12h sourcetype=excludelist | stats count by tag::source, host, source | rename tag::source as County
I've tried to add the following
| fillnull value=NULL
| fillnull value=NULL count
| fillnull (which should fill all empty fields with a zero)

None of the above cases changes the output, if there are <1 record count I get a no results found message.