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

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

njohnson7 — Fri, 16 Mar 2018 13:02:39 GMT

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?

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

richgalloway — Fri, 16 Mar 2018 13:18:35 GMT

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

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

njohnson7 — Fri, 16 Mar 2018 13:33:33 GMT

@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

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

tiagofbmm — Fri, 16 Mar 2018 13:38:56 GMT

How about

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

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

njohnson7 — Fri, 16 Mar 2018 17:10:58 GMT

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

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

niketn — Fri, 16 Mar 2018 19:07:34 GMT

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

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

somesoni2 — Fri, 16 Mar 2018 19:10:21 GMT

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 *

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

niketn — Fri, 16 Mar 2018 19:10:37 GMT

@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

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

njohnson7 — Sat, 17 Mar 2018 05:27:51 GMT

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

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

njohnson7 — Tue, 20 Mar 2018 07:49:52 GMT

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

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

njohnson7 — Wed, 21 Mar 2018 04:48:32 GMT

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.

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

pandasplunk84 — Wed, 17 Oct 2018 19:47:45 GMT

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

njohnson7 — Sat, 09 Mar 2019 09:25:25 GMT

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