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
SplunkTrust
SplunkTrust

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
SplunkTrust
SplunkTrust

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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...