Splunk Search

Help with search that creates Table with host, sourcetype, sample event or log

muradgh
Path Finder

Hi Splunkers,

I need to make a statistical table to show me the hosts and each sourcetype that it generates and the count for each sourcetype with a column that calculates the total count and most importantly a column with a sample event from each sourcetype.

I want it to be something like the attached table:

muradgh_0-1654077564480.png

Can someone please help me with the search that provides me with such a table?

I have tried to make such a table using the following command (without the raw log):

| tstats values(sourcetype) count where index=* by host
| sort - count

but the above search counts only the total of all the sourcetypes

Then I have tried a different search:

index=* | chart count OVER host BY sourcetype useother=false limit=0

but again this is not an accurate search for what I want.

 

 

Much Thanks 

Murad Ghazzawi

 

Labels (5)
0 Karma
1 Solution

gcusello
SplunkTrust
SplunkTrust

Hi @muradgh ,

please try this:

index=*
| stats count first(_row) AS sample_event BY host sourcetype
| appendpipe [ | stats sum(count) AS total BY host | eval sourcetype="ZZTotal" ]
| sort host sourcetype
| eval sourcetype=if(sourcetype="ZZTotal","Total",sourcetype)
| table host sourcetype count total sample_event

 Ciao.

Giuseppe

View solution in original post

gcusello
SplunkTrust
SplunkTrust

Hi @muradgh,

you could try something like this:

index=*
| stats count values(total) AS total last(_row) AS sample_event BY host sourcetype
| appendpipe [ | stats sum(count) AS total BY host | eval sourcetype="Total" ]
| table host sourcetype count total sample_event

Ciao.

Giuseppe

0 Karma

muradgh
Path Finder

Hi @gcusello 

Unfortunately, this search did not provide me with what I wanted, check the below screenshot for the result of your search:

 

muradgh_0-1654080337051.png

 

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @muradgh ,

please try this:

index=*
| stats count first(_row) AS sample_event BY host sourcetype
| appendpipe [ | stats sum(count) AS total BY host | eval sourcetype="ZZTotal" ]
| sort host sourcetype
| eval sourcetype=if(sourcetype="ZZTotal","Total",sourcetype)
| table host sourcetype count total sample_event

 Ciao.

Giuseppe

muradgh
Path Finder

Hi @gcusello 

Thank you for your help. 👍👍

0 Karma

SinghK
Builder

Just with a small correction 

index=* |stats count first(_raw) ….. rest is same. I think autocorrect messed it up.

muradgh
Path Finder

Hi @SinghK 

Thanks for the note.

0 Karma
Get Updates on the Splunk Community!

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

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...