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

            /**
             * *************************************************************************************************
             * 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";
            }

//            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 = '{}' );
             * ***************************************************************************************************
             *
             */
            String szQuery = "select host,service,resourcetype,resourceid,array_to_string(array_agg(metricvalue),',') as metricvalues,"
                    + "array_to_string(array_agg(sla),',') as slavalues ,array_to_string(array_agg(metrictype),',') as metricname,"
                    + "array_to_string(array_agg(criticalsum),',') as metrictypehealth,resourceSubType from "
                    + "( select def.resourcesubtype as resourceSubType,f1.host,case when  f1.host=def.host and "
                    + "f1.resourceid=def.resourceid then def.service end as service,resourcetype,f1.resourceid,metricvalue,"
                    + "metrictype,sla,criticalsum from ( select host,service,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,service,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 "
                    + "metrictype in (" + metrictypeIn + ") and "
                    + "metricvalue is not null and "
                    + "metrictype not in ('downtime'," + DBUtilHelper.getMetrics_mapping_properties().getProperty("MetrictypeNotUsedInChart") + ")) as f "
                    + "group by host,service,resourcetype,resourceid,metrictype "
                    + "order by criticalsum) as f1 "
                    + "left outer join "
                    + "(select distinct host,resourceid,service,resourcesubtype from "
                    + "servicemetrics "
                    + "where resourcetype='" + resourceType + "' and "
                    + "customerid=(select id from customerinfo where customername='" + cCustomer + "') and "
                    + "service='" + service + "' and "
                    + "metrictype in (" + metrictypeIn + ") and "
                    + "service <>'null' and "
                    + "resourcesubtype !='') 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(metricvalue),',') as metricvalues,array_to_string(array_agg(sla),',') as slavalues ,array_to_string(array_agg(metrictype),',') as metricname,array_to_string(array_agg(criticalsum),',') as metrictypehealth from "
                        + "( select host,resourcetype, service,al.resourceid,metricvalue,metrictype,sla,criticalsum from "
                        + "( select service,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 service,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 "
                        + "metrictype in (" + metrictypeIn + ") and "
                        + "metricvalue is not null and "
                        + "metrictype not in ('downtime'," + DBUtilHelper.getMetrics_mapping_properties().getProperty("MetrictypeNotUsedInChart") + ")) as f "
                        + "group by resourceid,service,metrictype ) as al "
                        + "left outer join "
                        + "(select distinct resourceid,host,resourcetype from "
                        + "servicemetrics "
                        + "where timestamp1 between " + smilli + " and " + emilli + " and "
                        + "metrictype in (" + metrictypeIn + ") and "
                        + "resourcetype='" + resourceType + "' and "
                        + "customerid=(select id from customerinfo where customername='" + cCustomer + "') and "
                        + "service='" + service + "') as hr "
                        + "using(resourceid) "
                        + "order by criticalsum) as fullservicetable "
                        + "group by host,service,resourcetype,resourceid order by metrictypehealth";
            }
            log.info("Report Metric Query /" + resourceType + "/ \n" + szQuery);

            log.debug("Report Metric Query /" + resourceType + "/ \n" + szQuery);
            ResultSet rs = ConnectionDAO.executerQuery(szQuery, customer);//stat.executeQuery(szQuery);
            String metricvalues;//
            String metricname;//
            String slavalues;//
            String metrictypehealth;//
            String host;//
            String metricnameTemp;//
            String serviceName;//
            String resourcetype;//
            String resourceId;//
            String resourceSubType;//
            while (rs.next()) {

                metricvalues = "[]";
                metricname = "[]";
                slavalues = "[]";
                metrictypehealth = "[]";

                host = rs.getString("host");
                if (rs.getString("metricvalues") != null) {
                    metricvalues = "[" + rs.getString("metricvalues") + "]";
                }
                if (rs.getString("metricname") != null) {
                    metricnameTemp = "\"" + rs.getString("metricname").replace(",", "\",\"") + "\"";
                    metricname = bizlogic.ChangeMetricNameAsPropertyname(metricnameTemp);
                }
                if (rs.getString("slavalues") != null) {
                    slavalues = "[" + rs.getString("slavalues") + "]";
                }
                if (rs.getString("metrictypehealth") != null) {
                    metrictypehealth = "[\"" + rs.getString("metrictypehealth").replace(",", "\",\"") + "\"]";
                }
                serviceName = rs.getString("service");
                resourcetype = rs.getString("resourcetype");
                resourceId = rs.getString("resourceId");
                resourceSubType = "";
                if (resourceType.equals("Desktop") || resourceType.equals("server")) {
                } else {
                    resourceSubType = rs.getString("resourceSubType");
                }


                if (serviceMetricInfo.containsKey(host + "," + resourceId)) {
                    serviceMetricInfo.remove(host + "," + resourceId);
                }
                if (!host.equals("null")) {
                    serviceMetricInfo.put(host + "," + resourceId, "\"ServiceName\":\"" + serviceName + "\",\"ResourceType\":\"" + resourcetype + "\",\"ServerName\":\"" + host + "\",\"ResourceNames\":" + metricname + ",\"ResourceValues\":" + metricvalues + ",\"SlaValues\":" + slavalues + ",\"Health\":" + metrictypehealth + ",\"ResourceSubType\":\"" + resourceSubType + "\",\"ResourceID\":\"" + resourceId + "\"");
                }
                host_resourcetype_list.add(host.trim() + "/" + resourcetype.trim());
            }

            /**
             * ***************************************************************************************************
             * Here we are setting All Host list Available in that Period
             * According to this we are showing Availability
             * ***************************************************************************************************
             */
            pojoObject.setHostListAvailable(host_resourcetype_list);
            ConnectionDAO.closeStatement();
            host_resourcetype_list = null;
        } 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> collectMetricdata(String customer, String resourceType, String cCustomer, String service,
            String metrictypeIn, long smilli, long emilli) throws IOException, SQLException {
        try {
            pojoObject = new PojoObject();
            serviceMetricInfo = new LinkedHashMap<String, String>();
            ArrayList<String> host_resourcetype_list = new ArrayList<String>();
            serviceMetricInfo.clear();
            /**
             * *************************************************************************************************
             * 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";
            }
            /**
             * ***************************************************************************************************
             * 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 = '{}' );
             * ***************************************************************************************************
             *
             */
            String szQuery = "select host,service,resourcetype,resourceid,array_to_string(array_agg(metricvalue),',') as metricvalues,"
                    + "array_to_string(array_agg(sla),',') as slavalues ,array_to_string(array_agg(metrictype),',') as metricname,"
                    + "array_to_string(array_agg(criticalsum),',') as metrictypehealth,resourceSubType from "
                    + "( select def.resourcesubtype as resourceSubType,f1.host,case when  f1.host=def.host and "
                    + "f1.resourceid=def.resourceid then def.service end as service,resourcetype,f1.resourceid,metricvalue,"
                    + "metrictype,sla,criticalsum from ( select host,service,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,service,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 "
                    + "metrictype in (" + metrictypeIn + ") and "
                    + "metricvalue is not null and "
                    + "metrictype not in ('downtime'," + DBUtilHelper.getMetrics_mapping_properties().getProperty("MetrictypeNotUsedInChart") + ")) as f "
                    + "group by host,service,resourcetype,resourceid,metrictype "
                    + "order by criticalsum) as f1 "
                    + "left outer join "
                    + "(select distinct host,resourceid,service,resourcesubtype from "
                    + "servicemetrics "
                    + "where resourcetype='" + resourceType + "' and "
                    + "customerid=(select id from customerinfo where customername='" + cCustomer + "') and "
                    + "service='" + service + "' and "
                    + "metrictype in (" + metrictypeIn + ") and "
                    + "service <>'null' and "
                    + "resourcesubtype !='') 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(metricvalue),',') as metricvalues,array_to_string(array_agg(sla),',') as slavalues ,array_to_string(array_agg(metrictype),',') as metricname,array_to_string(array_agg(criticalsum),',') as metrictypehealth from "
                        + "( select host,resourcetype, service,al.resourceid,metricvalue,metrictype,sla,criticalsum from "
                        + "( select service,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 service,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 "
                        + "metrictype in (" + metrictypeIn + ") and "
                        + "metricvalue is not null and "
                        + "metrictype not in ('downtime'," + DBUtilHelper.getMetrics_mapping_properties().getProperty("MetrictypeNotUsedInChart") + ")) as f "
                        + "group by resourceid,service,metrictype ) as al "
                        + "left outer join "
                        + "(select distinct resourceid,host,resourcetype from "
                        + "servicemetrics "
                        + "where timestamp1 between " + smilli + " and " + emilli + " and "
                        + "metrictype in (" + metrictypeIn + ") and "
                        + "resourcetype='" + resourceType + "' and "
                        + "customerid=(select id from customerinfo where customername='" + cCustomer + "') and "
                        + "service='" + service + "') as hr "
                        + "using(resourceid) "
                        + "order by criticalsum) as fullservicetable "
                        + "group by host,service,resourcetype,resourceid order by metrictypehealth";
            }
            log.info("Report Metric Query /" + resourceType + "/ \n" + szQuery);
            log.debug("Report Metric Query /" + resourceType + "/ \n" + szQuery);
            ResultSet rs = ConnectionDAO.executerQuery(szQuery, customer);
            String metricvalues;
            String metricname;
            String slavalues;
            String metrictypehealth;
            String host;
            String metricnameTemp;
            String serviceName;
            String resourcetype;
            String resourceId;
            String resourceSubType;
            while (rs.next()) {
                metricvalues = "[]";
                metricname = "[]";
                slavalues = "[]";
                metrictypehealth = "[]";
                host = rs.getString("host");
                if (rs.getString("metricvalues") != null) {
                    metricvalues = "[" + rs.getString("metricvalues") + "]";
                }
                if (rs.getString("metricname") != null) {
                    metricnameTemp = "\"" + rs.getString("metricname").replace(",", "\",\"") + "\"";
                    metricname = bizlogic.ChangeMetricNameAsPropertyname(metricnameTemp);
                }
                if (rs.getString("slavalues") != null) {
                    slavalues = "[" + rs.getString("slavalues") + "]";
                }
                if (rs.getString("metrictypehealth") != null) {
                    metrictypehealth = "[\"" + rs.getString("metrictypehealth").replace(",", "\",\"") + "\"]";
                }
                serviceName = rs.getString("service");
                resourcetype = rs.getString("resourcetype");
                resourceId = rs.getString("resourceId");
                resourceSubType = "";
                if (resourceType.equals("Desktop") || resourceType.equals("server")) {
                } else {
                    resourceSubType = rs.getString("resourceSubType");
                }
                if (serviceMetricInfo.containsKey(host + "," + resourceId)) {
                    serviceMetricInfo.remove(host + "," + resourceId);
                }
                if (!host.equals("null")) {
                    serviceMetricInfo.put(host + "," + resourceId, "\"ServiceName\":\"" + serviceName + "\",\"ResourceType\":\"" + resourcetype + "\",\"ServerName\":\"" + host + "\",\"ResourceNames\":" + metricname + ",\"ResourceValues\":" + metricvalues + ",\"SlaValues\":" + slavalues + ",\"Health\":" + metrictypehealth + ",\"ResourceSubType\":\"" + resourceSubType + "\",\"ResourceID\":\"" + resourceId + "\"");
                }
                host_resourcetype_list.add(host.trim() + "/" + resourcetype.trim());
            }
            /**
             * ***************************************************************************************************
             * Here we are setting All Host list Available in that Period
             * According to this we are showing Availability
             * ***************************************************************************************************
             */
            pojoObject.setHostListAvailable(host_resourcetype_list);
            ConnectionDAO.closeStatement();
            host_resourcetype_list = null;
        } catch (Exception e) {
            e.printStackTrace();
            log.error("DashBoard Report Metric Query /" + resourceType + "/ \n" + e.getMessage());
        }
        return serviceMetricInfo;
    }
