IoT CAS Frameworks/IOT_Server/Report_Generator_Framework/source/DashBoard-JSONgenerator-ArbitraryDate/src/com/merit/dashboard/queryexecuter/QueryExecuterDirectDB.java
public LinkedHashMap<String, String> collectMetricdata1(String customer, String resourceType, String cCustomer, String service,
            long smilli, long emilli) throws IOException, SQLException {
        try {
            serviceMetricInfo = new LinkedHashMap<String, String>();

            /**
             * *************************************************************************************************
             * Here we are checking the condition of showing critical according
             * to avg (means avg of actual value compare with avg of actual sla)
             * or Count (means actual value compare with actual sla)
             * *************************************************************************************************
             *
             */
            String metricHealthCheckLogic = "case when sum(criticalflag)>0 then 'CRITICAL' else 'OK' end";
            if (DBUtilHelper.getMetrics_mapping_properties().getProperty("HealthCheck").equals("AVG")) {
                metricHealthCheckLogic = "case when (avg(metricvalue)>avg(sla) and metrictype not in ("
                        + DBUtilHelper.getMetrics_mapping_properties().getProperty("MetricTypeBelowThresholdIsDanger")
                        + ")) or (avg(metricvalue)<avg(sla) and metrictype in ("
                        + DBUtilHelper.getMetrics_mapping_properties().getProperty("MetricTypeBelowThresholdIsDanger")
                        + ")) then  'CRITICAL' else 'OK' end";
            }

            String szQuery = "select host,service,resourcetype,resourceid,array_to_string(array_agg(metrictype),',') as metricname,array_to_string(array_agg(criticalsum),',') as metrictypehealth,case when resourceSubType is null then resourcetype else resourceSubType end as resourceSubType from "
                    + "( select def.resourcesubtype as resourceSubType,def.host,def.service as service,def.resourcetype,def.resourceid as resourceid,case when metrictype is null then 'default' else metrictype end as metrictype,case when criticalsum is null then 'GREY' else criticalsum end as criticalsum from "
                    + "( select host,resourcetype,resourceid,case when metrictype in (" + DBUtilHelper.getMetrics_mapping_properties().getProperty("MetrictypeCountAsValue") + ") then count(metrictype) else   round(cast(avg(metricvalue) as numeric),2) end as metricvalue,metrictype,round(cast(avg(sla) as numeric),2) as sla, " + metricHealthCheckLogic + " as criticalsum from "
                    + "( select host,resourcetype,resourceid, metricvalue,metrictype,sla,case when metricvalue>sla and metrictype not in (" + DBUtilHelper.getMetrics_mapping_properties().getProperty("MetricTypeBelowThresholdIsDanger") + ") or metricvalue<sla and metrictype  in (" + DBUtilHelper.getMetrics_mapping_properties().getProperty("MetricTypeBelowThresholdIsDanger") + ") then 1 else 0  end as criticalflag from "
                    + "  servicemetrics "
                    + "where timestamp1 between " + smilli + " and " + emilli + " and  "
                    + "resourcetype='" + resourceType + "' and "
                    + "customerid=(select id from customerinfo where customername='" + cCustomer + "') and "
                    + "service='" + service + "' and "
                    + "metricvalue is not null and "
                    + "metrictype not in ('downtime'," + DBUtilHelper.getMetrics_mapping_properties().getProperty("MetrictypeNotUsedInChart") + ")) as f "
                    + "group by host,resourcetype,resourceid,metrictype "
                    + "order by criticalsum) as f1 "
                    + "right outer join "
                    + "(select distinct host,resourceid,service,resourcetype,resourcesubtype from "
                    + "hostinfo "
                    + "where resourcetype='" + resourceType + "' and "
                    + "customerid=(select id from customerinfo where customername='" + cCustomer + "') and "
                    + "service='" + service + "' and "
                    + "service <>'null') as def using(host,resourceid)) as s1 "
                    + "group by host,service,resourcetype,resourceid,resourcesubtype order by metrictypehealth";


            /*if (resourceType.equals("Desktop") || resourceType.equals("server")) {
             szQuery = "select host,service,resourcetype,resourceid,array_to_string(array_agg(metrictype),',') as metricname,array_to_string(array_agg(criticalsum),',') as metrictypehealth from "
             + "( select host,resourcetype, hr.service,hr.resourceid as resourceid,case when metrictype is null then 'default' else metrictype end as metrictype,case when criticalsum is null then 'GREY' else criticalsum end as criticalsum from "
             + "( select host,resourceid,case when metrictype in (" + DBUtilHelper.getMetrics_mapping_properties().getProperty("MetrictypeCountAsValue") + ") then count(metrictype) else   round(cast(avg(metricvalue) as numeric),2) end as metricvalue,metrictype,round(cast(avg(sla) as numeric),2) as sla, " + metricHealthCheckLogic + " as criticalsum from "
             + "( select host,resourceid, metricvalue,metrictype,sla,case when metricvalue>sla and metrictype not in (" + DBUtilHelper.getMetrics_mapping_properties().getProperty("MetricTypeBelowThresholdIsDanger") + ") or metricvalue<sla and metrictype in (" + DBUtilHelper.getMetrics_mapping_properties().getProperty("MetricTypeBelowThresholdIsDanger") + ") then 1 else 0  end as criticalflag from "
             + "servicemetrics "
             + "where timestamp1 between " + smilli + " and " + emilli + " and  "
             + "resourcetype='" + resourceType + "' and "
             + "customerid=(select id from customerinfo where customername='" + cCustomer + "') and "
             + "service='" + service + "' and "
             + "metricvalue is not null and "
             + "metrictype not in ('downtime'," + DBUtilHelper.getMetrics_mapping_properties().getProperty("MetrictypeNotUsedInChart") + ")) as f "
             + "group by host,resourceid,metrictype ) as al "
             + "right outer join "
             + "(select distinct resourceid,host,service,resourcetype from "
             + "hostinfo "
             + "where resourcetype='" + resourceType + "' and "
             + "customerid=(select id from customerinfo where customername='" + cCustomer + "') and "
             + "service='" + service + "' ) as hr "
             + "using(host,resourceid) "
             + "order by criticalsum) as fullservicetable "
             + "group by host,service,resourcetype,resourceid order by metrictypehealth";
             }*/
//            uniqueconnection = ConnectionDAO.getConnection(customer);
//            stat = uniqueconnection.createStatement();

            /**
             * ***************************************************************************************************
             * Here Query is Separated according to resource type because server
             * and Desktop taking more time because of more data , We can use
             * the same query by removing if condition array_agg function in
             * postgresql below 8 version we have to create by following
             * command: create aggregate array_agg ( sfunc = array_append,
             * basetype = anyelement, stype = anyarray, initcond = '{}' );
             * ***************************************************************************************************
             *
             */
            log.debug("Report Metric Query /" + resourceType + "/ \n" + szQuery);
            ResultSet rs = ConnectionDAO.executerQuery(szQuery, customer);//stat.executeQuery(szQuery);
            String metricname;//
            String chartType;//
            String metrictypehealth;//
            String host;//
            String serviceName;//
            String resourcetype;//
            String resourceId;//
            String resourceSubType;//
            String metricnameTemp;//
            while (rs.next()) {
                metricname = "[]";
                chartType = "[\"0\",\"1\"]";
                metrictypehealth = "[]";

                host = rs.getString("host");
                if (rs.getString("metrictypehealth") != null) {
                    metrictypehealth = "[\"" + rs.getString("metrictypehealth").replace(",", "\",\"") + "\"]";
                }
                if (rs.getString("metricname") != null) {
                    metricnameTemp = "\"" + rs.getString("metricname").replace(",", "\",\"") + "\"";
                    metricname = bizlogic.ChangeMetricNameAsPropertyname(metricnameTemp);
                    if (!metricnameTemp.equals("Default")) {
                        chartType = bizlogic.getChartType(resourceType, metricnameTemp, metrictypehealth);
                    }
                }

                serviceName = rs.getString("service");
                resourcetype = rs.getString("resourcetype");
                resourceId = rs.getString("resourceId");
                resourceSubType = "resourceSubType";
                /*if (resourceType.equals("Desktop") || resourceType.equals("server")) {
                 } else {*/
                resourceSubType = rs.getString("resourceSubType");
                //}


                if (serviceMetricInfo.containsKey(serviceName + "," + host + "," + resourceId)) {
                    serviceMetricInfo.remove(serviceName + "," + host + "," + resourceId);
                }
                if (!host.equals("null")) {
                    serviceMetricInfo.put(serviceName + "," + host + "," + resourceId, "\"ServiceName\":\"" + serviceName
                            + "\",\"ResourceType\":\"" + resourceType + "\",\"ServerName\":\"" + host + "\",\"ResourceNames\":"
                            + metricname + ",\"Health\":" + metrictypehealth + ",\"ResourceSubType\":\"" + resourceSubType
                            + "\",\"ResourceChartType\":" + chartType + ",\"ResourceID\":\"" + resourceId + "\"");
                }

            }
            log.info("QueryExecutor.collectMetricdata1()    serviceMetricInfo = " + serviceMetricInfo);

            /**
             * ***************************************************************************************************
             * Here we are setting All Host list Available in that Period
             * According to this we are showing Availability
             * ***************************************************************************************************
             */
            ConnectionDAO.closeStatement();
        } catch (Exception e) {
            e.printStackTrace();
            log.error("DashBoard Report Metric Query /" + resourceType + "/ \n" + e.getMessage());
        }

        return serviceMetricInfo;
    }
