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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

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