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

[njohnson7]

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?

[somesoni2 · Accepted solution]

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]

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

[pandasplunk84]

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 *

[njohnson7]

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

[richgalloway]

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

[niketn]

@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

[njohnson7]

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.

[njohnson7]

@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

[tiagofbmm]

How about

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

[njohnson7]

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

[niketn]

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

[njohnson7]

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