Counting distinct field values and dislaying count and value together

[Sqig]

Hi. Been trying to work this one out for hours... I'm close!!!

We are Splunking data such that each Host has a field "SomeText" which is some arbitrary string, and that string may be repeated on that host any number of times. It may also appear on other hosts... Basically, think of something like a syslog file... your crond message can be any number of different strings.

Let's say that Host1 has the following strings:

"The quick brown fox" shows up 5 times

"jumps over the" shows up 2 times

"lazy dog" shows up 10 times

"My dog has fleas" shows up 2 times

"So does yours" also shows up 2 times

I want a chart that shows me:

Host1	10	"lazy dog"
	5	"The quick brown fox"
	2	"jumps over the"
	2	"My dog has fleas"
	2	"So does yours"

But what I GET is this:

Host1	1	"lazy dog"
	2	"The quick brown fox"
	5	"jumps over the"
		"My dog has fleas"
		"So does yours"

(I think the string column is actually sorted alphabetically).

This is a mockup of the search I'm running, with field names obviously simplified:

index=myindex earliest=-24h | stats count(SomeText) as textCount by SomeText host | stats values(textCount) as Count,values(SomeText) as "Text" by host

What am I missing? How can I marry up the # of times a message appears with that message?

Thanks for any ideas.

[kristian_kolb · Accepted solution]

The reason for your table not showing up as you want is that the values function does not have any connection with the count.

Wouldn't something like this work?

index=myindex earliest=-24h | top 10 SomeText by host

or if you really want all possible values and counts for SomeText

index=myindex earliest=-24h | stats count by SomeText host | sort - count

BR,

Kristian

[Sqig]

Wow, I feel like a dope! I was thinking about this in way more complex terms than I needed to. Probably because I started with something that just gave me each host and then a list of its SomeText field contents...then wanted to add one more element to that. So I was working with one query and trying to add to it.

This solution, of course, works. Thanks. The only thing I don't like about it is that the host name appears on every line. I'll work with it and see if I can make it appear only once.