Need to find OpenDate and CloseDate of an Event

[vengat4043]

Dear Team, We are generating the Temporary ID based on the Parameter which is crossing beyond the Park Average. Here i have attached a scenario for a single Parameter. For each day alert Generating for a Particular Events. The code which we using,

index="alert_id" ID="KirvereGKA42SlipRingTemperatureHigh"
|dedup New_ID, Turbine, Alert_ID, Unique_ID, CreatedDate
|eval Today=strftime(relative_time(now(), "@d"), "%Y-%m-%d")
|eval Date_1 = strptime(CreatedDate,"%Y-%m-%d")
|eval Date_2 = strptime(Today,"%Y-%m-%d")
|eval Duration = round((Date_2 - Date_1)/86400)
|streamstats window=2 range(Date_1) as NDate by ID
|eval Dur_Str = round(NDate/86400)
|eval Combine = if(Dur_Str<=1,"Open","Closed")
|eval OpenDate = if(Combine="Open", CreatedDate, "")
|eval ClosedDate = if(Combine="Closed", CreatedDate, "")
| streamstats count as S_No by ID
|eval Close = case(Combine="Closed",S_No)
|eventstats values(eval(if(S_No >= Close, "Closed", "Open"))) as Status by S_No
|table S_No, Alert_ID, WindFarm, Turbine, Category, WindFarm, Parameter, WTG_value, farm_avg, CreatedDate, Duration, Dur_Str.

Output of the Code ,Please find the below screenshot for your reference.

S_No	Alert_ID	WindFarm	Turbine	Category	Parameter	WTG_value	farm_avg	CreatedDate	Duration	Dur_Str
1	TE-43065	Kirvere	GKA42	High	SlipRingTemperature	45.99	33.1	9/4/2020	0	0
2	TE-42243	Kirvere	GKA42	High	SlipRingTemperature	46.02	32.73	9/3/2020	1	1
3	TE-41336	Kirvere	GKA42	High	SlipRingTemperature	46.02	32.64	9/2/2020	2	1
4	TE-39260	Kirvere	GKA42	High	SlipRingTemperature	46.1	31.66	8/31/2020	4	2
5	TE-38213	Kirvere	GKA42	High	SlipRingTemperature	46.01	32.72	8/30/2020	5	1
6	TE-37103	Kirvere	GKA42	High	SlipRingTemperature	45.97	33.91	8/29/2020	6	1
7	TE-36017	Kirvere	GKA42	High	SlipRingTemperature	45.96	34.55	8/28/2020	7	1
8	TE-34988	Kirvere	GKA42	High	SlipRingTemperature	45.94	34.81	8/27/2020	8	1
9	TE-33969	Kirvere	GKA42	High	SlipRingTemperature	45.94	34.91	8/26/2020	9	1
10	TE-33042	Kirvere	GKA42	High	SlipRingTemperature	45.95	34.66	8/25/2020	10	1
11	TE-32112	Kirvere	GKA42	High	SlipRingTemperature	45.97	34	8/24/2020	11	1
12	TE-31177	Kirvere	GKA42	High	SlipRingTemperature	45.99	33.21	8/23/2020	12	1
13	TE-30189	Kirvere	GKA42	High	SlipRingTemperature	46.01	32.74	8/22/2020	13	1
14	TE-29007	Kirvere	GKA42	High	SlipRingTemperature	46	32.65	8/21/2020	14	1
15	TE-27658	Kirvere	GKA42	High	SlipRingTemperature	45.98	33.25	8/20/2020	15	1
16	TE-26334	Kirvere	GKA42	High	SlipRingTemperature	45.97	33.93	8/19/2020	16	1
17	TE-25039	Kirvere	GKA42	High	SlipRingTemperature	45.97	33.93	8/19/2020	16	0
18	TE-23723	Kirvere	GKA42	High	SlipRingTemperature	45.98	34.18	8/18/2020	17	1
19	TE-22307	Kirvere	GKA42	High	SlipRingTemperature	45.97	34.28	8/17/2020	18	1
20	TE-21016	Kirvere	GKA42	High	SlipRingTemperature	45.98	34.04	8/16/2020	19	1
21	TE-19738	Kirvere	GKA42	High	SlipRingTemperature	46	33.6	8/15/2020	20	1
22	TE-18365	Kirvere	GKA42	High	SlipRingTemperature	46	33.08	8/14/2020	21	1
23	TE-17108	Kirvere	GKA42	High	SlipRingTemperature	45.97	33.41	8/13/2020	22	1
24	TE-15941	Kirvere	GKA42	High	SlipRingTemperature	45.98	33.36	8/12/2020	23	1
25	TE-14800	Kirvere	GKA42	High	SlipRingTemperature	45.97	33.8	8/11/2020	24	1
26	TE-13633	Kirvere	GKA42	High	SlipRingTemperature	45.96	34.48	8/10/2020	25	1
27	TE-12587	Kirvere	GKA42	High	SlipRingTemperature	46.01	34.63	8/9/2020	26	1
28	TE-11483	Kirvere	GKA42	High	SlipRingTemperature	46.03	34.22	8/8/2020	27	1
29	TE-10278	Kirvere	GKA42	High	SlipRingTemperature	46.03	34.21	8/7/2020	28	1
30	TE-9178	Kirvere	GKA42	High	SlipRingTemperature	46.02	34.04	8/6/2020	29	1
31	TE-8156	Kirvere	GKA42	High	SlipRingTemperature	46.04	33.51	8/5/2020	30	1
32	TE-7065	Kirvere	GKA42	High	SlipRingTemperature	45.93	34.11	8/4/2020	31	1

From the above table we need to find the duration of the Particular Events, for that i have derived the day difference from today date and days difference between rows. What we want, you can see the above table from the below we are having the Events occurred from 20/07/2020(Open) to 26/07/2020(Closed) and its not happened till 03/08/2020. After that the same events happened on 04/08/2020(Open) to 31/08/2020(Closed) and its again started on 02/09/2020(Open) to till date. We need the output like the below table:

Alert_ID

WindFarm

Turbine

Category

Parameter

WTG_value

farm_avg

CreatedDate

No.ofDaysOpen

OpenDate

ClosedDate

Please Help me on this, how to we derive OPEN and Closed Status of the Particular Events based on the Date?

Thanks in Advance.

[thambisetty]

below might help you.

https://community.splunk.com/t5/Splunk-Enterprise-Security/How-to-calculate-for-what-duration-the-st...