Getting Data In

How do I list all sourcetypes for each host per index, provided one sourcetype=apache?

ArthurGautesen
Path Finder

I am trying to set up a stats output so that for each index, it lists all hosts, and for each of those hosts, it lists all sourcetypes.

This is provided that one of the sourcetypes (for that host) equals "apache".

Any suggestions?

0 Karma

wrangler2x
Motivator

If you just want to see what indexes you have, and what hosts are in them, and what sourcetypes are being logged, this search works well (Include the pipe in the search):

| tstats values(host) AS Host, values(sourcetype) AS Sourcetype WHERE index=* by index

ArthurGautesen
Path Finder

That is working, but it still displays like a table. can it display like the following (without the dashes) providing sourcetype1 is among the different sourcetypes for each host, in each index. instead of a table, you have a nested set of stat displays (sourcetypes for each host, hosts for each index, provided at least one sourcetype=sourcetype1)

index1---------hostA---------sourcetype1
-----------------------------------sourcetype2
-----------------------------------sourcetype3
------------------hostB---------sourcetype1
-----------------------------------sourcetype4
-----------------------------------sourcetype7
------------------hostC---------sourcetype1
-----------------------------------sourcetype4
index2---------hostD---------sourcetype1
-----------------------------------sourcetype5
-----------------------------------sourcetype6
index3---------hostE---------sourcetype1
-----------------------------------sourcetype5
-----------------------------------sourcetype7

0 Karma

wrangler2x
Motivator

I have not been able to figure out how to get the effect you desire here, but this search will associate the sourcetypes with the host correctly, but it causes the index name to be redundantly added.

| tstats values(sourcetype) AS Sourcetype dc(sourcetype) AS #sourcetypes WHERE index=* by index, host

If you don't want the count of sourcetypes I added to it, just use this:

| tstats values(sourcetype) count WHERE index=* by index, host

And then here is a search which adds to each sourcetype shown the number of hosts that are associated with it:

| tstats values(host) AS Host dc(host) as count by sourcetype,index | eval sourcetype=sourcetype." - ".count | stats list(sourcetype) as "sourcetype-hostCount" by index, Host

wrangler2x
Motivator

Enter that last one all on one line. After I posted this it looks like two searches, but it is one.

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Try something like this

| tstats count WHERE index=* by index host sourcetype | eval flag=if(sourcetype="apache",1,0) | eventstats sum(flag) as flag by host | where flag=1 | fields - count

ArthurGautesen
Path Finder

I copied it right to the search bar as is, and it gave the error I listed using enterprise version 6.3

0 Karma

ArthurGautesen
Path Finder

I tried this, and a few variations, but it gives back "Error in 'tstats' command: This command must be the first command of a search"...

0 Karma

somesoni2
SplunkTrust
SplunkTrust

How are you using this search? As you can see tstats is the first command being executed in above search, so it does work for me without any issues.

0 Karma
Get Updates on the Splunk Community!

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...