Max flow count distgribution

max_flow_counts.r

# install.packages("RPostgreSQL")
# install.packages("zoo")

# Notes: EMA - Exponential moving average with alpha=0.05

library(RPostgreSQL)
library(TTR)
drv <- dbDriver("PostgreSQL")
##con <- dbConnect(drv, dbname="probedb", user="postgres", host="192.168.132.111")
con <- dbConnect(drv, dbname="1", user="postgres", host="192.168.132.111")
#  dbListConnections(drv)
#  dbGetInfo(drv)
#  summary(con)

rs <- dbSendQuery(
  con,
"SELECT \"timestamp\", max(flow_count) AS max_flow_count FROM (
SELECT
  start_time_sec AS \"timestamp\",
  CASE d WHEN 1 THEN ip_addr_1 ELSE ip_addr_2 END AS ip_addr,
  count(DISTINCT flow_id) AS flow_count
FROM long_flow_samples, (VALUES (1),(2)) AS d(d)
WHERE ( d!=1 OR byte_count_1!=0 ) AND ( d!=1 OR byte_count_1!=0 )
GROUP BY start_time_sec, CASE d WHEN 1 THEN ip_addr_1 ELSE ip_addr_2 END ) AS c
GROUP BY \"timestamp\"");

#rs <- dbSendQuery(
#  con,
#  "SELECT *  FROM prx_flow_counts_per_sec( NULL, NULL, '{98e95c92-235c-699d-08dd-d278a59a1675}')")


cnts <- fetch(rs,n=-1)

plot( 
  cnts$flow_count ~ cnts$timestamp,,
  col="red",
  xlab="timestamp (sec)", 
  ylab="Flow Counts", 
  main="Incoming flow counts per sec")
lines(cnts$timestamp, EMA(cnts$flow_count,n=1,ratio=0.05), col="blue",lwd=2)