Splunk Search

Split the total count in the rows per month and show the count under each months

njohnson7
Path Finder

Hallo,

I am trying to find the total number of different types of events per month(chronologically) and the sum of events per month , in short I am trying to achieve the below result without pivot -->

Name Type Jan-17 Feb-17 Mar-17....... Dec 2017
A IN 10 20 30 ........ 15
SR 15 12 14 ........ 26

B IN 10 11 16 ....... 22
SR 5 14 12 ......... 20
30(Total) .........72(total).........83(total)

index=*...
| fields Name, events, Type
| dedup events
| eval month=strftime(_time,"%b-%Y")
| stats count(events) as "Events" by Name, Type

I am able to formulate the results only until a point.

Name Type Events
A IN 45
A SR 40
B IN 40
B SR 26

Events is the total sum of events per month, I want to find out a way to split it up somehow to achieve the desired results. Is it possible?

0 Karma
1 Solution

somesoni2
Revered Legend

Give this a try

index=*...
| fields Name, events, Type 
| dedup events
| eval month=strftime(_time,"%b-%Y") 
| stats count(events) as "Events" by Name, Type month 
| eval temp=Name."###".Type 
| chart sum(Events) over temp by month
| rex field=temp "(?<Name>.+)###(?<Type>.+)"
| fields - temp | table Name Type *

View solution in original post

somesoni2
Revered Legend

Give this a try

index=*...
| fields Name, events, Type 
| dedup events
| eval month=strftime(_time,"%b-%Y") 
| stats count(events) as "Events" by Name, Type month 
| eval temp=Name."###".Type 
| chart sum(Events) over temp by month
| rex field=temp "(?<Name>.+)###(?<Type>.+)"
| fields - temp | table Name Type *

njohnson7
Path Finder

@somesoni2 , thanks a lot . almost there 🙂

When the Months were plotted as Column Headers, it was not getting sorted.
So I had to use | eval month=strftime(_time,"%Y-%m (%B-%Y)") to get it sorted.

Now after plotting the table, the final layout looks like this -->

Name Type 2017-02 (February-2017) 2017-03 (March-2017) 2017-04 (April-2017)
Name1 IN 27 15 3
Name 1 SR 1 6 3
Name1 P 21 41 39

Name2 IN 27 15 3
Name 2 SR 1 6 3
Name2 P 21 41 39

1) If you see the values in the Name column remains the same for a group of data contained in the different months(column headers), is there anyway to list that value once for a group of data .. read about mvcombine but I was not able to figure out how to use it?

2) If we use eval month=strftime(_time,"%b-%Y") --> is there anyway to sort the months coming as column headers, because this time format is pleasing visually.

0 Karma

pandasplunk84
Engager

Please explain line 5
index=*...
| fields Name, events, Type
| dedup events
| eval month=strftime(_time,"%b-%Y")
| stats count(events) as "Events" by Name, Type month
| eval temp=Name."###".Type
| chart sum(Events) over temp by month
| rex field=temp "(?.+)###(?.+)"
| fields - temp | table Name Type *

0 Karma

njohnson7
Path Finder

@pandasplunk84 "eval temp=Name."###".Type " this is concatenating the fields Name and Type with ### in the middle and forming a new field called temp.

0 Karma

richgalloway
SplunkTrust
SplunkTrust

Have you tried ... | stats count(events) as "Events" by Name, Type, month?

---
If this reply helps you, Karma would be appreciated.
0 Karma

niketn
Legend

@njohnson7, how about the following?

index=*...
| fields Name, events, Type 
| dedup events
| eval month=strftime(_time,"%b-%Y") 
| eval key=Name."-". Type
| chart count(events) as "Events" over key by month
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

njohnson7
Path Finder

Thanks @niketnilay , I didnt see this comment as it was not showing unless expanded, obviously this comment was the first steps to what I wanted to achieve 🙂 Hope there is someway to go about the two questions I asked in my last comment.

0 Karma

njohnson7
Path Finder

@richgalloway - Yes ofcourse I had tried that, It gave me the months and events as row items only .

Name Type Events Months
A IN 45 Apr
A SR 40 Apr
B IN 40 Apr
B SR 26 Apr

0 Karma

tiagofbmm
Influencer

How about

| chart count(events) as "Events" over date_month by Name, Type
0 Karma

njohnson7
Path Finder

@tiagofbmm - I dont think we can use two fields after BY clause when using chart.

0 Karma

niketn
Legend

@njohnson7, if over clause is not used, then two fields can be used with chart in by clause and first one in the by clause is considered as over. 🙂

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

njohnson7
Path Finder

@niketnilay - Sure 🙂 But @tiagofbmm mentioned about using two fields after a by clause even with an over close in the query. I was just referring to that.

0 Karma
Get Updates on the Splunk Community!

Splunk Decoded: Service Maps vs Service Analyzer Tree View vs Flow Maps

It’s Monday morning, and your phone is buzzing with alert escalations – your customer-facing portal is running ...

What’s New in Splunk Observability – September 2025

What's NewWe are excited to announce the latest enhancements to Splunk Observability, designed to help ITOps ...

Fun with Regular Expression - multiples of nine

Fun with Regular Expression - multiples of nineThis challenge was first posted on Slack #regex channel ...