From e3d89991be40d3786d5da01169965ebb601ed2cb Mon Sep 17 00:00:00 2001 From: Nicolas Boisselier Date: Fri, 24 Jun 2016 15:57:18 +0100 Subject: [PATCH] ui --- etc/dbs/ui.php | 64 +++++++++++++++++++++++++++++++++++------ lib/mysql/functions.sql | 7 +++++ 2 files changed, 62 insertions(+), 9 deletions(-) diff --git a/etc/dbs/ui.php b/etc/dbs/ui.php index dea642b5..7679f3bc 100644 --- a/etc/dbs/ui.php +++ b/etc/dbs/ui.php @@ -1,5 +1,5 @@ 'Semantico UI', 'pdo' => 'sqlite:/opt/semantico/product/releases/sem_ui/db/semantico.db', 'default_table' => 'node', @@ -14,7 +14,7 @@ $CONF['ui'] = array( 'aws_ec2s' => "(SELECT count(*) FROM aws_ec2 WHERE aws_az.id=idaws_az)", 'idnetwork' => "(SELECT id FROM network WHERE network.id='subnet-' || aws_az.id)", 'network_subnet' => "(SELECT network FROM network WHERE network.id='subnet-' || aws_az.id)", - 'ec2' => "'--region '||(SELECT id FROM aws_region WHERE aws_region.id=idaws_region)||' --subnet-id '||(SELECT id FROM aws_subnet WHERE aws_subnet.aws_azid=aws_az.id)||' --security-group-ids '||(SELECT sg FROM aws_region WHERE aws_region.id=idaws_region)||' --associate-public-ip-address --key-name semadm'" + 'ec2' => "CONCAT('--region ',(SELECT id FROM aws_region WHERE aws_region.id=idaws_region),' --subnet-id ',(SELECT id FROM aws_subnet WHERE aws_subnet.aws_azid=aws_az.id),' --security-group-ids ',(SELECT sg FROM aws_region WHERE aws_region.id=idaws_region),' --associate-public-ip-address --key-name semadm')" #'subnet' => "(SELECT 'subnet' || aws_az.id)", ) ), @@ -82,24 +82,45 @@ $CONF['ui'] = array( 'node' => array( 'row_parse_post'=> function(&$r) { + global $Db; + $metas = []; + foreach ($Db->rows( + #"SELECT name,value FROM fact WHERE name IN ('environment','last_run') AND idnode=".$Db->quote($r['id']) + #"SELECT * FROM (SELECT 'dell-drac' as 'n',replace(src,'.semantico.net','') as 'v' FROM dns WHERE src LIKE ".$Db->quote($r['id'].'-rac.%')." ORDER BY length(v) LIMIT 1) drac" + "" + ."SELECT 'idvmware',idvmware FROM vms WHERE vms.idnode=".$Db->quote($r['id']) + ."UNION SELECT 'env',value FROM fact WHERE name = 'environment' AND idnode=".$Db->quote($r['id']) + ."UNION SELECT 'aws_ec2',id FROM aws_ec2 WHERE aws_ec2.idnode=".$Db->quote($r['id']) + ."UNION SELECT 'last_backup',CONCAT('',max(date),'') as 'v' FROM backup WHERE idnode=".$Db->quote($r['id']) + ."UNION SELECT 'last_run',substr(value,1,10) FROM fact WHERE name = 'last_run' AND idnode=".$Db->quote($r['id'])." AND value < ".$Db->quote(date('Y-m-d', strtotime('-3 days'))) + ."UNION SELECT 'rack',CONCAT(idrack,'/',position) FROM rack_node WHERE idnode=".$Db->quote($r['id'])." OR idnode IN (SELECT idvmware FROM vms WHERE vms.idnode=rack_node.idnode)" + ."UNION SELECT 'switch' as 'n', CONCAT(id,':',port) as 'v' FROM switch_port WHERE switch_port.idnode=".$Db->quote($r['id'])." AND switch_port.port NOT LIKE 'ch%'" + ,PDO::FETCH_NUM) as $f) { $metas[$f[0]] = $f[1]; } + if ($metas) { + $r['metas'] = join("",array_map(function($k,$v){return (string)$v==="" ? "" : "$k=$v\n";},array_keys($metas),$metas)); + } + + $r['keyevent'] = $Db->row("SELECT count(*) FROM fact WHERE fact.idnode=".$Db->quote($r['id'])); $r['graph'] = ''; + #debug($facts); }, + /* 'extras'=> array( # n => v 'metas' => preg_replace('/(^\t|\n$)/',' ',"(SELECT GROUP_CONCAT(n || '=' || v,'\n') FROM ( - SELECT * FROM (SELECT 'dell-drac' as 'n',replace(src,'.semantico.net','') as 'v' FROM dns WHERE src LIKE id || '-rac.%' ORDER BY length(v) LIMIT 1) drac + SELECT * FROM (SELECT 'dell-drac' as 'n',replace(src,'.semantico.net','') as 'v' FROM dns WHERE src LIKE CONCAT(node.id,'-rac.%') ORDER BY length(v) LIMIT 1) drac UNION SELECT 'idvmware' as 'n',idvmware as 'v' FROM vms WHERE vms.idnode=node.id UNION SELECT 'env' as 'n',value as 'v' FROM fact WHERE fact.idnode=node.id AND name='environment' UNION SELECT 'aws_ec2' as 'n',id as 'v' FROM aws_ec2 WHERE aws_ec2.idnode=node.id - UNION SELECT 'last_backup' as 'n',''||max(date)||'' as 'v' FROM backup WHERE idnode=node.id - UNION SELECT name as 'n', strftime('%Y-%m-%d',value) as 'v' FROM fact WHERE idnode=id AND name='last_run' AND value < date(CURRENT_DATE,'-7 days') || ' 00:00:00' - UNION SELECT 'rack' as'n',idrack || '/' || position as 'v' FROM rack_node WHERE id=idnode OR idnode IN (SELECT idvmware FROM vms WHERE vms.idnode=node.id) - UNION SELECT 'switch' as 'n', id||':'||port as 'v' FROM switch_port WHERE switch_port.idnode=node.id AND switch_port.port NOT LIKE 'ch%' - ))"), + UNION SELECT 'last_backup' as 'n',CONCAT('',max(date),'') as 'v' FROM backup WHERE idnode=node.id + UNION SELECT name as 'n', strftime('%Y-%m-%d',value) as 'v' FROM fact WHERE idnode=id AND name='last_run' AND value < '".date('Y-m-d', strtotime('-3 days'))."' + UNION SELECT 'rack' as'n',CONCAT(idrack,'/',position) as 'v' FROM rack_node WHERE id=idnode OR idnode IN (SELECT idvmware FROM vms WHERE vms.idnode=node.id) + UNION SELECT 'switch' as 'n', CONCAT(id,':',port) as 'v' FROM switch_port WHERE switch_port.idnode=node.id AND switch_port.port NOT LIKE 'ch%' + ) t)"), 'fact' => "(SELECT count(*) FROM fact WHERE fact.idnode=node.id)", 'keyevent' => "(SELECT count(*) FROM keyevent WHERE keyevent.idnode=node.id)", #'graph' => '\'\'', - ), + */ ), @@ -122,8 +143,33 @@ $CONF['ui'] = array( 'row_parse_post'=> function(&$r) { $r['View'] = ''; }, + ), + 'network_ips' => array('sql' => "SELECT (SELECT id FROM network WHERE network <>'' AND network IS NOT NULL AND cidr_range(network,ip) ORDER BY hidden LIMIT 1) as idnetwork,code,network,iddc,count(*) as count FROM view_ip_detail JOIN network ON idnetwork=network.id GROUP BY idnetwork HAVING idnetwork NOT NULL ORDER BY count DESC"), + 'view_puppet_error' => array('sql' => "SELECT idnode ,idcompany ,iddc ,value as last_run FROM fact JOIN node ON id=idnode WHERE name='last_run' AND substr(value,1,10) <='".date('Y-m-d', strtotime('-3 days'))."'"), + 'view_backup_error' => array('sql' => "SELECT idnode,idcompany,iddc,server,max(backup.date) as date FROM backup JOIN node ON id=idnode WHERE NOT EXISTS (SELECT 1 FROM node_decommission nd WHERE nd.id=node.id) GROUP BY idnode HAVING date <= '".date('Y-m-d', strtotime('-2 days'))."' ORDER BY backup.date") ), ); +$CONF['ui'] = array( + '_import' => array('_ui'), +); +$CONF['ui-mysql'] = array( + 'host' => 'ui.semantico.net', + 'type' => 'mysql', + 'name' => 'ui', + 'pdo' => '', + '_import' => array('_ui'), +); +#bye(realpath(dirname(__FILE__).'/../../lib/php/db.php')); +#require_once(realpath(dirname(__FILE__).'/../../lib/php/db/types/sqlite.php')); +#bye($DB_TYPES['sqlite']); +# NB 24.06.16 if (!empty($DB_TYPES['sqlite']) and 0) +# NB 24.06.16 $DB_TYPES['sqlite']['sqliteCreateFunction'] = array_merge($DB_TYPES['sqlite']['sqliteCreateFunction'],array( +# NB 24.06.16 'gethostbyaddr' => function ($value) { return gethostbyaddr($value); }, +# NB 24.06.16 'ip2int' => function ($value) { return ip2long($value); }, +# NB 24.06.16 'int2ip' => function ($value) { return long2ip($value); }, +# NB 24.06.16 'concat' => function ($v1,$v2) { return $v1.$v2; }, +# NB 24.06.16 'cidr_range' => function ( $cidr, $chkip=null ) { return cidr_range( $cidr,$chkip); }, +# NB 24.06.16 )); ?> diff --git a/lib/mysql/functions.sql b/lib/mysql/functions.sql index bb5e67f9..e2aa5f75 100644 --- a/lib/mysql/functions.sql +++ b/lib/mysql/functions.sql @@ -5,3 +5,10 @@ RETURNS text DETERMINISTIC BEGIN RETURN date_format(s,f); END$$ + +-- NB 24.06.16 DROP FUNCTION IF EXISTS cidr_range$$ +-- NB 24.06.16 CREATE FUNCTION cidr_range (cidr text,s text) +-- NB 24.06.16 RETURNS text DETERMINISTIC +-- NB 24.06.16 BEGIN +-- NB 24.06.16 RETURN date_format(s,f); +-- NB 24.06.16 END$$ -- 2.47.3