Can you help me with a tstats count using lookup data?

[ajith_sukumaran]

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!

[Vijeta · Accepted solution]

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

Thanks for sharing this search. This is producing the result as required.

[tiagofbmm]

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

[ajith_sukumaran]

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.

[tiagofbmm]

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?

[ajith_sukumaran]

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.