Splunk Search

Can you help us with a basic search that uses the stats command?

jip31
Motivator

Hi,

With the code below, I count the event number by source for a sourcetype.

But different sources use the same sourcetype.

So I have the sourcetype field reproduced many times.

I just want to count the events number by source for only one sourcetype.

index=x| stats count by sourcetype, source

I tried this but it doesn't do what I want

index="x" sourcetype=* host=* 
| stats values(source) by sourcetype| stats count by sourcetype, source

could you help me please?

Tags (2)
0 Karma
1 Solution

FrankVl
Ultra Champion

"I need to have a table with a column with the sourcetype name, a column linked to the first column in order to have all the sources for a specific sourcetype and a last column with the count of events by source...."

Your original attempt was close, but you need to combine both into 1 stats command, like this:

index="x"
| stats count values(source) by sourcetype

Edit: Just realized I read your requirement wrong. You need the count by source, not by sourcetype, so above would not really work. But your requirements are then conflicting. You want the count by sourcetype,source, but for some reason have a problem with repeating the sourcetype value for each related source.

This should do the trick:

 | stats count by source,sourcetype
 | stats list(source) as source list(count) as count by sourcetype

View solution in original post

0 Karma

FrankVl
Ultra Champion

"I need to have a table with a column with the sourcetype name, a column linked to the first column in order to have all the sources for a specific sourcetype and a last column with the count of events by source...."

Your original attempt was close, but you need to combine both into 1 stats command, like this:

index="x"
| stats count values(source) by sourcetype

Edit: Just realized I read your requirement wrong. You need the count by source, not by sourcetype, so above would not really work. But your requirements are then conflicting. You want the count by sourcetype,source, but for some reason have a problem with repeating the sourcetype value for each related source.

This should do the trick:

 | stats count by source,sourcetype
 | stats list(source) as source list(count) as count by sourcetype
0 Karma

tsaikumar009
Explorer

index=yourindex sourcetype="sourcetype you are looking for " | stats count by source

another way

index=yourindex | stats count by source | search sourcetype="sourcetype you are looking for "

0 Karma

renjith_nair
Legend

@jip31 ,

If you want to group all sources by sourcetype, try this

index="your index"
| stats count by sourcetype,source
| stats values(source) as source,values(count) as count by sourcetype

Updated
In the above result, source and count might not have a 1-1 mapping since the values will sort them lexicographically .
Try instead

    | stats count by source,sourcetype
    | eval combined=source." | ".count
    | stats values(combined) as source by sourcetype
---
What goes around comes around. If it helps, hit it with Karma 🙂
0 Karma

FrankVl
Ultra Champion

Note that this way you have no way of telling which count belongs to which source, as values() does a lexicographical sort.

jip31
Motivator

perfect renjith many thanks

0 Karma

renjith_nair
Legend

@jip31 , What @FrankVl mentioned is absolutely right. You might not have a 1-1 mapping between source & count. If you need this try something like this

| stats count by source,sourcetype
| eval combined=source." | ".count
| stats values(combined) as source by sourcetype

I will update the answer and would "unaccept" it.

---
What goes around comes around. If it helps, hit it with Karma 🙂
0 Karma

FrankVl
Ultra Champion

Basically his requirements are conflicting. He wants count by sourcetype,source, but without repeating the sourcetype value each time.

Now, you can of course pull tricks like combining source and count in 1 field. Or something like this:

| stats count by source,sourcetype
| stats list(source) as source list(count) as count by sourcetype

(list keeps the original order)

But I'm quite curious why a simple | stats count by source,sourcetype wouldn't be OK.

0 Karma

damann
Communicator

Could you provide an example, screenshot or some events and try to describe the desired outcome with their values?
For me your SPL is answering your question already.

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@jip31

Are you looking for these?

| chart count over sourcetype by source

OR

| chart count over source by sourcetype
0 Karma

jip31
Motivator

not exactly
I need to have a table with a column with the sourcetype name, a column linked to the first column in order to have all the sources for a specific sourcetype and a last column with the count of events by source....

0 Karma
Get Updates on the Splunk Community!

Thanks for the Memories! Splunk University, .conf24, and Community Connections

Thank you to everyone in the Splunk Community who joined us for .conf24 – starting with Splunk University and ...

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...