/**
     * ************************************************************************************************
     * This method is giving All metricType,Avg(Metricvalues) related to each
     * metricType, SLAValues related to each
     * metricType,services,ResourceType,ResourceId,Servername
     *
     * @param customer Selected customerID
     * @param resourceType Different category as
     * Desktop,Server,DataBase,Network,JVM
     * @param smilli date in second as long Timestamp(Current Time-selected Time
     * Period)
     * @param emilli date in second as long Timestamp(Current Time)
     * @return serviceMetricInfo Metric information as string Json
     *
     * *************************************************************************************************
     */
This method is giving All metricType,Avg(Metricvalues) related to each
metricType, SLAValues related to each
metricType,services,ResourceType,ResourceId,Servername


This method is giving All metricType Avg ( Metricvalues ) related to each metricType SLAValues related to each metricType services ResourceType ResourceId Servername
public LinkedHashMap<String, String> collectMetricdata1(String customer, String resourceType, String cCustomer, String service,
            long smilli, long emilli) throws IOException, SQLException {
        try {
            serviceMetricInfo = new LinkedHashMap<String, String>();
            /**
             * *************************************************************************************************
             * Here we are checking the condition of showing critical according
             * to avg (means avg of actual value compare with avg of actual sla)
             * or Count (means actual value compare with actual sla)
             * *************************************************************************************************
             *
             */
            String metricHealthCheckLogic = "case when sum(criticalflag)>0 then 'CRITICAL' else 'OK' end";
            if (DBUtilHelper.getMetrics_mapping_properties().getProperty("HealthCheck").equals("AVG")) {
                metricHealthCheckLogic = "case when (avg(metricvalue)>avg(sla) and metrictype not in ("
                        + DBUtilHelper.getMetrics_mapping_properties().getProperty("MetricTypeBelowThresholdIsDanger")
                        + ")) or (avg(metricvalue)<avg(sla) and metrictype in ("
                        + DBUtilHelper.getMetrics_mapping_properties().getProperty("MetricTypeBelowThresholdIsDanger")
                        + ")) then  'CRITICAL' else 'OK' end";
            }
            String szQuery = "select host,service,resourcetype,resourceid,array_to_string(array_agg(metrictype),',') as metricname,array_to_string(array_agg(criticalsum),',') as metrictypehealth,case when resourceSubType is null then resourcetype else resourceSubType end as resourceSubType from "
                    + "( select def.resourcesubtype as resourceSubType,def.host,def.service as service,def.resourcetype,def.resourceid as resourceid,case when metrictype is null then 'default' else metrictype end as metrictype,case when criticalsum is null then 'GREY' else criticalsum end as criticalsum from "
                    + "( select host,resourcetype,resourceid,case when metrictype in (" + DBUtilHelper.getMetrics_mapping_properties().getProperty("MetrictypeCountAsValue") + ") then count(metrictype) else   round(cast(avg(metricvalue) as numeric),2) end as metricvalue,metrictype,round(cast(avg(sla) as numeric),2) as sla, " + metricHealthCheckLogic + " as criticalsum from "
                    + "( select host,resourcetype,resourceid, metricvalue,metrictype,sla,case when metricvalue>sla and metrictype not in (" + DBUtilHelper.getMetrics_mapping_properties().getProperty("MetricTypeBelowThresholdIsDanger") + ") or metricvalue<sla and metrictype  in (" + DBUtilHelper.getMetrics_mapping_properties().getProperty("MetricTypeBelowThresholdIsDanger") + ") then 1 else 0  end as criticalflag from "
                    + "  servicemetrics "
                    + "where timestamp1 between " + smilli + " and " + emilli + " and  "
                    + "resourcetype='" + resourceType + "' and "
                    + "customerid=(select id from customerinfo where customername='" + cCustomer + "') and "
                    + "service='" + service + "' and "
                    + "metricvalue is not null and "
                    + "metrictype not in ('downtime'," + DBUtilHelper.getMetrics_mapping_properties().getProperty("MetrictypeNotUsedInChart") + ")) as f "
                    + "group by host,resourcetype,resourceid,metrictype "
                    + "order by criticalsum) as f1 "
                    + "right outer join "
                    + "(select distinct host,resourceid,service,resourcetype,resourcesubtype from "
                    + "hostinfo "
                    + "where resourcetype='" + resourceType + "' and "
                    + "customerid=(select id from customerinfo where customername='" + cCustomer + "') and "
                    + "service='" + service + "' and "
                    + "service <>'null') as def using(host,resourceid)) as s1 "
                    + "group by host,service,resourcetype,resourceid,resourcesubtype order by metrictypehealth";
            /*if (resourceType.equals("Desktop") || resourceType.equals("server")) {
             szQuery = "select host,service,resourcetype,resourceid,array_to_string(array_agg(metrictype),',') as metricname,array_to_string(array_agg(criticalsum),',') as metrictypehealth from "
             + "( select host,resourcetype, hr.service,hr.resourceid as resourceid,case when metrictype is null then 'default' else metrictype end as metrictype,case when criticalsum is null then 'GREY' else criticalsum end as criticalsum from "
             + "( select host,resourceid,case when metrictype in (" + DBUtilHelper.getMetrics_mapping_properties().getProperty("MetrictypeCountAsValue") + ") then count(metrictype) else   round(cast(avg(metricvalue) as numeric),2) end as metricvalue,metrictype,round(cast(avg(sla) as numeric),2) as sla, " + metricHealthCheckLogic + " as criticalsum from "
             + "( select host,resourceid, metricvalue,metrictype,sla,case when metricvalue>sla and metrictype not in (" + DBUtilHelper.getMetrics_mapping_properties().getProperty("MetricTypeBelowThresholdIsDanger") + ") or metricvalue<sla and metrictype in (" + DBUtilHelper.getMetrics_mapping_properties().getProperty("MetricTypeBelowThresholdIsDanger") + ") then 1 else 0  end as criticalflag from "
             + "servicemetrics "
             + "where timestamp1 between " + smilli + " and " + emilli + " and  "
             + "resourcetype='" + resourceType + "' and "
             + "customerid=(select id from customerinfo where customername='" + cCustomer + "') and "
             + "service='" + service + "' and "
             + "metricvalue is not null and "
             + "metrictype not in ('downtime'," + DBUtilHelper.getMetrics_mapping_properties().getProperty("MetrictypeNotUsedInChart") + ")) as f "
             + "group by host,resourceid,metrictype ) as al "
             + "right outer join "
             + "(select distinct resourceid,host,service,resourcetype from "
             + "hostinfo "
             + "where resourcetype='" + resourceType + "' and "
             + "customerid=(select id from customerinfo where customername='" + cCustomer + "') and "
             + "service='" + service + "' ) as hr "
             + "using(host,resourceid) "
             + "order by criticalsum) as fullservicetable "
             + "group by host,service,resourcetype,resourceid order by metrictypehealth";
             }*/
            /**
             * ***************************************************************************************************
             * Here Query is Separated according to resource type because server
             * and Desktop taking more time because of more data , We can use
             * the same query by removing if condition array_agg function in
             * postgresql below 8 version we have to create by following
             * command: create aggregate array_agg ( sfunc = array_append,
             * basetype = anyelement, stype = anyarray, initcond = '{}' );
             * ***************************************************************************************************
             *
             */
            log.debug("Report Metric Query /" + resourceType + "/ \n" + szQuery);
            ResultSet rs = ConnectionDAO.executerQuery(szQuery, customer);
            String metricname;
            String chartType;
            String metrictypehealth;
            String host;
            String serviceName;
            String resourcetype;
            String resourceId;
            String resourceSubType;
            String metricnameTemp;
            while (rs.next()) {
                metricname = "[]";
                chartType = "[\"0\",\"1\"]";
                metrictypehealth = "[]";
                host = rs.getString("host");
                if (rs.getString("metrictypehealth") != null) {
                    metrictypehealth = "[\"" + rs.getString("metrictypehealth").replace(",", "\",\"") + "\"]";
                }
                if (rs.getString("metricname") != null) {
                    metricnameTemp = "\"" + rs.getString("metricname").replace(",", "\",\"") + "\"";
                    metricname = bizlogic.ChangeMetricNameAsPropertyname(metricnameTemp);
                    if (!metricnameTemp.equals("Default")) {
                        chartType = bizlogic.getChartType(resourceType, metricnameTemp, metrictypehealth);
                    }
                }
                serviceName = rs.getString("service");
                resourcetype = rs.getString("resourcetype");
                resourceId = rs.getString("resourceId");
                resourceSubType = "resourceSubType";
                /*if (resourceType.equals("Desktop") || resourceType.equals("server")) {
                 } else {*/
                resourceSubType = rs.getString("resourceSubType");
                }
                if (serviceMetricInfo.containsKey(serviceName + "," + host + "," + resourceId)) {
                    serviceMetricInfo.remove(serviceName + "," + host + "," + resourceId);
                }
                if (!host.equals("null")) {
                    serviceMetricInfo.put(serviceName + "," + host + "," + resourceId, "\"ServiceName\":\"" + serviceName
                            + "\",\"ResourceType\":\"" + resourceType + "\",\"ServerName\":\"" + host + "\",\"ResourceNames\":"
                            + metricname + ",\"Health\":" + metrictypehealth + ",\"ResourceSubType\":\"" + resourceSubType
                            + "\",\"ResourceChartType\":" + chartType + ",\"ResourceID\":\"" + resourceId + "\"");
                }
            }
            log.info("QueryExecutor.collectMetricdata1()    serviceMetricInfo = " + serviceMetricInfo);
            /**
             * ***************************************************************************************************
             * Here we are setting All Host list Available in that Period
             * According to this we are showing Availability
             * ***************************************************************************************************
             */
            ConnectionDAO.closeStatement();
        } catch (Exception e) {
            e.printStackTrace();
            log.error("DashBoard Report Metric Query /" + resourceType + "/ \n" + e.getMessage());
        }
        return serviceMetricInfo;
    }