public LinkedHashMap < String , String > collectMetricdata ( String customer , String resourceType , String cCustomer , String service , String metrictypeIn , long smilli , long emilli ) throws IOException , SQLException { try { pojoObject = new PojoObject ( ) ; serviceMetricInfo = new LinkedHashMap < String , String > ( ) ; ArrayList < String > host_resourcetype_list = new ArrayList < String > ( ) ; serviceMetricInfo . clear ( ) ; /**
             * *************************************************************************************************
             * 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" ; } /**
             * ***************************************************************************************************
             * 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 = '{}' );
             * ***************************************************************************************************
             *
             */ String szQuery = "select host,service,resourcetype,resourceid,array_to_string(array_agg(metricvalue),',') as metricvalues," + "array_to_string(array_agg(sla),',') as slavalues ,array_to_string(array_agg(metrictype),',') as metricname," + "array_to_string(array_agg(criticalsum),',') as metrictypehealth,resourceSubType from " + "( select def.resourcesubtype as resourceSubType,f1.host,case when  f1.host=def.host and " + "f1.resourceid=def.resourceid then def.service end as service,resourcetype,f1.resourceid,metricvalue," + "metrictype,sla,criticalsum from ( select host,service,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,service,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 " + "metrictype in (" + metrictypeIn + ") and " + "metricvalue is not null and " + "metrictype not in ('downtime'," + DBUtilHelper . getMetrics_mapping_properties ( ) . getProperty ( "MetrictypeNotUsedInChart" ) + ")) as f " + "group by host,service,resourcetype,resourceid,metrictype " + "order by criticalsum) as f1 " + "left outer join " + "(select distinct host,resourceid,service,resourcesubtype from " + "servicemetrics " + "where resourcetype='" + resourceType + "' and " + "customerid=(select id from customerinfo where customername='" + cCustomer + "') and " + "service='" + service + "' and " + "metrictype in (" + metrictypeIn + ") and " + "service <>'null' and " + "resourcesubtype !='') 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(metricvalue),',') as metricvalues,array_to_string(array_agg(sla),',') as slavalues ,array_to_string(array_agg(metrictype),',') as metricname,array_to_string(array_agg(criticalsum),',') as metrictypehealth from " + "( select host,resourcetype, service,al.resourceid,metricvalue,metrictype,sla,criticalsum from " + "( select service,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 service,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 " + "metrictype in (" + metrictypeIn + ") and " + "metricvalue is not null and " + "metrictype not in ('downtime'," + DBUtilHelper . getMetrics_mapping_properties ( ) . getProperty ( "MetrictypeNotUsedInChart" ) + ")) as f " + "group by resourceid,service,metrictype ) as al " + "left outer join " + "(select distinct resourceid,host,resourcetype from " + "servicemetrics " + "where timestamp1 between " + smilli + " and " + emilli + " and " + "metrictype in (" + metrictypeIn + ") and " + "resourcetype='" + resourceType + "' and " + "customerid=(select id from customerinfo where customername='" + cCustomer + "') and " + "service='" + service + "') as hr " + "using(resourceid) " + "order by criticalsum) as fullservicetable " + "group by host,service,resourcetype,resourceid order by metrictypehealth" ; } log . info ( "Report Metric Query /" + resourceType + "/ \n" + szQuery ) ; log . debug ( "Report Metric Query /" + resourceType + "/ \n" + szQuery ) ; ResultSet rs = ConnectionDAO . executerQuery ( szQuery , customer ) ; String metricvalues ; String metricname ; String slavalues ; String metrictypehealth ; String host ; String metricnameTemp ; String serviceName ; String resourcetype ; String resourceId ; String resourceSubType ; while ( rs . next ( ) ) { metricvalues = "[]" ; metricname = "[]" ; slavalues = "[]" ; metrictypehealth = "[]" ; host = rs . getString ( "host" ) ; if ( rs . getString ( "metricvalues" ) != null ) { metricvalues = "[" + rs . getString ( "metricvalues" ) + "]" ; } if ( rs . getString ( "metricname" ) != null ) { metricnameTemp = "\"" + rs . getString ( "metricname" ) . replace ( "," , "\",\"" ) + "\"" ; metricname = bizlogic . ChangeMetricNameAsPropertyname ( metricnameTemp ) ; } if ( rs . getString ( "slavalues" ) != null ) { slavalues = "[" + rs . getString ( "slavalues" ) + "]" ; } if ( rs . getString ( "metrictypehealth" ) != null ) { metrictypehealth = "[\"" + rs . getString ( "metrictypehealth" ) . replace ( "," , "\",\"" ) + "\"]" ; } serviceName = rs . getString ( "service" ) ; resourcetype = rs . getString ( "resourcetype" ) ; resourceId = rs . getString ( "resourceId" ) ; resourceSubType = "" ; if ( resourceType . equals ( "Desktop" ) || resourceType . equals ( "server" ) ) { } else { resourceSubType = rs . getString ( "resourceSubType" ) ; } if ( serviceMetricInfo . containsKey ( host + "," + resourceId ) ) { serviceMetricInfo . remove ( host + "," + resourceId ) ; } if ( ! host . equals ( "null" ) ) { serviceMetricInfo . put ( host + "," + resourceId , "\"ServiceName\":\"" + serviceName + "\",\"ResourceType\":\"" + resourcetype + "\",\"ServerName\":\"" + host + "\",\"ResourceNames\":" + metricname + ",\"ResourceValues\":" + metricvalues + ",\"SlaValues\":" + slavalues + ",\"Health\":" + metrictypehealth + ",\"ResourceSubType\":\"" + resourceSubType + "\",\"ResourceID\":\"" + resourceId + "\"" ) ; } host_resourcetype_list . add ( host . trim ( ) + "/" + resourcetype . trim ( ) ) ; } /**
             * ***************************************************************************************************
             * Here we are setting All Host list Available in that Period
             * According to this we are showing Availability
             * ***************************************************************************************************
             */ pojoObject . setHostListAvailable ( host_resourcetype_list ) ; ConnectionDAO . closeStatement ( ) ; host_resourcetype_list = null ; } 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); // // // // // // // // // //