topic How do I aggregate events using 2 different timestamps? in Splunk Search

How do I aggregate events using 2 different timestamps?

magenta — Mon, 11 Apr 2016 17:06:53 GMT

I am looking to "segment" operational changes(events) based on both the CLOSEDDATE & OPENDATE and essentially calculate the total number of changes in the month of March based on CLOSEDATE into one column and then OPENDATE for another column

I have created a stats table based on OPENDATE where I segment the events into different time buckets:

| eval _time=strptime(OPENDATETIME,"%Y-%m-%d %H:%M:%S")

Can I run a stats command on the events indexed using OPENDATE and then use appendcols to filter events that occurred in the last month using CLOSEDDATE?

Or is there a better way to do this?

Re: How do I aggregate events using 2 different timestamps?

sundareshr — Mon, 11 Apr 2016 18:52:43 GMT

Assuming you events will have either the OPENDATETIME OR CLOSEDDATETIME, you could do

index=* | eval openmonth=strftime(OPENDATETIME, "%m") | eval closedmonth=strftime(OPENDATETIME, "%m") | where openmonth="04" OR closedmonth="04" | stats count(OPENDATETIME) as open count(CLOSEDDATETIME) as closed

Re: How do I aggregate events using 2 different timestamps?

somesoni2 — Mon, 11 Apr 2016 19:12:01 GMT

Do both CLOSEDATE and OPENDATE field appear on the same event?? Are you looking for how many new changes opened in March and how many closed in March?

Re: How do I aggregate events using 2 different timestamps?

woodcock — Tue, 12 Apr 2016 05:53:17 GMT

Ignore any feelings about implementation approaches: What exactly are you trying to do? Show sample events, and desired output.

Re: How do I aggregate events using 2 different timestamps?

magenta — Tue, 12 Apr 2016 17:34:39 GMT

Each event has both OPENDATETIME & CLOSEDDATETIME so i'm looking to actually create a table by business process that includes the following fields:
1) changes opened in march (based on opendate)
2) changes opened in march leading to errors (based on opendate)
2) changes opened in march (based on closeddate)

All shown by Business process - so the question is can i use STATS to count by OPENDATE (using _time=OPENDATETIME) and then index by CLOSEDDATETIME and count again?

Re: How do I aggregate events using 2 different timestamps?

somesoni2 — Tue, 12 Apr 2016 19:11:46 GMT

I would try like this

your base search | eval OpenMonth=strftime(strptime(OPENDATETIME,"%Y-%m-%d %H:%M:%S") ,"%Y-%m") | eval ClosedMonth=strftime(strptime(CLOSEDATETIME,"%Y-%m-%d %H:%M:%S") ,"%Y-%m") | eval OpenedInMarch=if(OpenMonth="2016-03",1,0) | eval ClosedInMarch=if(ClosedMonth="2016-03",1,0) | stats sum(OpenedInMarch) as OpenedInMarch sum(ClosedInMarch) as ClosedInMarch