topic Re: search results sum count by date? in Splunk Search

search results sum count by date?

abovebeyond — Fri, 18 Dec 2015 09:55:51 GMT

Hi,
Im trying to sum results by date:

CreatedDate ------ count
2015-12-2 ------ 1
2015-12-1 ------ 4
2015-11-30 ------ 5
2015-11-29 ------ 2

i want to count how much in each month, how can i do it?

Thanks!

Re: search results sum count by date?

jmallorquin — Fri, 18 Dec 2015 10:15:34 GMT

|rex "\d{4}\-(?<month>[^\-]+)"
|rex "\-\-\-\-\-\-\s+(?<count>\d+)"
|stats sum(count) by month

Re: search results sum count by date?

abovebeyond — Fri, 18 Dec 2015 10:30:46 GMT

hmm i forgot to mention , this is a db connect query

the results from the DB , its not parsed so date_month isn't working

any other options ?

Re: search results sum count by date?

dcarmack_splunk — Fri, 18 Dec 2015 13:41:20 GMT

try this

| bucket span=1mon CreatedDate
| stats sum(count) AS total_count by CreatedDate

Re: search results sum count by date?

yannK — Sat, 19 Dec 2015 17:13:52 GMT

If the field CreatedDate is not detected as a valid date, you can convert it.
see http://docs.splunk.com/Documentation/Splunk/6.3.1511/SearchReference/Convert

<mysearch>
| convert timeformat="%Y-%m-%d" ctime(CreatedDate) AS NewCreatedDate
| bucket span=1month NewCreatedDate
 | stats sum(count) AS total_count by NewCreatedDate

Re: search results sum count by date?

abovebeyond — Thu, 24 Dec 2015 10:28:55 GMT

Hi yannK , tried it without a success

NewCreatedDate shows nothing...

Any other suggestions ?

Thanks !

Re: search results sum count by date?

fdi01 — Tue, 29 Sep 2020 08:17:03 GMT

if count and CreateDate fields exit after you run your_DB_search try simplily:

<your_DB_search>| timechart span=1months sum(count) by CreatedDate usenull=f useother=f