Evaluation of IPv6 readiness by looking at "A" and "AAAA" DNS Queries

DNS-A-AAAA.r

# Library Loading
library("RPostgreSQL");
library("car");
library("reshape");

# Connect to Database
pgDrv <- dbDriver("PostgreSQL")
dbh <- dbConnect(pgDrv, host="localhost", dbname="dnsmonitor", user="dnsmon", password="tooEasy")

# Retrieve Statistics from DB
details <- dbGetQuery(dbh, "select q.client_id, rq.type, count(1) as queries
  from packet_query q
		inner join packet_meta_question mq on q.id = mq.query_id
		inner join packet_record_question rq on mq.question_id = rq.id
	where q.query_ts > NOW() - interval '24 hours' and rq.class = 'IN'
	group by q.client_id, rq.type
	order by q.client_id, rq.type
")
  
# Close the Database Connection and free variables
dbDisconnect(dbh)
rm(dbh)
rm(pgDrv)

# Reshape the Data
melted <- melt( details, id=c("client_id","type") )
stats <- cast( melted, client_id ~ type, fill=0 )

# Sum the Total Queries
cols = length(stats) - 1
stats$queries = rowSums( stats[2:cols] ) 

# Do stuff with the data

# Plot Forwards (IPv4 Only)
plot( stats$queries - stats$AAAA, stats$A,  xlab="Total Queries minus AAAA", ylab="A Requests",
      main="Forward Requests (IPv4 Only)")
      
forwardipv4_reg <- lm( stats$A ~ stats$queries - stats$AAAA )
regLine( forwardipv4_reg, col="red" )

# Plot Forwards (IPv4 & IPv6 Only)
plot( stats$queries, stats$A + stats$AAAA,  xlab="Total Queries", ylab="A + AAAA Requests",
      main="Forward Requests (IPv4 and IPv6)")
      
forwards_reg <- lm( stats$A + stats$AAAA ~ stats$queries )
regLine( forwards_reg, col="red" )

# IPv4 vs IPv6 analysis
v4v6.v4mean <- round( mean( stats$A ), digits=2 )
v4v6.v4median <- round( median( stats$A ), digits =2 )
v4v6.v4sd <- round( sd( stats$A ), digits=2 )

v4v6.v6mean <- round(mean( stats$AAAA ), digits=2 )
v4v6.v6median <- round(median( stats$AAAA ), digits=2 )
v4v6.v6sd <- round(sd( stats$AAAA ), digits=2 )

# Plot A vs AAAA
plot( stats$A, stats$AAAA,
    xlab=paste("A Questions : mean(", v4v6.v4mean, "), median(", v4v6.v4median, "), sd(", v4v6.v4sd, ")" ), 
    ylab=paste("AAAA Questions : mean(", v4v6.v6mean, "), median(", v4v6.v6median, "), sd(", v4v6.v6sd, ")" ), 
    main="Forward Requests (IPv4 vs IPv6)"
)
v4v6.reg <- lm( stats$AAAA ~ stats$A )
regLine( v4v6.reg, col="red" )

Sample Output: https://skitch.com/reyjrar/fe2i6/dns-a-aaaa