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
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
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
Hi @gcusello
Unfortunately, this search did not provide me with what I wanted, check the below screenshot for the result of your search:
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
Hi @gcusello
Thank you for your help. 👍👍
Just with a small correction
index=* |stats count first(_raw) ….. rest is same. I think autocorrect messed it up.
Hi @SinghK
Thanks for the note.