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?

Tags (4)
1 Solution
Highlighted

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

SplunkTrust

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

---
If this reply helps you, an upvote would be appreciated.
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

Highlighted

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

Influencer

``````| chart count(events) as "Events" over date_month by Name, Type
``````
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.

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!!!"

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.

Highlighted

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

Legend

``````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!!!"

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.

Highlighted

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

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 *
``````