How can I split the values in the stats table. I have used in combination of stats Values() count() by host

[nawazns5038]

index=* | stats values(source),values(sourcetype),count(sourcetype) by host ....query i used

host values(source) values(sourcetype) count(sourcetype)
xyz WinAuthentication_Security.log test 54971
sysmonitor.log test2

The value 54971 is the combined value of all both the sourceype . I want that total to be split accordingly. something like this:

host values(source) values(sourcetype) count(sourcetype)
xyz WinAuthentication_Security.log test 4000
sysmonitor.log test2 1971

Can you please suggest.

[lguinn2]

You could do this

index=* 
| stats values(source) as source count by host sourcetype

If that is not the format that you want, then you could do something like this:

index=* 
| stats values(source) as source count by host sourcetype
| stats values(source) as source list(sourcetype) as sourcetype list(count) as totals_by_sourcetype by host

[nawazns5038]

Hi lguinn ,

Thanks for the query. I want the count values to be split in the individual row rather than mixing everything into single row.

I want to see the individual count of the sourcetype for the respective row.

[lguinn2]

I don't think I understand your comment. The first search will split the counts by sourcetype; I think that is what you asked for. But maybe you want this:

index=* 
 | stats count by host sourcetype source

In which case, the following will be at least 10x faster:

| tstats count where index=* by host sourcetype source