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

[muradgh]

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:

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

[gcusello]

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

[muradgh]

Hi @gcusello 

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

[gcusello · Accepted solution]

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]

Hi @gcusello 

Thank you for your help. 👍👍

[SinghK]

Just with a small correction 

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

[muradgh]

Hi @SinghK 

Thanks for the note.