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?
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
Ignore any feelings about implementation approaches: What exactly are you trying to do? Show sample events, and desired output.
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?
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
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?