Hello,
I have the below query trying to produce the event and host count for the last hour. The index & sourcetype is listed in the lookup CSV file.
However this search does not show an index - sourcetype in the output if it has no data during the last hour.
How do I use fillnull or any other method to show the event & host count as 0 when there is no data for that index/sourcetype?
| tstats count, dc(host) as hosts where
[| inputlookup List.csv ]
by index, sourcetype
My List.csv is something like below, that lists down the index and sorcetypes.
index sourcetype
---------- ----------------
win_idx seclog
unx_idx syslog
nw_idx lblog
db_idx dblog
For eg: if index=db_idx sourcetype=dblog
count is 0, my current output using the above search is
index sourcetype count hosts
---------- ---------------- --------- ----------
win_idx seclog 1500 15
unx_idx syslog 3000 50
nw_idx lblog 1000 25
My expected output is:
index sourcetype count hosts
---------- ---------------- --------- ----------
win_idx seclog 1500 15
unx_idx syslog 3000 50
nw_idx lblog 1000 25
db_idx dblog 0 0
Thanks!
@ajith_sukumaran - Try this
| tstats count, dc(host) as hosts where
[| inputlookup List.csv ]
by index, sourcetype| append[|inputlookup List.csv ]| fillnull value=0 count, hosts| stats sum(count) as count, sum(hosts) as hosts by index, sourcetype
@ajith_sukumaran - Try this
| tstats count, dc(host) as hosts where
[| inputlookup List.csv ]
by index, sourcetype| append[|inputlookup List.csv ]| fillnull value=0 count, hosts| stats sum(count) as count, sum(hosts) as hosts by index, sourcetype
Thanks for sharing this search. This is producing the result as required.
There will be nothing to count on if the sourcetype doesn't have any events there, that's just how Splunk works there. You can get the results you want with this though:
| inputlookup list.csv
| join type=left index, sourcetype [
| inputlookup list.csv
| map search="| tstats count where index=$index$ sourcetype=$sourcetype$ by index,sourcetype"]
| fillnull value=0
Let me know what you think
Thanks. I tried this but the result is not as expected as the count value gets mismatched with the index - sourcetype.
The first few index/ sourcetype shows the values as expected and fills data for those where no events. That's great.
However remaining index/ sourcetype shows value 0 even if there is events present.
This search shows a count of 0 for pairs (sourcetype,index) that don't have any value. Adding the dc(host) will get your fourth column. Value mismatch on the count? tstats count by index,sourcetype can't be wrong, this runs tstats count by index,sourcetype
, how can it be right for some and wrong for others?
yeah, I agree. This should have populated the actual counts and filled with null values. But I am not sure why the mismatch happens. Thanks for the search, I have the stats as required now.