This website uses cookies. By clicking Accept, you consent to the use of cookies. Click Here to learn more about how we use cookies.
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for
NetWitness Discussions
- NetWitness Community
- Discussions
- Trend Analysis version 2 with Netwitness
-
Options
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
Trend Analysis version 2 with Netwitness
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
2017-09-22 07:23 AM
Following on from the work of
I have had a go at improving the model. The benefits of the approach below are:
- The model updates much quicker and does not have to wait until the end of the context
- You can define working hours and Bank Holidays in the model
All feedback appreciated below.
//Update our Baseline Model every Hour
CREATE VARIABLE integer Mylearning_Days_BHB = 0;
/* the alert is triggered when the number of events is mean+/-MyanomalyStddevTimes_BHB*sttdev */
CREATE VARIABLE double MyanomalyStddevTimes_BHB = 3.0;
CREATE CONTEXT baselineBusinessHoursBHcontextEvery1Hour INITIATED @now AND PATTERN [every timer:at(*/5, *, *, *, *)] TERMINATED AFTER 1 min;
CREATE CONTEXT baselineBusinessHoursBHcontextEvery1Day INITIATED @now AND PATTERN [every timer:at(*/5, *, *, *, *)] TERMINATED AFTER 1 min;
@RSAPersist
create window baselineBusinessHoursBH1HourData.win:time(28 days) (metaKey string, metaValue string, HourEventCountTotal integer, timestamp string, MyDayOfMonth integer, MyHourOfDay integer,MyDayOfWeek integer,isWorkingHours boolean);
@RSAPersist
create window baselineBusinessHoursBH1DayData.win:time(28 days) (metaKey string, metaValue string, DayEventCountTotal integer, timestamp string, MyDayOfMonth integer,MyDayOfWeek integer,isWorkingHours boolean);
CREATE WINDOW baselineBusinessHoursBH_HOURS.std:groupwin(metaKey, metaValue, MyHourOfDay,MyDayOfWeek).win:length(1)(metaKey string, metaValue string, MyHourOfDay integer, MyDayOfWeek integer,myMean double,myMedian double, myStddev double, cvMean double, cvMedian double,myMin integer, myMax integer,timestamp string,isWorkingHours boolean);
CREATE WINDOW baselineBusinessHoursBH_DAYS.std:groupwin(metaKey, metaValue,MyDayOfWeek).win:length(1)(metaKey string, metaValue string, MyDayOfWeek integer,myMean double,myMedian double, myStddev double, cvMean double, cvMedian double,myMin integer, myMax integer,timestamp string,isWorkingHours boolean);
CREATE WINDOW baselineBusinessHoursBH_ALL.std:groupwin(metaKey, metaValue).win:length(1)(metaKey string, metaValue string,myMean double,myMedian double, myStddev double, cvMean double, cvMedian double,myMin integer, myMax integer,timestamp string,isWorkingHours boolean);
@Name('baselineBusinessHoursBHGenerateByHour')
CONTEXT baselineBusinessHoursBHcontextEvery1Hour
INSERT INTO baselineBusinessHoursBH_HOURS
SELECT metaKey,metaValue,MyHourOfDay,MyDayOfWeek,isWorkingHours,
median(HourEventCountTotal) AS myMedian,
avg(HourEventCountTotal) AS myMean,
min(HourEventCountTotal) AS myMin,
max(HourEventCountTotal) AS myMax,
stddev(HourEventCountTotal) AS myStddev,
stddev(HourEventCountTotal)/median(HourEventCountTotal) AS cvMedian,
stddev(HourEventCountTotal)/avg(HourEventCountTotal) AS cvMean,
current_timestamp().format() AS timestamp
FROM baselineBusinessHoursBH1HourData
GROUP BY metaKey,metaValue,MyHourOfDay,MyDayOfWeek,isWorkingHours
HAVING count(*) >= Mylearning_Days_BHB
OUTPUT snapshot WHEN TERMINATED;
@Name('baselineBusinessHoursBHGenerateByDay')
CONTEXT baselineBusinessHoursBHcontextEvery1Day
INSERT INTO baselineBusinessHoursBH_DAYS
SELECT metaKey,metaValue,MyDayOfWeek,isWorkingHours,
median(DayEventCountTotal) AS myMedian,
avg(DayEventCountTotal) AS myMean,
min(DayEventCountTotal) AS myMin,
max(DayEventCountTotal) AS myMax,
stddev(DayEventCountTotal) AS myStddev,
stddev(DayEventCountTotal)/median(DayEventCountTotal) AS cvMedian,
stddev(DayEventCountTotal)/avg(DayEventCountTotal) AS cvMean,
current_timestamp().format() AS timestamp
FROM baselineBusinessHoursBH1DayData
GROUP BY metaKey,metaValue,MyDayOfWeek,isWorkingHours
HAVING count(*) >= Mylearning_Days_BHB
OUTPUT snapshot WHEN TERMINATED;
@Name('baselineBusinessHoursBHGenerateByAll')
CONTEXT baselineBusinessHoursBHcontextEvery1Day
INSERT INTO baselineBusinessHoursBH_ALL
SELECT metaKey,metaValue,isWorkingHours,
median(HourEventCountTotal) AS myMedian,
avg(HourEventCountTotal) AS myMean,
min(HourEventCountTotal) AS myMin,
max(HourEventCountTotal) AS myMax,
stddev(HourEventCountTotal) AS myStddev,
stddev(HourEventCountTotal)/median(HourEventCountTotal) AS cvMedian,
stddev(HourEventCountTotal)/avg(HourEventCountTotal) AS cvMean,
current_timestamp().format() AS timestamp
FROM baselineBusinessHoursBH1HourData
GROUP BY metaKey,metaValue,isWorkingHours
HAVING count(*) >= Mylearning_Days_BHB
OUTPUT snapshot WHEN TERMINATED;
// Generate Alerts Based Upon Anomalies
//Note the data for the current hour and day is constantly changing so we can only compare the previous hour or day period
@Name('baselineBusinessHoursBHAlertByHour')
@RSAAlert(oneInSeconds=0)
CONTEXT baselineBusinessHoursBHcontextEvery1Hour
SELECT
baselineBusinessHoursBH1HourData.metaKey AS metaKey,
baselineBusinessHoursBH1HourData.metaValue AS metaValue,
baselineBusinessHoursBH1HourData.HourEventCountTotal AS HourEventCountTotal,
baselineBusinessHoursBH1HourData.MyHourOfDay AS MyHourOfDay,
baselineBusinessHoursBH1HourData.MyDayOfWeek AS MyDayOfWeek,
baselineBusinessHoursBH1HourData.MyDayOfMonth AS MyDayOfMonth,
baselineBusinessHoursBH_HOURS.myMedian AS myMedian,
baselineBusinessHoursBH_HOURS.myStddev AS myStddev ,
baselineBusinessHoursBH_HOURS.cvMean AS CoefficientOfVarianceMean,
baselineBusinessHoursBH_HOURS.cvMedian AS CoefficientOfVarianceMedian
FROM baselineBusinessHoursBH1HourData, baselineBusinessHoursBH_HOURS
WHERE
baselineBusinessHoursBH1HourData.metaKey = baselineBusinessHoursBH_HOURS.metaKey
AND baselineBusinessHoursBH1HourData.metaValue = baselineBusinessHoursBH_HOURS.metaValue
AND baselineBusinessHoursBH1HourData.MyHourOfDay = baselineBusinessHoursBH_HOURS.MyHourOfDay
AND baselineBusinessHoursBH1HourData.MyDayOfWeek = baselineBusinessHoursBH_HOURS.MyDayOfWeek
AND (baselineBusinessHoursBH1HourData.HourEventCountTotal > (baselineBusinessHoursBH_HOURS.myMedian+MyanomalyStddevTimes_BHB*baselineBusinessHoursBH_HOURS.myStddev) OR baselineBusinessHoursBH1HourData.HourEventCountTotal < (baselineBusinessHoursBH_HOURS.myMedian-MyanomalyStddevTimes_BHB*baselineBusinessHoursBH_HOURS.myStddev))
AND (current_timestamp()-1000*60*60).getHourOfDay() = baselineBusinessHoursBH1HourData.MyHourOfDay
AND (current_timestamp()-1000*60*60).getDayofWeek() = baselineBusinessHoursBH1HourData.MyDayOfWeek
AND (current_timestamp()-1000*60*60).getDayofMonth() = baselineBusinessHoursBH1HourData.MyDayOfMonth
AND baselineBusinessHoursBH1HourData.MyHourOfDay=baselineBusinessHoursBH_HOURS.MyHourOfDay
AND baselineBusinessHoursBH1HourData.MyDayOfWeek=baselineBusinessHoursBH_HOURS.MyDayOfWeek
GROUP BY baselineBusinessHoursBH_HOURS.metaKey, baselineBusinessHoursBH_HOURS.metaValue,baselineBusinessHoursBH_HOURS.isWorkingHours
OUTPUT snapshot WHEN TERMINATED;
@Name('baselineBusinessHoursBHAlertByALL')
@RSAAlert(oneInSeconds=0)
CONTEXT baselineBusinessHoursBHcontextEvery1Hour
SELECT
baselineBusinessHoursBH1HourData.metaKey AS metaKey,
baselineBusinessHoursBH1HourData.metaValue AS metaValue,
baselineBusinessHoursBH1HourData.HourEventCountTotal AS HourEventCountTotal,
baselineBusinessHoursBH1HourData.MyHourOfDay AS MyHourOfDay,
baselineBusinessHoursBH1HourData.MyDayOfWeek AS MyDayOfWeek,
baselineBusinessHoursBH1HourData.MyDayOfMonth AS MyDayOfMonth,
baselineBusinessHoursBH_ALL.myMedian AS myMedian,
baselineBusinessHoursBH_ALL.myStddev AS myStddev ,
baselineBusinessHoursBH_ALL.cvMean AS CoefficientOfVarianceMean,
baselineBusinessHoursBH_ALL.cvMedian AS CoefficientOfVarianceMedian
FROM baselineBusinessHoursBH1HourData, baselineBusinessHoursBH_ALL
WHERE
baselineBusinessHoursBH1HourData.metaKey = baselineBusinessHoursBH_ALL.metaKey
AND baselineBusinessHoursBH1HourData.metaValue = baselineBusinessHoursBH_ALL.metaValue
AND (baselineBusinessHoursBH1HourData.HourEventCountTotal > (baselineBusinessHoursBH_ALL.myMedian+MyanomalyStddevTimes_BHB*baselineBusinessHoursBH_ALL.myStddev) OR baselineBusinessHoursBH1HourData.HourEventCountTotal < (baselineBusinessHoursBH_ALL.myMedian-MyanomalyStddevTimes_BHB*baselineBusinessHoursBH_ALL.myStddev))
AND (current_timestamp()-1000*60*60).getHourOfDay() = baselineBusinessHoursBH1HourData.MyHourOfDay
AND (current_timestamp()-1000*60*60).getDayofWeek() = baselineBusinessHoursBH1HourData.MyDayOfWeek
AND (current_timestamp()-1000*60*60).getDayofMonth() = baselineBusinessHoursBH1HourData.MyDayOfMonth
GROUP BY baselineBusinessHoursBH_ALL.metaKey, baselineBusinessHoursBH_ALL.metaValue,baselineBusinessHoursBH_ALL.isWorkingHours
OUTPUT snapshot WHEN TERMINATED;
@Name('baselineBusinessHoursBHAlertByDay')
@RSAAlert(oneInSeconds=0)
CONTEXT baselineBusinessHoursBHcontextEvery1Hour
SELECT
baselineBusinessHoursBH1DayData.metaKey AS metaKey,
baselineBusinessHoursBH1DayData.metaValue AS metaValue,
baselineBusinessHoursBH1DayData.DayEventCountTotal AS DayEventCountTotal,
baselineBusinessHoursBH1DayData.MyDayOfWeek AS MyDayOfWeek,
baselineBusinessHoursBH1DayData.MyDayOfMonth AS MyDayOfMonth,
baselineBusinessHoursBH_DAYS.myMedian AS myMedian,
baselineBusinessHoursBH_DAYS.myStddev AS myStddev ,
baselineBusinessHoursBH_DAYS.cvMean AS CoefficientOfVarianceMean,
baselineBusinessHoursBH_DAYS.cvMedian AS CoefficientOfVarianceMedian
FROM baselineBusinessHoursBH1DayData, baselineBusinessHoursBH_DAYS
WHERE
baselineBusinessHoursBH1DayData.metaKey = baselineBusinessHoursBH_DAYS.metaKey
AND baselineBusinessHoursBH1DayData.metaValue = baselineBusinessHoursBH_DAYS.metaValue
AND baselineBusinessHoursBH1DayData.MyDayOfWeek = baselineBusinessHoursBH_DAYS.MyDayOfWeek
AND (baselineBusinessHoursBH1DayData.DayEventCountTotal > (baselineBusinessHoursBH_DAYS.myMedian+MyanomalyStddevTimes_BHB*baselineBusinessHoursBH_DAYS.myStddev) OR baselineBusinessHoursBH1DayData.DayEventCountTotal < (baselineBusinessHoursBH_DAYS.myMedian-MyanomalyStddevTimes_BHB*baselineBusinessHoursBH_DAYS.myStddev))
AND (current_timestamp()-1000*60*60*24).getDayOfWeek() = baselineBusinessHoursBH1DayData.MyDayOfWeek
AND (current_timestamp()-1000*60*60*24).getDayofMonth() = baselineBusinessHoursBH1DayData.MyDayOfMonth
AND baselineBusinessHoursBH1DayData.MyDayOfWeek=baselineBusinessHoursBH_DAYS.MyDayOfWeek
GROUP BY baselineBusinessHoursBH_DAYS.metaKey, baselineBusinessHoursBH_DAYS.metaValue,baselineBusinessHoursBH_DAYS.isWorkingHours
OUTPUT snapshot WHEN TERMINATED;
//
//INPUTS - Feed Model Here
// Keep a Running Total for the Hour
ON Event(
user_dst IS NOT NULL
AND (((esa_time).getDayOfWeek IN (2,3,4,5,6) // Monday to Friday
AND (esa_time).getHourOfDay IN (8,9,10,11,12,13,14,15,16,17,18)) // 8:00 -18:00 UTC
AND NOT
(
(esa_time).getDayOfMonth IN (25,26) and (esa_time).getMonthOfYear IN (12) //25th 26th December
OR (esa_time).getDayOfMonth IN (1) and (esa_time).getMonthOfYear IN (1) //1st January
OR (esa_time).getDayOfMonth IN (30) and (esa_time).getMonthOfYear IN (3) //30th March Good Friday
OR (esa_time).getDayOfMonth IN (2) and (esa_time).getMonthOfYear IN (4) //2nd April
OR (esa_time).getDayOfMonth IN (7,28) and (esa_time).getMonthOfYear IN (5) // May Bank Holidays 2018
OR (esa_time).getDayOfMonth IN (27) and (esa_time).getMonthOfYear IN (8) //August Bank Holidays 2018
))
)
AS myevent
merge baselineBusinessHoursBH1HourData myBusinessHoursData
where myBusinessHoursData.metaValue=myevent.user_dst
AND myBusinessHoursData.MyHourOfDay=myevent.esa_time.getHourOfDay()
AND myBusinessHoursData.MyDayOfMonth=myevent.esa_time.getDayOfMonth()
when matched
then update set HourEventCountTotal = 1 + myBusinessHoursData.HourEventCountTotal, timestamp = current_timestamp().format()
when not matched
then insert
select 'user_dst' AS metaKey,
user_dst AS metaValue,
current_timestamp().getHourOfDay() AS MyHourOfDay,
current_timestamp().getDayOfMonth() AS MyDayOfMonth ,
current_timestamp().getDayOfWeek() AS MyDayOfWeek ,
current_timestamp().format() AS timestamp,
true AS isWorkingHours,
1 AS HourEventCountTotal;
//Keep a Running Total for the Day
ON Event(
user_dst IS NOT NULL
AND (((esa_time).getDayOfWeek IN (2,3,4,5,6) // Monday to Friday
AND (esa_time).getHourOfDay IN (8,9,10,11,12,13,14,15,16,17,18)) // 8:00 -18:00 UTC
AND NOT
(
(esa_time).getDayOfMonth IN (25,26) and (esa_time).getMonthOfYear IN (12) //25th 26th December
OR (esa_time).getDayOfMonth IN (1) and (esa_time).getMonthOfYear IN (1) //1st January
OR (esa_time).getDayOfMonth IN (30) and (esa_time).getMonthOfYear IN (3) //30th March Good Friday
OR (esa_time).getDayOfMonth IN (2) and (esa_time).getMonthOfYear IN (4) //2nd April
OR (esa_time).getDayOfMonth IN (7,28) and (esa_time).getMonthOfYear IN (5) // May Bank Holidays 2018
OR (esa_time).getDayOfMonth IN (27) and (esa_time).getMonthOfYear IN (8) //August Bank Holidays 2018
))
)
AS myevent
merge baselineBusinessHoursBH1DayData myBusinessHoursData
where myBusinessHoursData.metaValue=myevent.user_dst
AND myBusinessHoursData.MyDayOfMonth=myevent.esa_time.getDayOfMonth()
when matched
then update set DayEventCountTotal = 1 + myBusinessHoursData.DayEventCountTotal, timestamp = current_timestamp().format()
when not matched
then insert
select 'user_dst' AS metaKey,
user_dst AS metaValue,
current_timestamp().getDayOfMonth() AS MyDayOfMonth ,
current_timestamp().getDayOfWeek() AS MyDayOfWeek ,
current_timestamp().format() AS timestamp,
true AS isWorkingHours,
1 AS DayEventCountTotal;
//INPUTS - Feed Model Here
// Keep a Running Total for the Hour
ON Event(
device_ip IS NOT NULL
AND (((esa_time).getDayOfWeek IN (2,3,4,5,6) // Monday to Friday
AND (esa_time).getHourOfDay IN (8,9,10,11,12,13,14,15,16,17,18)) // 8:00 -18:00 UTC
// And not a Bank Holiday
AND NOT
(
(esa_time).getDayOfMonth IN (25,26) and (esa_time).getMonthOfYear IN (12) //25th 26th December
OR (esa_time).getDayOfMonth IN (1) and (esa_time).getMonthOfYear IN (1) //1st January
OR (esa_time).getDayOfMonth IN (30) and (esa_time).getMonthOfYear IN (3) //30th March Good Friday
OR (esa_time).getDayOfMonth IN (2) and (esa_time).getMonthOfYear IN (4) //2nd April
OR (esa_time).getDayOfMonth IN (7,28) and (esa_time).getMonthOfYear IN (5) // May Bank Holidays 2018
OR (esa_time).getDayOfMonth IN (27) and (esa_time).getMonthOfYear IN (8) //August Bank Holidays 2018
))
)
AS myevent
merge baselineBusinessHoursBH1HourData myBusinessHoursData
where myBusinessHoursData.metaValue=myevent.device_ip
AND myBusinessHoursData.MyHourOfDay=myevent.esa_time.getHourOfDay()
AND myBusinessHoursData.MyDayOfMonth=myevent.esa_time.getDayOfMonth()
when matched
then update set HourEventCountTotal = 1 + myBusinessHoursData.HourEventCountTotal, timestamp = current_timestamp().format()
when not matched
then insert
select 'device_ip' AS metaKey,
device_ip AS metaValue,
current_timestamp().getHourOfDay() AS MyHourOfDay,
current_timestamp().getDayOfMonth() AS MyDayOfMonth ,
current_timestamp().getDayOfWeek() AS MyDayOfWeek ,
current_timestamp().format() AS timestamp,
true AS isWorkingHours,
1 AS HourEventCountTotal;
//Keep a Running Total for the Day
ON Event(
device_ip IS NOT NULL
AND (((esa_time).getDayOfWeek IN (2,3,4,5,6) // Monday to Friday
AND (esa_time).getHourOfDay IN (8,9,10,11,12,13,14,15,16,17,18)) // 8:00 -18:00 UTC
AND NOT
(
(esa_time).getDayOfMonth IN (25,26) and (esa_time).getMonthOfYear IN (12) //25th 26th December
OR (esa_time).getDayOfMonth IN (1) and (esa_time).getMonthOfYear IN (1) //1st January
OR (esa_time).getDayOfMonth IN (30) and (esa_time).getMonthOfYear IN (3) //30th March Good Friday
OR (esa_time).getDayOfMonth IN (2) and (esa_time).getMonthOfYear IN (4) //2nd April
OR (esa_time).getDayOfMonth IN (7,28) and (esa_time).getMonthOfYear IN (5) // May Bank Holidays 2018
OR (esa_time).getDayOfMonth IN (27) and (esa_time).getMonthOfYear IN (8) //August Bank Holidays 2018
))
)
AS myevent
merge baselineBusinessHoursBH1DayData myBusinessHoursData
where myBusinessHoursData.metaValue=myevent.device_ip
AND myBusinessHoursData.MyDayOfMonth=myevent.esa_time.getDayOfMonth()
when matched
then update set DayEventCountTotal = 1 + myBusinessHoursData.DayEventCountTotal, timestamp = current_timestamp().format()
when not matched
then insert
select 'device_ip' AS metaKey,
device_ip AS metaValue,
current_timestamp().getDayOfMonth() AS MyDayOfMonth ,
current_timestamp().getDayOfWeek() AS MyDayOfWeek ,
current_timestamp().format() AS timestamp,
true AS isWorkingHours,
1 AS DayEventCountTotal;
// NON BUSINESS HOUR MODEL
//INPUTS - Feed Model Here
// Keep a Running Total for the Hour
ON Event(
user_dst IS NOT NULL
AND ((((esa_time).getDayOfWeek IN (2,3,4,5,6) // Monday to Friday
AND (esa_time).getHourOfDay NOT IN (8,9,10,11,12,13,14,15,16,17,18)) // 8:00 -18:00 UTC
OR (esa_time).getDayOfWeek IN (1,7)) // Saturday or Sunday
OR
(
(esa_time).getDayOfMonth IN (25,26) and (esa_time).getMonthOfYear IN (12) //25th 26th December
OR (esa_time).getDayOfMonth IN (1) and (esa_time).getMonthOfYear IN (1) //1st January
OR (esa_time).getDayOfMonth IN (30) and (esa_time).getMonthOfYear IN (3) //30th March Good Friday
OR (esa_time).getDayOfMonth IN (2) and (esa_time).getMonthOfYear IN (4) //2nd April
OR (esa_time).getDayOfMonth IN (7,28) and (esa_time).getMonthOfYear IN (5) // May Bank Holidays 2018
OR (esa_time).getDayOfMonth IN (27) and (esa_time).getMonthOfYear IN (8) //August Bank Holidays 2018
))
)
AS myevent
merge baselineBusinessHoursBH1HourData myBusinessHoursData
where myBusinessHoursData.metaValue=myevent.user_dst
AND myBusinessHoursData.MyHourOfDay=myevent.esa_time.getHourOfDay()
AND myBusinessHoursData.MyDayOfMonth=myevent.esa_time.getDayOfMonth()
when matched
then update set HourEventCountTotal = 1 + myBusinessHoursData.HourEventCountTotal, timestamp = current_timestamp().format()
when not matched
then insert
select 'user_dst' AS metaKey,
user_dst AS metaValue,
current_timestamp().getHourOfDay() AS MyHourOfDay,
current_timestamp().getDayOfMonth() AS MyDayOfMonth ,
current_timestamp().getDayOfWeek() AS MyDayOfWeek ,
current_timestamp().format() AS timestamp,
false AS isWorkingHours,
1 AS HourEventCountTotal;
//Keep a Running Total for the Day
ON Event(
user_dst IS NOT NULL
AND ((((esa_time).getDayOfWeek IN (2,3,4,5,6) // Monday to Friday
AND (esa_time).getHourOfDay NOT IN (8,9,10,11,12,13,14,15,16,17,18)) // 8:00 -18:00 UTC
OR (esa_time).getDayOfWeek IN (1,7)) // Saturday or Sunday
OR
(
(esa_time).getDayOfMonth IN (25,26) and (esa_time).getMonthOfYear IN (12) //25th 26th December
OR (esa_time).getDayOfMonth IN (1) and (esa_time).getMonthOfYear IN (1) //1st January
OR (esa_time).getDayOfMonth IN (30) and (esa_time).getMonthOfYear IN (3) //30th March Good Friday
OR (esa_time).getDayOfMonth IN (2) and (esa_time).getMonthOfYear IN (4) //2nd April
OR (esa_time).getDayOfMonth IN (7,28) and (esa_time).getMonthOfYear IN (5) // May Bank Holidays 2018
OR (esa_time).getDayOfMonth IN (27) and (esa_time).getMonthOfYear IN (8) //August Bank Holidays 2018
))
)
AS myevent
merge baselineBusinessHoursBH1DayData myBusinessHoursData
where myBusinessHoursData.metaValue=myevent.user_dst
AND myBusinessHoursData.MyDayOfMonth=myevent.esa_time.getDayOfMonth()
when matched
then update set DayEventCountTotal = 1 + myBusinessHoursData.DayEventCountTotal, timestamp = current_timestamp().format()
when not matched
then insert
select 'user_dst' AS metaKey,
user_dst AS metaValue,
current_timestamp().getDayOfMonth() AS MyDayOfMonth ,
current_timestamp().getDayOfWeek() AS MyDayOfWeek ,
current_timestamp().format() AS timestamp,
false AS isWorkingHours,
1 AS DayEventCountTotal;
//INPUTS - Feed Model Here
// Keep a Running Total for the Hour
ON Event(
device_ip IS NOT NULL
AND ((((esa_time).getDayOfWeek IN (2,3,4,5,6) // Monday to Friday
AND (esa_time).getHourOfDay NOT IN (8,9,10,11,12,13,14,15,16,17,18)) // 8:00 -18:00 UTC
OR (esa_time).getDayOfWeek IN (1,7)) // Saturday or Sunday
OR
(
(esa_time).getDayOfMonth IN (25,26) and (esa_time).getMonthOfYear IN (12) //25th 26th December
OR (esa_time).getDayOfMonth IN (1) and (esa_time).getMonthOfYear IN (1) //1st January
OR (esa_time).getDayOfMonth IN (30) and (esa_time).getMonthOfYear IN (3) //30th March Good Friday
OR (esa_time).getDayOfMonth IN (2) and (esa_time).getMonthOfYear IN (4) //2nd April
OR (esa_time).getDayOfMonth IN (7,28) and (esa_time).getMonthOfYear IN (5) // May Bank Holidays 2018
OR (esa_time).getDayOfMonth IN (27) and (esa_time).getMonthOfYear IN (8) //August Bank Holidays 2018
))
)
AS myevent
merge baselineBusinessHoursBH1HourData myBusinessHoursData
where myBusinessHoursData.metaValue=myevent.device_ip
AND myBusinessHoursData.MyHourOfDay=myevent.esa_time.getHourOfDay()
AND myBusinessHoursData.MyDayOfMonth=myevent.esa_time.getDayOfMonth()
when matched
then update set HourEventCountTotal = 1 + myBusinessHoursData.HourEventCountTotal, timestamp = current_timestamp().format()
when not matched
then insert
select 'device_ip' AS metaKey,
device_ip AS metaValue,
current_timestamp().getHourOfDay() AS MyHourOfDay,
current_timestamp().getDayOfMonth() AS MyDayOfMonth ,
current_timestamp().getDayOfWeek() AS MyDayOfWeek ,
current_timestamp().format() AS timestamp,
false AS isWorkingHours,
1 AS HourEventCountTotal;
//Keep a Running Total for the Day
ON Event(
device_ip IS NOT NULL
AND ((((esa_time).getDayOfWeek IN (2,3,4,5,6) // Monday to Friday
AND (esa_time).getHourOfDay NOT IN (8,9,10,11,12,13,14,15,16,17,18)) // 8:00 -18:00 UTC
OR (esa_time).getDayOfWeek IN (1,7)) // Saturday or Sunday
OR
(
(esa_time).getDayOfMonth IN (25,26) and (esa_time).getMonthOfYear IN (12) //25th 26th December
OR (esa_time).getDayOfMonth IN (1) and (esa_time).getMonthOfYear IN (1) //1st January
OR (esa_time).getDayOfMonth IN (30) and (esa_time).getMonthOfYear IN (3) //30th March Good Friday
OR (esa_time).getDayOfMonth IN (2) and (esa_time).getMonthOfYear IN (4) //2nd April
OR (esa_time).getDayOfMonth IN (7,28) and (esa_time).getMonthOfYear IN (5) // May Bank Holidays 2018
OR (esa_time).getDayOfMonth IN (27) and (esa_time).getMonthOfYear IN (8) //August Bank Holidays 2018
))
)
AS myevent
merge baselineBusinessHoursBH1DayData myBusinessHoursData
where myBusinessHoursData.metaValue=myevent.device_ip
AND myBusinessHoursData.MyDayOfMonth=myevent.esa_time.getDayOfMonth()
when matched
then update set DayEventCountTotal = 1 + myBusinessHoursData.DayEventCountTotal, timestamp = current_timestamp().format()
when not matched
then insert
select 'device_ip' AS metaKey,
device_ip AS metaValue,
current_timestamp().getDayOfMonth() AS MyDayOfMonth ,
current_timestamp().getDayOfWeek() AS MyDayOfWeek ,
current_timestamp().format() AS timestamp,
false AS isWorkingHours,
1 AS DayEventCountTotal;
0 REPLIES 0
