Splunk Enterprise

Need to find OpenDate and CloseDate of an Event

vengat4043
Path Finder

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_NoAlert_IDWindFarmTurbineCategoryParameterWTG_valuefarm_avgCreatedDateDurationDur_Str
1TE-43065KirvereGKA42HighSlipRingTemperature45.9933.19/4/202000
2TE-42243KirvereGKA42HighSlipRingTemperature46.0232.739/3/202011
3TE-41336KirvereGKA42HighSlipRingTemperature46.0232.649/2/202021
4TE-39260KirvereGKA42HighSlipRingTemperature46.131.668/31/202042
5TE-38213KirvereGKA42HighSlipRingTemperature46.0132.728/30/202051
6TE-37103KirvereGKA42HighSlipRingTemperature45.9733.918/29/202061
7TE-36017KirvereGKA42HighSlipRingTemperature45.9634.558/28/202071
8TE-34988KirvereGKA42HighSlipRingTemperature45.9434.818/27/202081
9TE-33969KirvereGKA42HighSlipRingTemperature45.9434.918/26/202091
10TE-33042KirvereGKA42HighSlipRingTemperature45.9534.668/25/2020101
11TE-32112KirvereGKA42HighSlipRingTemperature45.97348/24/2020111
12TE-31177KirvereGKA42HighSlipRingTemperature45.9933.218/23/2020121
13TE-30189KirvereGKA42HighSlipRingTemperature46.0132.748/22/2020131
14TE-29007KirvereGKA42HighSlipRingTemperature4632.658/21/2020141
15TE-27658KirvereGKA42HighSlipRingTemperature45.9833.258/20/2020151
16TE-26334KirvereGKA42HighSlipRingTemperature45.9733.938/19/2020161
17TE-25039KirvereGKA42HighSlipRingTemperature45.9733.938/19/2020160
18TE-23723KirvereGKA42HighSlipRingTemperature45.9834.188/18/2020171
19TE-22307KirvereGKA42HighSlipRingTemperature45.9734.288/17/2020181
20TE-21016KirvereGKA42HighSlipRingTemperature45.9834.048/16/2020191
21TE-19738KirvereGKA42HighSlipRingTemperature4633.68/15/2020201
22TE-18365KirvereGKA42HighSlipRingTemperature4633.088/14/2020211
23TE-17108KirvereGKA42HighSlipRingTemperature45.9733.418/13/2020221
24TE-15941KirvereGKA42HighSlipRingTemperature45.9833.368/12/2020231
25TE-14800KirvereGKA42HighSlipRingTemperature45.9733.88/11/2020241
26TE-13633KirvereGKA42HighSlipRingTemperature45.9634.488/10/2020251
27TE-12587KirvereGKA42HighSlipRingTemperature46.0134.638/9/2020261
28TE-11483KirvereGKA42HighSlipRingTemperature46.0334.228/8/2020271
29TE-10278KirvereGKA42HighSlipRingTemperature46.0334.218/7/2020281
30TE-9178KirvereGKA42HighSlipRingTemperature46.0234.048/6/2020291
31TE-8156KirvereGKA42HighSlipRingTemperature46.0433.518/5/2020301
32TE-7065KirvereGKA42HighSlipRingTemperature45.9334.118/4/2020311

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_IDWindFarmTurbineCategoryParameterWTG_valuefarm_avgCreatedDateNo.ofDaysOpenOpenDateClosedDate

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

Thanks in Advance.

Labels (2)
0 Karma

thambisetty
SplunkTrust
SplunkTrust

below might help you.

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

————————————
If this helps, give a like below.
0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...