Knowledge Management

count events by index and by sourcetype from an inputlookup

jip31
Motivator

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

Tags (1)
0 Karma
1 Solution

niketn
Legend

@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
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

0 Karma

niketn
Legend

@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
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

FrankVl
Ultra Champion

Use tstats to count the index contents:

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

niketn
Legend

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

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

FrankVl
Ultra Champion

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).

Get Updates on the Splunk Community!

Enterprise Security Content Update (ESCU) | New Releases

In January, the Splunk Threat Research Team had one release of new security content via the Splunk ES Content ...

Expert Tips from Splunk Professional Services, Ensuring Compliance, and More New ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Observability Release Update: AI Assistant, AppD + Observability Cloud Integrations & ...

This month’s releases across the Splunk Observability portfolio deliver earlier detection and faster ...