Splunk Search
Highlighted

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

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
Highlighted

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

SplunkTrust
SplunkTrust

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

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

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

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
Highlighted

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

Influencer

How about

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

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

Path Finder

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

0 Karma
Highlighted

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

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. 🙂




| eval message="Happy Splunking!!!"


0 Karma
Highlighted

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

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
Highlighted

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

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



| eval message="Happy Splunking!!!"


0 Karma
Highlighted

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

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
Highlighted

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

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