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 f...
See more...
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.