How to group the count of daily events by their month?

[csar5634]

Hi and just reaching out as stumped. Very grateful for assistance. This query returns the following in the statistics tab:

index="ds" (tags_rule="Jason" OR tags_rule="Bill" OR tags_rule=”Smithy”)
| timechart span=1d dc(Device_Name) as Number_of_Devices by tags_rule

The next step i'd like to do is then count up all the values in the columns and group them by the respective month. So it would look like the below. Just not having any luck figuring out the right query. Thanks in advance!

[bowesmana · Accepted solution]

@csar5634 You are using DC to calculate unique devices per day but want the total by month, i.e. in your example Jason has a total of 4. If that is the same device you still want SUM of those DC counts?

If it's the same device or different devices you still want the result to be 4?

If you want that to be 4 then just add the timechart 

| timechart span=1mon sum(*) as *

if you then want to change the date format to be MMM-YY instead of YYYY-MM just add this to the end

| fieldformat _time=strftime(_time, "%b-%y")

if you want the DC() to reflect unique devices in the whole month then change the initial span to span=1mon

[csar5634]

Thanks for responding. I couldn't get the output from the modification you gave, apols if my question wasn't clear enough. I appreciate you responding.

[csar5634]

Awesome, this one worked. So simple in the end. Thanks for the prompt response.

[bowesmana]

Which one worked? Can you accept the one that worked, so the solution as there are multiple suggestions - it's not clear if you don't see the hierarchy, which one you are replying to.

[csar5634]

Hey and i accepted your's as the solution. It should be noted as such. Thanks.

[PickleRick]

1. You don't need to timechart per day if you want to finally aggregate by month 🙂

2. Render a month from the date using

| eval month=strftime(_time,"%m")

And now you can use that field to split your stats by

| stats dc(Device_Name) as 'Number of Devices' by tags_rule month

So your search effectively looks like this:

index="ds" (tags_rule="Jason" OR tags_rule="Bill" OR tags_rule=”Smithy”)
| eval month=strftime(_time,"%m")
| stats dc(Device_Name) as 'Number of Devices' by tags_rule month

Finally you can do xyseries to print it in a tabular form if you want.

Theoretically, you could use the default time-related fields but I don't trust them.