public LinkedHashMap < String , String > collectMetricdata1 ( String customer , String resourceType , String cCustomer , String service , long smilli , long emilli ) throws IOException , SQLException { try { serviceMetricInfo = new LinkedHashMap < String , String > ( ) ; /**
             * *************************************************************************************************
             * Here we are checking the condition of showing critical according
             * to avg (means avg of actual value compare with avg of actual sla)
             * or Count (means actual value compare with actual sla)
             * *************************************************************************************************
             *
             */ String metricHealthCheckLogic = "case when sum(criticalflag)>0 then 'CRITICAL' else 'OK' end" ; if ( DBUtilHelper . getMetrics_mapping_properties ( ) . getProperty ( "HealthCheck" ) . equals ( "AVG" ) ) { metricHealthCheckLogic = "case when (avg(metricvalue)>avg(sla) and metrictype not in (" + DBUtilHelper . getMetrics_mapping_properties ( ) . getProperty ( "MetricTypeBelowThresholdIsDanger" ) + ")) or (avg(metricvalue)<avg(sla) and metrictype in (" + DBUtilHelper . getMetrics_mapping_properties ( ) . getProperty ( "MetricTypeBelowThresholdIsDanger" ) + ")) then  'CRITICAL' else 'OK' end" ; } String szQuery = "select host,service,resourcetype,resourceid,array_to_string(array_agg(metrictype),',') as metricname,array_to_string(array_agg(criticalsum),',') as metrictypehealth,case when resourceSubType is null then resourcetype else resourceSubType end as resourceSubType from " + "( select def.resourcesubtype as resourceSubType,def.host,def.service as service,def.resourcetype,def.resourceid as resourceid,case when metrictype is null then 'default' else metrictype end as metrictype,case when criticalsum is null then 'GREY' else criticalsum end as criticalsum from " + "( select host,resourcetype,resourceid,case when metrictype in (" + DBUtilHelper . getMetrics_mapping_properties ( ) . getProperty ( "MetrictypeCountAsValue" ) + ") then count(metrictype) else   round(cast(avg(metricvalue) as numeric),2) end as metricvalue,metrictype,round(cast(avg(sla) as numeric),2) as sla, " + metricHealthCheckLogic + " as criticalsum from " + "( select host,resourcetype,resourceid, metricvalue,metrictype,sla,case when metricvalue>sla and metrictype not in (" + DBUtilHelper . getMetrics_mapping_properties ( ) . getProperty ( "MetricTypeBelowThresholdIsDanger" ) + ") or metricvalue<sla and metrictype  in (" + DBUtilHelper . getMetrics_mapping_properties ( ) . getProperty ( "MetricTypeBelowThresholdIsDanger" ) + ") then 1 else 0  end as criticalflag from " + "  servicemetrics " + "where timestamp1 between " + smilli + " and " + emilli + " and  " + "resourcetype='" + resourceType + "' and " + "customerid=(select id from customerinfo where customername='" + cCustomer + "') and " + "service='" + service + "' and " + "metricvalue is not null and " + "metrictype not in ('downtime'," + DBUtilHelper . getMetrics_mapping_properties ( ) . getProperty ( "MetrictypeNotUsedInChart" ) + ")) as f " + "group by host,resourcetype,resourceid,metrictype " + "order by criticalsum) as f1 " + "right outer join " + "(select distinct host,resourceid,service,resourcetype,resourcesubtype from " + "hostinfo " + "where resourcetype='" + resourceType + "' and " + "customerid=(select id from customerinfo where customername='" + cCustomer + "') and " + "service='" + service + "' and " + "service <>'null') as def using(host,resourceid)) as s1 " + "group by host,service,resourcetype,resourceid,resourcesubtype order by metrictypehealth" ; /*if (resourceType.equals("Desktop") || resourceType.equals("server")) {
             szQuery = "select host,service,resourcetype,resourceid,array_to_string(array_agg(metrictype),',') as metricname,array_to_string(array_agg(criticalsum),',') as metrictypehealth from "
             + "( select host,resourcetype, hr.service,hr.resourceid as resourceid,case when metrictype is null then 'default' else metrictype end as metrictype,case when criticalsum is null then 'GREY' else criticalsum end as criticalsum from "
             + "( select host,resourceid,case when metrictype in (" + DBUtilHelper.getMetrics_mapping_properties().getProperty("MetrictypeCountAsValue") + ") then count(metrictype) else   round(cast(avg(metricvalue) as numeric),2) end as metricvalue,metrictype,round(cast(avg(sla) as numeric),2) as sla, " + metricHealthCheckLogic + " as criticalsum from "
             + "( select host,resourceid, metricvalue,metrictype,sla,case when metricvalue>sla and metrictype not in (" + DBUtilHelper.getMetrics_mapping_properties().getProperty("MetricTypeBelowThresholdIsDanger") + ") or metricvalue<sla and metrictype in (" + DBUtilHelper.getMetrics_mapping_properties().getProperty("MetricTypeBelowThresholdIsDanger") + ") then 1 else 0  end as criticalflag from "
             + "servicemetrics "
             + "where timestamp1 between " + smilli + " and " + emilli + " and  "
             + "resourcetype='" + resourceType + "' and "
             + "customerid=(select id from customerinfo where customername='" + cCustomer + "') and "
             + "service='" + service + "' and "
             + "metricvalue is not null and "
             + "metrictype not in ('downtime'," + DBUtilHelper.getMetrics_mapping_properties().getProperty("MetrictypeNotUsedInChart") + ")) as f "
             + "group by host,resourceid,metrictype ) as al "
             + "right outer join "
             + "(select distinct resourceid,host,service,resourcetype from "
             + "hostinfo "
             + "where resourcetype='" + resourceType + "' and "
             + "customerid=(select id from customerinfo where customername='" + cCustomer + "') and "
             + "service='" + service + "' ) as hr "
             + "using(host,resourceid) "
             + "order by criticalsum) as fullservicetable "
             + "group by host,service,resourcetype,resourceid order by metrictypehealth";
             }*/ /**
             * ***************************************************************************************************
             * Here Query is Separated according to resource type because server
             * and Desktop taking more time because of more data , We can use
             * the same query by removing if condition array_agg function in
             * postgresql below 8 version we have to create by following
             * command: create aggregate array_agg ( sfunc = array_append,
             * basetype = anyelement, stype = anyarray, initcond = '{}' );
             * ***************************************************************************************************
             *
             */ log . debug ( "Report Metric Query /" + resourceType + "/ \n" + szQuery ) ; ResultSet rs = ConnectionDAO . executerQuery ( szQuery , customer ) ; String metricname ; String chartType ; String metrictypehealth ; String host ; String serviceName ; String resourcetype ; String resourceId ; String resourceSubType ; String metricnameTemp ; while ( rs . next ( ) ) { metricname = "[]" ; chartType = "[\"0\",\"1\"]" ; metrictypehealth = "[]" ; host = rs . getString ( "host" ) ; if ( rs . getString ( "metrictypehealth" ) != null ) { metrictypehealth = "[\"" + rs . getString ( "metrictypehealth" ) . replace ( "," , "\",\"" ) + "\"]" ; } if ( rs . getString ( "metricname" ) != null ) { metricnameTemp = "\"" + rs . getString ( "metricname" ) . replace ( "," , "\",\"" ) + "\"" ; metricname = bizlogic . ChangeMetricNameAsPropertyname ( metricnameTemp ) ; if ( ! metricnameTemp . equals ( "Default" ) ) { chartType = bizlogic . getChartType ( resourceType , metricnameTemp , metrictypehealth ) ; } } serviceName = rs . getString ( "service" ) ; resourcetype = rs . getString ( "resourcetype" ) ; resourceId = rs . getString ( "resourceId" ) ; resourceSubType = "resourceSubType" ; /*if (resourceType.equals("Desktop") || resourceType.equals("server")) {
                 } else {*/ resourceSubType = rs . getString ( "resourceSubType" ) ; if ( serviceMetricInfo . containsKey ( serviceName + "," + host + "," + resourceId ) ) { serviceMetricInfo . remove ( serviceName + "," + host + "," + resourceId ) ; } if ( ! host . equals ( "null" ) ) { serviceMetricInfo . put ( serviceName + "," + host + "," + resourceId , "\"ServiceName\":\"" + serviceName + "\",\"ResourceType\":\"" + resourceType + "\",\"ServerName\":\"" + host + "\",\"ResourceNames\":" + metricname + ",\"Health\":" + metrictypehealth + ",\"ResourceSubType\":\"" + resourceSubType + "\",\"ResourceChartType\":" + chartType + ",\"ResourceID\":\"" + resourceId + "\"" ) ; } } log . info ( "QueryExecutor.collectMetricdata1()    serviceMetricInfo = " + serviceMetricInfo ) ; /**
             * ***************************************************************************************************
             * Here we are setting All Host list Available in that Period
             * According to this we are showing Availability
             * ***************************************************************************************************
             */ ConnectionDAO . closeStatement ( ) ; } catch ( Exception e ) { e . printStackTrace ( ) ; log . error ( "DashBoard Report Metric Query /" + resourceType + "/ \n" + e . getMessage ( ) ) ; } return serviceMetricInfo ; }
This method is giving All metricType,Avg(Metricvalues) related to each
 metricType, SLAValues related to each
 metricType,services,ResourceType,ResourceId,Servername
This method is giving All metricType Avg ( Metricvalues ) related to each metricType SLAValues related to each metricType services ResourceType ResourceId Servername
//            uniqueconnection = ConnectionDAO.getConnection(customer); //            stat = uniqueconnection.createStatement(); //stat.executeQuery(szQuery); // // // // // // // // // //}