Выявляем TOP 10 “засранцев”.
Работает со скоростью выполнения SELECT’ов.
Prerequisite:
ODBC драйвер
PHP5
R с библиотеками RODBC,gplots,gdata,gtools
ovo_stat.php
<html>
<body>
<?php
exec("del ovo_act.png");
exec("del ovo_hist.png");
$c=`C:\R\R-2.7.1\bin\Rscript.exe ovo_act.r > NUL 2>&1`;
while (!file_exists("ovo_hist.png"));
?>
Активные<br>
<img src="ovo_act.png">
<br>Исторические<br>
<img src="ovo_hist.png">
</body>
</html>
ovo_act.r
#RODBC,gplots,gdata,gtools
library(RODBC); library(gplots)
ovo <- odbcConnect("odbc источник",uid="логин",pwd="пароль")
mbs <- sqlQuery(ovo,"select a.severity, b.node_name from opc_op.opc_act_messages a, opc_op.opc_node_names b where a.node_id=b.node_id;")
mbs1 <- tapply(mbs[,1],mbs[,1],length)
mbs2 <- tapply(mbs[,1],mbs[,1:2],length)
mbs2[is.na(mbs2)] <- 0
mbs2 <- mbs2[,rev(order(colSums(mbs2)))]
sev_names <- paste(c("Normal","Warning","Critical","Minor","Major"),"\n",mbs1,"(",round(100*mbs1/length(mbs[,1])),"%)",sep="")
sev_cols <- c("green","turquoise1","red","yellow","orange")
png(file="c:/Inetpub/wwwroot/ovo_act.png",width=1000,height=300)
par(las=1, mar=c(5.1, 15.1, 2, 2.1), screen(1))
barplot2(mbs2[,1:10],horiz=T,col=c("green","turquoise1","red","yellow","orange"))
close.screen(all = T)
dev.off()
mbs <- sqlQuery(ovo,"select a.severity, b.node_name from opc_op.opc_hist_messages a, opc_op.opc_node_names b where a.node_id=b.node_id;")
odbcClose(ovo)
mbs1 <- tapply(mbs[,1],mbs[,1],length)
mbs2 <- tapply(mbs[,1],mbs[,1:2],length)
mbs2[is.na(mbs2)] <- 0
mbs2 <- mbs2[,rev(order(colSums(mbs2)))]
sev_names <- paste(c("Normal","Warning","Critical","Minor","Major"),"\n",mbs1,"(",round(100*mbs1/length(mbs[,1])),"%)",sep="")
sev_cols <- c("green","turquoise1","red","yellow","orange")
png(file="c:/Inetpub/wwwroot/ovo_hist.png",width=1000,height=300)
par(las=1, mar=c(5.1, 15.1, 2, 2.1), screen(1))
barplot2(mbs2[,1:10],horiz=T,col=c("green","turquoise1","red","yellow","orange"))
close.screen(all = T)
dev.off()
На выходе имеем такую картинку
