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?
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 *
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 *
@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.
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 *
@pandasplunk84 "eval temp=Name."###".Type " this is concatenating the fields Name and Type with ### in the middle and forming a new field called temp.
Have you tried ... | stats count(events) as "Events" by Name, Type, month
?
@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
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.
@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
How about
| chart count(events) as "Events" over date_month by Name, Type
@tiagofbmm - I dont think we can use two fields after BY clause when using chart.
@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. 🙂
@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.