# data.table way this stuff feels faster than dplyr but isn't very FP when using := methods
# alternatively, use the .() aka list() feature and create a new table. Still faster than dplyr or plyr
# https://mran.microsoft.com/web/packages/data.table/vignettes/datatable-intro.html
library(data.table) # for fread and other data.table functions
library(tidyverse)  # for as_tibble to feed into ggplot
library(lubridate)  # for round_date
library(fasttime)   # for fastPOSIXct

# SQL for dtLGE01
# SELECT    [transactiontype]
#          ,[transactionid]
#          ,CASE when COUNT(time_stamp) = 1 then 1 else 0 END one_is_timeout
#          ,CASE when COUNT(time_stamp) = 1
#                then 0.000755
#                else DATEDIFF(MS, min(time_stamp), max([TIME_STAMP]))
#           END duration_ms
#          ,min([TIME_STAMP]) start
#          ,max([time_stamp]) endt
#          ,[componentname]
#          ,correlationid
#   -- use "with (nolock)" to prevent table locking
#   FROM [HAWK_Log_Archive].[dbo].[PR_LOG] with (nolock)
#   -- Refer to timestamp format for time-level granularity
#   where transactionid in (
#     select distinct transactionid
#     FROM [HAWK_Log_Archive].[dbo].[PR_LOG] with (nolock)
#     where TIME_STAMP  >= '20171024 09:00:00:00' and TIME_STAMP < '20171024 11:00:00:00'  -- 195341578
#   )
#    and status in ('Start','End')
#    group by transactionid,transactiontype,applicationid,componentname,correlationid
#    order by start

dtLG01=fread("c:/kewoo/eai/d20171024.cle-log.csv")
AESTDiff <- 36000
interval.length <- "1 seconds"
start.AEST <- fastPOSIXct("2017-10-24 10:15:00")-36000
end.AEST <- fastPOSIXct("2017-10-24 10:45:00")-36000

# exploratory
str(dtER01)
names(dtER01)
names(dtLG01)
dtLG01[,.(TIME_STAMP, APPLICATIONID)]
# end exploration

tb01.tx.times.all <-dtLG01[, list(transactionid,
                               componentname,
                               startPct = round_date(fastPOSIXct(start)-AESTDiff, interval.length),
                               endtPct = round_date(fastPOSIXct(endt)-AESTDiff, interval.length))
                        ]
tb01.expandedIntervals <- tb01.tx.times.all[, list(intervals = seq(startPct, endtPct, by=1)), by = transactionid
                            ][, list(txCount = .N), by = intervals]

# OLD plot with startPct == endtPct when transaction is a timeout (side-effect of not incorporating endtPct from exceptionrec)
ggplot() +
 geom_line(data=tb01.expandedIntervals[intervals > start.AEST & intervals < end.AEST],
           aes(x=intervals,y=txCount), color='blue')

# SQL for dtER
# -- GENERIC EXCEPTIONREC SQL
#    SELECT [TIME_STAMP]
#       ,[COMPONENTNAME]
#       ,[TRANSACTIONTYPE]
#       ,[transactionid]
#       ,[correlationid]
#   -- use "with (nolock)" to prevent table locking
#   FROM [HAWK_Log_Archive].[dbo].[PR_EXCEPTIONREC] with (nolock)
#   -- Refer to timestamp format for time-level granularity
#   where TIME_STAMP  >= '20171024 09:00:00:00' and TIME_STAMP < '20171024 11:00:00:00'  -- 195341578
#   order by TIME_STAMP


dtER=fread("c:/kewoo/eai/d20171024.exceptionrec.csv")
tb02.tx.times.all <-dtER[, list(transactionid,
                                  COMPONENTNAME,
                                  endtPct = round_date(fastPOSIXct(TIME_STAMP)-AESTDiff, interval.length))]
tb02.txCounts <- tb02.tx.times.all[, list(txCount = .N), by = endtPct]


ggplot() +
 geom_line(data=tb01.expandedIntervals, aes(x=intervals,y=txCount), color='blue') +
 geom_line(data=tb02.txCounts, aes(x=endtPct,y=txCount), color='red')


# 20171215: The reason there's a drop in txCount during a service interruption
#           is because startPct == endPct caused by the group by in the original extracting SQL
#           Solution is to extract actual endPct from EXCEPTIONREC joining via transactionid
# first, take outer join
dtOJ <- tb02.tx.times.all[tb01.tx.times.all, on = "transactionid"]
# second, populate blank (NA) endPct values with i.endPct which has actual end times from the cle-log table
dtOJ[is.na(endtPct), endtPct := i.endtPct]
# tb01.tx.times.filtered <- tb01.tx.times.all[startPct > start.AEST & endtPct < end.AEST]
# expand intervals only for AcurityConnector transactions
dtOJ.expandedIntervals <- dtOJ[componentname %in% c('AcurityConnector-1-AcurityConnectorPA-01',
                                                   'AcurityConnector-1-AcurityConnectorPA-02')
                            ][, list(intervals = seq(startPct, endtPct, by=1)), by = transactionid
                            ][, list(txCount = .N), by = intervals]
# expand intervals for all transactions
dtOJ.expandedIntervals <- dtOJ[, list(intervals = seq(startPct, endtPct, by=1)), by = transactionid
                                ][, list(txCount = .N), by = intervals]


# NEW plot with startPct != endtPct when transaction is a timeout after incorporating endtPct from exceptionrec
start.AEST <- fastPOSIXct("2017-10-24 10:15:00")-36000
end.AEST <- fastPOSIXct("2017-10-24 10:45:00")-36000
ggplot() +
 geom_line(data=dtOJ.expandedIntervals[intervals > start.AEST & intervals < end.AEST],
           aes(x=intervals,y=txCount), color='blue') +
 geom_line(data=tb02.txCounts[endtPct > start.AEST & endtPct < end.AEST],
           aes(x=endtPct,y=txCount), color='red')

# exploratory
View(tb02.txCounts)
View(dtOJ.expandedIntervals)
View(tb02.tx.times.all)
# end exploration

You are viewing proxied material from sdf.org. The copyright of proxied material belongs to its original authors. Any comments or complaints in relation to proxied material should be directed to the original authors of the content concerned. Please see the disclaimer for more details.