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!

Dashboards: Hiding charts while search is being executed and other uses for tokens

There are a couple of features of SimpleXML / Classic dashboards that can be used to enhance the user ...

Splunk Observability Cloud's AI Assistant in Action Series: Explaining Metrics and ...

This is the fourth post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how ...

Brains, Bytes, and Boston: Learn from the Best at .conf25

When you think of Boston, you might picture colonial charm, world-class universities, or even the crack of a ...