count events by index and by sourcetype from an inputlookup

[jip31]

hello

I need to count the events generated by index and by sourcetype from an host list (csv file)
It seems to work but its very very long
how to do this with better performances please??

index=toto
| join host type="outer"
[| inputlookup host.csv]
| stats count by sourcetype

[niketn · Accepted solution]

@jip31 try the following search based on tstats which should run much faster.

| tstats count where index=toto [| inputlookup hosts.csv | table host ] by sourcetype

Following is a run anywhere example based on Splunk's _internal index.

| tstats count where index=_internal 
    [| tstats count where index=_internal by sourcetype 
    | search sourcetype IN ("splunkd*") 
    | table sourcetype] by host sourcetype

[FrankVl]

Use tstats to count the index contents:

| tstats count where index=toto by sourcetype,host
| join host type=outer [
| inputlookup host.csv
]

[niketn]

@FrankVl you beat me to it. I have slightly different answer, but that also involves sub-search.

[FrankVl]

Yours filters the tstats results with the lookup list, it doesn't show the lookup entries that do not occur in the index and vice versa (which his original search with outer join does). So that's why I tried to stay as close as possible to his existing search.

If filtering is all you need, your solution would indeed also work.

Since he does a stats count by sourcetype in the end, he might actually be destroying the hosts that are only in the lookup anyway (unless that lookup also contains sourcetype field).