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.
below might help you.