Archive

Count Events, Group by date field

Path Finder

I have data that looks like this that I'm pulling from a db. Each row is pulling in as one event:
trxn_id create_dt_tm
123456 2013-11-22 11:01:22.xxx
123457 2013-11-22 11:01:23.xxx
123458 2013-11-22 11:01:24.xxx
123459 2013-11-22 11:02:22.xxx
123460 2013-11-22 11:02:22.xxx

I'd like the results to look like this in a timechart:
Time Count
2013-11-22 11:01 3
2013-11-22 11:02 2
etc

When I do something like this below, I'm getting the results in minute but they are grouped by the time in which they were indexed.
"index=main | timechart count(edi_trxn_detail_id) span=1m"

How do I tell splunk to group by the create_dt_tm of the transaction and subsequently by minute? Thanks.

0 Karma
1 Solution

Path Finder

I think I may have figured this one out through suggestions and trial and error...

create_dt_tm | convert timeformat="%m/%d/%y %H:%M" ctime(create_dt_tm) as Minute | stats count as "Transactions" by Minute

This gives me the following which is what I was looking for. Thanks to those who helped!
Minute Transactions
11/24/13 10:00 8
11/24/13 10:01 6
11/24/13 10:02 4
etc

View solution in original post

0 Karma

Path Finder

I think I may have figured this one out through suggestions and trial and error...

create_dt_tm | convert timeformat="%m/%d/%y %H:%M" ctime(create_dt_tm) as Minute | stats count as "Transactions" by Minute

This gives me the following which is what I was looking for. Thanks to those who helped!
Minute Transactions
11/24/13 10:00 8
11/24/13 10:01 6
11/24/13 10:02 4
etc

View solution in original post

0 Karma

Revered Legend

Try following

index=main sourcetype=yoursourcetype| eval create_dt_tm=strptime(create_dt_tm,"%Y-%m-%d %H:%M") | stats count by create_dt_tm | eval create_dt_tm=strftime(create_dt_tm,"%Y-%m-%d %H:%M")
0 Karma

Path Finder

You could extract the values until the minutes in create_dt_tm field by using Rex Command.
With the New field you can simply make a timechart span=1m count by "newfield".

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!