fix:端口实体基数统计sql条件修改

1 files changed, 1 insertions, 1 deletions

diff --git a/cn-admin/src/main/resources/db/R__AZ_magic_api_file.sql b/cn-admin/src/main/resources/db/R__AZ_magic_api_file.sql
index a340f1b..82e36e7 100644
--- a/cn-admin/src/main/resources/db/R__AZ_magic_api_file.sql
+++ b/cn-admin/src/main/resources/db/R__AZ_magic_api_file.sql
@@ -798,7 +798,7 @@ INSERT INTO `magic_api_file` VALUES ('/api/v1/entity/explorer/top/国家-实体�
 INSERT INTO `magic_api_file` VALUES ('/api/v1/entity/explorer/top/城市-实体基数统计.ms', '{\n  \"properties\" : { },\n  \"id\" : \"29790a82d1b54dff811980a7167ebaca\",\n  \"script\" : null,\n  \"groupId\" : \"80392cde8e944a6e9c947b4ad8661c29\",\n  \"name\" : \"城市-实体基数统计\",\n  \"createTime\" : null,\n  \"updateTime\" : 1694415483477,\n  \"lock\" : null,\n  \"createBy\" : null,\n  \"updateBy\" : \"ljy\",\n  \"path\" : \"/aggCity\",\n  \"method\" : \"GET\",\n  \"parameters\" : [ {\n    \"name\" : \"resource\",\n    \"value\" : \"ip.port=80\",\n    \"description\" : null,\n    \"required\" : false,\n    \"dataType\" : \"String\",\n    \"type\" : null,\n    \"defaultValue\" : null,\n    \"validateType\" : null,\n    \"error\" : null,\n    \"expression\" : null,\n    \"children\" : null\n  }, {\n    \"name\" : \"startTime\",\n    \"value\" : \"\",\n    \"description\" : null,\n    \"required\" : false,\n    \"dataType\" : \"Long\",\n    \"type\" : null,\n    \"defaultValue\" : null,\n    \"validateType\" : null,\n    \"error\" : null,\n    \"expression\" : null,\n    \"children\" : null\n  }, {\n    \"name\" : \"endTime\",\n    \"value\" : \"\",\n    \"description\" : null,\n    \"required\" : false,\n    \"dataType\" : \"Long\",\n    \"type\" : null,\n    \"defaultValue\" : null,\n    \"validateType\" : null,\n    \"error\" : null,\n    \"expression\" : null,\n    \"children\" : null\n  } ],\n  \"options\" : [ ],\n  \"requestBody\" : \"\",\n  \"headers\" : [ ],\n  \"paths\" : [ ],\n  \"responseBody\" : \"{\\n    \\\"code\\\": 200,\\n    \\\"data\\\": {\\n        \\\"list\\\": [{\\n            \\\"value\\\": \\\"Shanghai City\\\",\\n            \\\"uniqueEntities\\\": \\\"50\\\"\\n        }, {\\n            \\\"value\\\": \\\"Beijing City\\\",\\n            \\\"uniqueEntities\\\": \\\"41\\\"\\n        }, {\\n            \\\"value\\\": \\\"Hongkong\\\",\\n            \\\"uniqueEntities\\\": \\\"30\\\"\\n        }, {\\n            \\\"value\\\": \\\"Guangzhou City\\\",\\n            \\\"uniqueEntities\\\": \\\"28\\\"\\n        }, {\\n            \\\"value\\\": \\\"Tokyo\\\",\\n            \\\"uniqueEntities\\\": \\\"28\\\"\\n        }]\\n    },\\n    \\\"time\\\": \\\"2023-08-31 02:54:15\\\",\\n    \\\"originalUrl\\\": \\\"http://192.168.44.55:9999/?query=SELECT ip_admin_area as ip_admin_area, COUNTIF(DISTINCT(app_name),notEmpty(app_name)) as appCount, COUNTIF(DISTINCT(domain),notEmpty(domain)) as domainCount, COUNTIF(DISTINCT(ip),notEmpty(ip)) as ipCount, plus(ipCount,plus(appCount,domainCount)) AS totalCount FROM( SELECT e.app_name AS app_name, e.domain AS domain, e.ip AS ip, e.update_time AS update_time, e.ip_admin_area AS ip_admin_area FROM cn_entity_relation e  JOIN cn_ip_dynamic_attribute dr ON (e.ip = dr.ip)  WHERE e.update_time >= 1677898455 AND e.update_time < 1693450455 AND notEmpty(ip_admin_area)  AND dr.update_time >= 1677898455 AND dr.update_time < 1693450455  AND (dr.port = 80) ) GROUP BY ip_admin_area ORDER BY totalCount DESC limit 5&format=json&option=real-time\\\",\\n    \\\"message\\\": \\\"success\\\",\\n    \\\"status\\\": 200\\n}\",\n  \"description\" : null,\n  \"requestBodyDefinition\" : null,\n  \"responseBodyDefinition\" : {\n    \"name\" : \"\",\n    \"value\" : \"\",\n    \"description\" : \"\",\n    \"required\" : false,\n    \"dataType\" : \"Object\",\n    \"type\" : null,\n    \"defaultValue\" : null,\n    \"validateType\" : \"\",\n    \"error\" : \"\",\n    \"expression\" : \"\",\n    \"children\" : [ {\n      \"name\" : \"code\",\n      \"value\" : \"200\",\n      \"description\" : \"\",\n      \"required\" : false,\n      \"dataType\" : \"Integer\",\n      \"type\" : null,\n      \"defaultValue\" : null,\n      \"validateType\" : \"\",\n      \"error\" : \"\",\n      \"expression\" : \"\",\n      \"children\" : [ ]\n    }, {\n      \"name\" : \"data\",\n      \"value\" : \"\",\n      \"description\" : \"\",\n      \"required\" : false,\n      \"dataType\" : \"Object\",\n      \"type\" : null,\n      \"defaultValue\" : null,\n      \"validateType\" : \"\",\n      \"error\" : \"\",\n      \"expression\" : \"\",\n      \"children\" : [ {\n        \"name\" : \"list\",\n        \"value\" : \"\",\n        \"description\" : \"\",\n        \"required\" : false,\n        \"dataType\" : \"Array\",\n        \"type\" : null,\n        \"defaultValue\" : null,\n        \"validateType\" : \"\",\n        \"error\" : \"\",\n        \"expression\" : \"\",\n        \"children\" : [ {\n          \"name\" : \"\",\n          \"value\" : \"\",\n          \"description\" : \"\",\n          \"required\" : false,\n          \"dataType\" : \"Object\",\n          \"type\" : null,\n          \"defaultValue\" : null,\n          \"validateType\" : \"\",\n          \"error\" : \"\",\n          \"expression\" : \"\",\n          \"children\" : [ {\n            \"name\" : \"value\",\n            \"value\" : \"Shanghai City\",\n            \"description\" : \"\",\n            \"required\" : false,\n            \"dataType\" : \"String\",\n            \"type\" : null,\n            \"defaultValue\" : null,\n            \"validateType\" : \"\",\n            \"error\" : \"\",\n            \"expression\" : \"\",\n            \"children\" : [ ]\n          }, {\n            \"name\" : \"uniqueEntities\",\n            \"value\" : \"50\",\n            \"description\" : \"\",\n            \"required\" : false,\n            \"dataType\" : \"String\",\n            \"type\" : null,\n            \"defaultValue\" : null,\n            \"validateType\" : \"\",\n            \"error\" : \"\",\n            \"expression\" : \"\",\n            \"children\" : [ ]\n          } ]\n        } ]\n      } ]\n    }, {\n      \"name\" : \"time\",\n      \"value\" : \"2023-08-31 02:54:15\",\n      \"description\" : \"\",\n      \"required\" : false,\n      \"dataType\" : \"String\",\n      \"type\" : null,\n      \"defaultValue\" : null,\n      \"validateType\" : \"\",\n      \"error\" : \"\",\n      \"expression\" : \"\",\n      \"children\" : [ ]\n    }, {\n      \"name\" : \"originalUrl\",\n      \"value\" : \"http://192.168.44.55:9999/?query=SELECT ip_admin_area as ip_admin_area, COUNTIF(DISTINCT(app_name),notEmpty(app_name)) as appCount, COUNTIF(DISTINCT(domain),notEmpty(domain)) as domainCount, COUNTIF(DISTINCT(ip),notEmpty(ip)) as ipCount, plus(ipCount,plus(appCount,domainCount)) AS totalCount FROM( SELECT e.app_name AS app_name, e.domain AS domain, e.ip AS ip, e.update_time AS update_time, e.ip_admin_area AS ip_admin_area FROM cn_entity_relation e  JOIN cn_ip_dynamic_attribute dr ON (e.ip = dr.ip)  WHERE e.update_time >= 1677898455 AND e.update_time < 1693450455 AND notEmpty(ip_admin_area)  AND dr.update_time >= 1677898455 AND dr.update_time < 1693450455  AND (dr.port = 80) ) GROUP BY ip_admin_area ORDER BY totalCount DESC limit 5&format=json&option=real-time\",\n      \"description\" : \"\",\n      \"required\" : false,\n      \"dataType\" : \"String\",\n      \"type\" : null,\n      \"defaultValue\" : null,\n      \"validateType\" : \"\",\n      \"error\" : \"\",\n      \"expression\" : \"\",\n      \"children\" : [ ]\n    }, {\n      \"name\" : \"message\",\n      \"value\" : \"success\",\n      \"description\" : \"\",\n      \"required\" : false,\n      \"dataType\" : \"String\",\n      \"type\" : null,\n      \"defaultValue\" : null,\n      \"validateType\" : \"\",\n      \"error\" : \"\",\n      \"expression\" : \"\",\n      \"children\" : [ ]\n    }, {\n      \"name\" : \"status\",\n      \"value\" : \"200\",\n      \"description\" : \"\",\n      \"required\" : false,\n      \"dataType\" : \"Integer\",\n      \"type\" : null,\n      \"defaultValue\" : null,\n      \"validateType\" : \"\",\n      \"error\" : \"\",\n      \"expression\" : \"\",\n      \"children\" : [ ]\n    } ]\n  }\n}\r\n================================\r\nimport cn.hutool.log.Log;\r\nimport cn.hutool.core.util.ObjectUtil;\r\nimport net.geedge.common.utils.FreeMarkerUtil as freeMarkerUtil;\r\nimport net.geedge.common.utils.JSONUtil as jsonUtil;\r\nimport net.geedge.common.utils.R as R;\r\nimport net.geedge.modules.entity.service.EntityExplorerService as EntityExplorerService;\r\nimport \'@/public/toHump\' as toHump;\r\nimport \'@/pre/timeHandleHalf\' as timeHandle;\r\nimport \'@/public/getGalaxyResult\' as getGalaxyResult;\r\n/**\r\n * 入参整理 包括调用公共函数\r\n */\r\nvar parameter={\r\n    \"resource\":resource,\r\n    \"startTime\":startTime,\r\n    \"endTime\":endTime,\r\n    \"dr\":false,\r\n    \"fqdnCategory\":false,\r\n    \"whois\":false,\r\n    \"appCategory\":false\r\n}\r\ntimeHandle(parameter);\r\nparameter.resource = EntityExplorerService.parseExpression(parameter.resource);\r\n/**\r\n * 如果resource表达式中涉及联表字段，将进行联表查询。例：Whois.Domain\r\n */\r\nparameter.filter = parameter.resource;\r\nvar param = EntityExplorerService.getAssociationQueryParam(parameter.resource);\r\nvar columns = [\"dr\",\"fqdnCategory\",\"whois\",\"appCategory\"];\r\nif(ObjectUtil.isNotEmpty(param)){\r\n    for (item in columns) {\r\n        for (k,v in param) {\r\n            parameter[k] = v;\r\n            if (v.asString().contains(item)){\r\n                parameter[item] = true;\r\n                break;\r\n            }\r\n        }\r\n    }\r\n}\r\nvar isAssociationQuery = EntityExplorerService.isAssociationQuery(parameter.resource);\r\nif(isAssociationQuery){\r\n    var sql = \"{\\\"sql\\\":\\\"SELECT ip_admin_area AS ip_admin_area, COUNT(DISTINCT(app_name)) FILTER (WHERE app_name != \'\') AS appCount, COUNT(DISTINCT(domain)) FILTER (WHERE domain != \'\') AS domainCount, COUNT(DISTINCT(ip)) FILTER (WHERE ip != \'\') AS ipCount, COUNT(DISTINCT(app_name)) FILTER (WHERE app_name != \'\') + COUNT(DISTINCT(domain)) FILTER (WHERE domain != \'\') + COUNT(DISTINCT(ip)) FILTER (WHERE ip != \'\') AS totalCount FROM( SELECT e.app_name AS app_name, e.domain AS domain, e.ip AS ip, e.update_time AS update_time, e.ip_admin_area AS ip_admin_area FROM clickhouse.cyber_narrator_galaxy.cn_entity_relation e <#if fqdnCategory> JOIN mysql.cn_api.cn_fqdn_category_built_in fqdnCategory ON (e.domain = fqdnCategory.fqdn <#if fqdnCategory\\.category_name?default(\'\')?trim? length gt 0> AND ${fqdnCategory\\.category_name} </#if> <#if fqdnCategory\\.category_group?default(\'\')?trim? length gt 0> AND ${fqdnCategory\\.category_group} </#if> <#if fqdnCategory\\.reputation_level?default(\'\')?trim? length gt 0> AND ${fqdnCategory\\.reputation_level}</#if>) </#if>  <#if whois> JOIN mysql.cn_api.cn_fqdn_who_is_built_in whois ON (e.domain = whois.fqdn <#if whois\\.whois_domain?default(\'\')?trim? length gt 0> AND ${whois\\.whois_domain} </#if> <#if whois\\.whois_registrant_name?default(\'\')?trim? length gt 0> AND ${whois\\.whois_registrant_name}</#if>) </#if> <#if appCategory> JOIN mysql.cn_api.cn_app_category_built_in appCategory ON (e.app_name = appCategory.app_name <#if appCategory\\.app_category?default(\'\')?trim? length gt 0> AND ${appCategory\\.app_category} </#if> <#if appCategory\\.app_subcategory?default(\'\')?trim? length gt 0> AND ${appCategory\\.app_subcategory} </#if> <#if appCategory\\.app_risk?default(\'\')?trim? length gt 0> AND ${appCategory\\.app_risk}</#if>) </#if> <#if dr\\.port?default(\'\')?trim? length gt 0> JOIN clickhouse.cyber_narrator_galaxy.cn_ip_dynamic_attribute dr ON (e.ip = dr.ip AND ${dr\\.port}) </#if> WHERE e.update_time >= ${startTime} AND e.update_time < ${endTime} AND ip_admin_area != \'\' <#if filter?default(\'\')?trim? length gt 0> ${filter} </#if> ) GROUP BY ip_admin_area ORDER BY totalCount DESC\\\"}\"\r\n    var aTemplate  = freeMarkerUtil.processTemplateIntoString(\"v1EntityExplorerTopAggCity\",sql,parameter);\r\n    var ares = jsonUtil.toBean(aTemplate,HashMap.class);\r\n    var log = Log.get();\r\n    log.info(ares.sql);\r\n    var aresult = db[\'trino\'].select(ares.sql);\r\n    var list=new ArrayList();\r\n    if(ObjectUtil.isNotEmpty(aresult)){\r\n        if(aresult.length > 0){\r\n            for(key,value in aresult){\r\n                var obj = {};\r\n                obj.value = value.ip_admin_area;\r\n                obj.uniqueEntities = value.totalCount;\r\n                list.add(obj);\r\n            }\r\n        }\r\n    }\r\n    var data = {};\r\n    data.list = list;\r\n    var url = db.select(\"select cvalue from sys_config where ckey = \'galaxy_url\'\");\r\n    return R.ok(data).put(\"originalUrl\",url.cvalue+ares.sql).put(\"status\",200);\r\n}\r\n/**\r\n * 接口sql相关内容整理 通过freemarker模板生成最后执行语句\r\n */\r\nvar q = \"{\\r\\n    \\\"query\\\": \\\"SELECT ip_admin_area as ip_admin_area, COUNTIF(DISTINCT(app_name),notEmpty(app_name)) as appCount, COUNTIF(DISTINCT(domain),notEmpty(domain)) as domainCount, COUNTIF(DISTINCT(ip),notEmpty(ip)) as ipCount, plus(ipCount,plus(appCount,domainCount)) AS totalCount FROM( SELECT e.app_name AS app_name, e.domain AS domain, e.ip AS ip, e.update_time AS update_time, e.ip_admin_area AS ip_admin_area FROM cn_entity_relation e <#if dr> JOIN cn_ip_dynamic_attribute dr ON (e.ip = dr.ip) </#if> WHERE e.update_time >= ${startTime} AND e.update_time < ${endTime} AND notEmpty(ip_admin_area) <#if dr> AND dr.update_time >= ${startTime} AND dr.update_time < ${endTime} </#if><#if filter?default(\\\'\\\')?trim? length gt 0> AND (${filter})</#if> ) GROUP BY ip_admin_area ORDER BY totalCount DESC limit 100\\\",\\r\\n    \\\"option\\\": \\\"real-time\\\",\\r\\n    \\\"format\\\": \\\"json\\\"\\r\\n}\"\r\n\r\nvar result  = freeMarkerUtil.processTemplateIntoString(\"v1EntityExplorerTopAggCity\",q,parameter);\r\n\r\n/**\r\n * 调用galaxy接口获取结果信息\r\n */\r\nvar res = jsonUtil.toBean(result,HashMap.class);\r\nvar result = getGalaxyResult(res);\r\n/**\r\n * 处理galaxy返回的数据结果 将新结果返回前端界面\r\n */\r\nvar data = {};\r\nvar list = new ArrayList();\r\nif(result.success){\r\n    for(key,value in result.data){\r\n        var obj = {};\r\n        obj.value = value.ip_admin_area;\r\n        obj.uniqueEntities = value.totalCount;\r\n        list.add(obj);\r\n    }\r\n}\r\ndata.list = list;\r\nreturn R.ok(data).put(\"originalUrl\",result.originalUrl).put(\"status\",200);');
 INSERT INTO `magic_api_file` VALUES ('/api/v1/entity/explorer/top/域名类别-实体基数统计.ms', '{\n  \"properties\" : { },\n  \"id\" : \"54cc297aa5274677bd874272e7843eda\",\n  \"script\" : null,\n  \"groupId\" : \"80392cde8e944a6e9c947b4ad8661c29\",\n  \"name\" : \"域名类别-实体基数统计\",\n  \"createTime\" : null,\n  \"updateTime\" : 1694415513220,\n  \"lock\" : null,\n  \"createBy\" : \"ljy\",\n  \"updateBy\" : \"ljy\",\n  \"path\" : \"/aggDomainCategory\",\n  \"method\" : \"GET\",\n  \"parameters\" : [ {\n    \"name\" : \"resource\",\n    \"value\" : \"\",\n    \"description\" : null,\n    \"required\" : false,\n    \"dataType\" : \"String\",\n    \"type\" : null,\n    \"defaultValue\" : null,\n    \"validateType\" : null,\n    \"error\" : null,\n    \"expression\" : null,\n    \"children\" : null\n  }, {\n    \"name\" : \"startTime\",\n    \"value\" : null,\n    \"description\" : null,\n    \"required\" : false,\n    \"dataType\" : \"Long\",\n    \"type\" : null,\n    \"defaultValue\" : null,\n    \"validateType\" : null,\n    \"error\" : null,\n    \"expression\" : null,\n    \"children\" : null\n  }, {\n    \"name\" : \"endTime\",\n    \"value\" : null,\n    \"description\" : null,\n    \"required\" : false,\n    \"dataType\" : \"Long\",\n    \"type\" : null,\n    \"defaultValue\" : null,\n    \"validateType\" : null,\n    \"error\" : null,\n    \"expression\" : null,\n    \"children\" : null\n  } ],\n  \"options\" : [ ],\n  \"requestBody\" : \"\",\n  \"headers\" : [ ],\n  \"paths\" : [ ],\n  \"responseBody\" : \"{\\n    \\\"code\\\": 999,\\n    \\\"time\\\": \\\"2023-08-29 07:57:15\\\",\\n    \\\"message\\\": \\\"404 null at Row:10~10,Col:32~36\\\\n\\\\nvar galaxyResult = httpRequest.get().getBody();\\\\r\\\\n                               ^^^^^            \\\"\\n}\",\n  \"description\" : null,\n  \"requestBodyDefinition\" : null,\n  \"responseBodyDefinition\" : {\n    \"name\" : \"\",\n    \"value\" : \"\",\n    \"description\" : \"\",\n    \"required\" : false,\n    \"dataType\" : \"Object\",\n    \"type\" : null,\n    \"defaultValue\" : null,\n    \"validateType\" : \"\",\n    \"error\" : \"\",\n    \"expression\" : \"\",\n    \"children\" : [ {\n      \"name\" : \"code\",\n      \"value\" : \"999\",\n      \"description\" : \"\",\n      \"required\" : false,\n      \"dataType\" : \"Integer\",\n      \"type\" : null,\n      \"defaultValue\" : null,\n      \"validateType\" : \"\",\n      \"error\" : \"\",\n      \"expression\" : \"\",\n      \"children\" : [ ]\n    }, {\n      \"name\" : \"time\",\n      \"value\" : \"2023-08-29 07:57:15\",\n      \"description\" : \"\",\n      \"required\" : false,\n      \"dataType\" : \"String\",\n      \"type\" : null,\n      \"defaultValue\" : null,\n      \"validateType\" : \"\",\n      \"error\" : \"\",\n      \"expression\" : \"\",\n      \"children\" : [ ]\n    }, {\n      \"name\" : \"message\",\n      \"value\" : \"404 null at Row:10~10,Col:32~36\\\\n\\\\nvar galaxyResult = httpRequest.get().getBody();\\\\r\\\\n                               ^^^^^\",\n      \"description\" : \"\",\n      \"required\" : false,\n      \"dataType\" : \"String\",\n      \"type\" : null,\n      \"defaultValue\" : null,\n      \"validateType\" : \"\",\n      \"error\" : \"\",\n      \"expression\" : \"\",\n      \"children\" : [ ]\n    } ]\n  }\n}\r\n================================\r\nimport cn.hutool.log.Log;\r\nimport cn.hutool.core.util.ObjectUtil;\r\nimport net.geedge.common.utils.FreeMarkerUtil as freeMarkerUtil;\r\nimport net.geedge.common.utils.JSONUtil as jsonUtil;\r\nimport net.geedge.common.utils.R as R;\r\nimport net.geedge.modules.entity.service.EntityExplorerService as EntityExplorerService;\r\nimport \'@/public/toHump\' as toHump;\r\nimport \'@/pre/timeHandleHalf\' as timeHandle;\r\nimport \'@/public/getGalaxyResult\' as getGalaxyResult;\r\n/**\r\n * 入参整理 包括调用公共函数\r\n */\r\nvar parameter={\r\n    \"resource\":resource,\r\n    \"startTime\":startTime,\r\n    \"endTime\":endTime,\r\n    \"dr\":false,\r\n    \"fqdnCategory\":false,\r\n    \"whois\":false,\r\n    \"appCategory\":false\r\n}\r\ntimeHandle(parameter);\r\nparameter.resource = EntityExplorerService.parseExpression(parameter.resource);\r\n/**\r\n * 如果resource表达式中涉及联表字段，将进行联表查询。例：Whois.Domain\r\n */\r\nparameter.filter = parameter.resource;\r\nvar param = EntityExplorerService.getAssociationQueryParam(parameter.resource);\r\nvar columns = [\"dr\",\"fqdnCategory\",\"whois\",\"appCategory\"];\r\nif(ObjectUtil.isNotEmpty(param)){\r\n    for (item in columns) {\r\n        for (k,v in param) {\r\n            parameter[k] = v;\r\n            if (v.asString().contains(item)){\r\n                parameter[item] = true;\r\n                break;\r\n            }\r\n        }\r\n    }\r\n}\r\nvar isAssociationQuery = EntityExplorerService.isAssociationQuery(parameter.resource);\r\nif(isAssociationQuery){\r\n    var sql = \"{\\\"sql\\\":\\\"SELECT domain_category_name AS domain_category_name, COUNT(DISTINCT(app_name)) FILTER (WHERE app_name != \'\') AS appCount, COUNT(DISTINCT(domain)) FILTER (WHERE domain != \'\') AS domainCount, COUNT(DISTINCT(ip)) FILTER (WHERE ip != \'\') AS ipCount, COUNT(DISTINCT(app_name)) FILTER (WHERE app_name != \'\') + COUNT(DISTINCT(domain)) FILTER (WHERE domain != \'\') + COUNT(DISTINCT(ip)) FILTER (WHERE ip != \'\') AS totalCount FROM( SELECT e.app_name AS app_name, e.domain AS domain, e.ip AS ip, e.update_time AS update_time, e.domain_category_name AS domain_category_name FROM clickhouse.cyber_narrator_galaxy.cn_entity_relation e <#if fqdnCategory> JOIN mysql.cn_api.cn_fqdn_category_built_in fqdnCategory ON (e.domain = fqdnCategory.fqdn <#if fqdnCategory\\.category_name?default(\'\')?trim? length gt 0> AND ${fqdnCategory\\.category_name} </#if> <#if fqdnCategory\\.category_group?default(\'\')?trim? length gt 0> AND ${fqdnCategory\\.category_group} </#if> <#if fqdnCategory\\.reputation_level?default(\'\')?trim? length gt 0> AND ${fqdnCategory\\.reputation_level}</#if>) </#if> <#if whois> JOIN mysql.cn_api.cn_fqdn_who_is_built_in whois ON (e.domain = whois.fqdn <#if whois\\.whois_domain?default(\'\')?trim? length gt 0> AND ${whois\\.whois_domain} </#if> <#if whois\\.whois_registrant_name?default(\'\')?trim? length gt 0> AND ${whois\\.whois_registrant_name}</#if>) </#if> <#if appCategory> JOIN mysql.cn_api.cn_app_category_built_in appCategory ON (e.app_name = appCategory.app_name <#if appCategory\\.app_category?default(\'\')?trim? length gt 0> AND ${appCategory\\.app_category} </#if> <#if appCategory\\.app_subcategory?default(\'\')?trim? length gt 0> AND ${appCategory\\.app_subcategory} </#if> <#if appCategory\\.app_risk?default(\'\')?trim? length gt 0> AND ${appCategory\\.app_risk}</#if>) </#if> <#if dr\\.port?default(\'\')?trim? length gt 0> JOIN clickhouse.cyber_narrator_galaxy.cn_ip_dynamic_attribute dr ON (e.ip = dr.ip AND ${dr\\.port}) </#if> WHERE e.update_time >= ${startTime} AND e.update_time < ${endTime} AND domain_category_name != \'\' <#if filter?default(\'\')?trim? length gt 0> ${filter} </#if> ) GROUP BY domain_category_name ORDER BY totalCount DESC\\\"}\"\r\n    var aTemplate  = freeMarkerUtil.processTemplateIntoString(\"v1EntityExplorerTopAggTag\",sql,parameter);\r\n    var ares = jsonUtil.toBean(aTemplate,HashMap.class);\r\n    var log = Log.get();\r\n    log.info(ares.sql);\r\n    var aresult = db[\'trino\'].select(ares.sql);\r\n    var list=new ArrayList();\r\n    if(ObjectUtil.isNotEmpty(aresult)){\r\n        if(aresult.length > 0){\r\n            for(key,value in aresult){\r\n                var obj = {};\r\n                obj.value = value.domain_category_name;\r\n                obj.uniqueEntities = value.totalCount;\r\n                list.add(obj);\r\n            }\r\n        }\r\n    }\r\n    var data = {};\r\n    data.list = list;\r\n    var url = db.select(\"select cvalue from sys_config where ckey = \'galaxy_url\'\");\r\n    return R.ok(data).put(\"originalUrl\",url.cvalue+ares.sql).put(\"status\",200);\r\n}\r\n/**\r\n * 接口sql相关内容整理 通过freemarker模板生成最后执行语句\r\n */\r\nvar q = \"{\\r\\n    \\\"query\\\": \\\"SELECT domain_category_name as domain_category_name, COUNTIF(DISTINCT(app_name),notEmpty(app_name)) as appCount, COUNTIF(DISTINCT(domain),notEmpty(domain)) as domainCount, COUNTIF(DISTINCT(ip),notEmpty(ip)) as ipCount, plus(ipCount,plus(appCount,domainCount)) AS totalCount FROM( SELECT e.app_name AS app_name, e.domain AS domain, e.ip AS ip, e.update_time AS update_time, e.domain_category_name AS domain_category_name FROM cn_entity_relation e <#if dr> JOIN cn_ip_dynamic_attribute dr ON (e.ip = dr.ip) </#if> WHERE e.update_time >= ${startTime} AND e.update_time < ${endTime} AND notEmpty(domain_category_name)<#if dr> AND dr.update_time >= ${startTime} AND dr.update_time < ${endTime} </#if><#if filter?default(\\\'\\\')?trim? length gt 0> AND (${filter}) </#if>) GROUP BY domain_category_name ORDER BY totalCount DESC limit 100\\\",\\r\\n    \\\"option\\\": \\\"real-time\\\",\\r\\n    \\\"format\\\": \\\"json\\\"\\r\\n}\"\r\n\r\nvar result  = freeMarkerUtil.processTemplateIntoString(\"v1EntityExplorerTopAggTag\",q,parameter);\r\n\r\n/**\r\n * 调用galaxy接口获取结果信息\r\n */\r\nvar res = jsonUtil.toBean(result,HashMap.class);\r\nvar result = getGalaxyResult(res);\r\n/**\r\n * 处理galaxy返回的数据结果 将新结果返回前端界面\r\n */\r\nvar data = {};\r\nvar list = new ArrayList();\r\nif(result.success){\r\n    for(key,value in result.data){\r\n        var obj = {};\r\n        obj.value = value.domain_category_name;\r\n        obj.uniqueEntities = value.totalCount;\r\n        list.add(obj);\r\n    }\r\n}\r\ndata.list = list;\r\nreturn R.ok(data).put(\"originalUrl\",result.originalUrl).put(\"status\",200);');
 INSERT INTO `magic_api_file` VALUES ('/api/v1/entity/explorer/top/标签-实体基数统计.ms', '{\n  \"properties\" : { },\n  \"id\" : \"5a271e27d449466fa061d4b95a915b68\",\n  \"script\" : null,\n  \"groupId\" : \"80392cde8e944a6e9c947b4ad8661c29\",\n  \"name\" : \"标签-实体基数统计\",\n  \"createTime\" : null,\n  \"updateTime\" : 1694415851785,\n  \"lock\" : null,\n  \"createBy\" : null,\n  \"updateBy\" : \"ljy\",\n  \"path\" : \"/aggTag\",\n  \"method\" : \"GET\",\n  \"parameters\" : [ {\n    \"name\" : \"resource\",\n    \"value\" : \"ip.port=80\",\n    \"description\" : null,\n    \"required\" : false,\n    \"dataType\" : \"String\",\n    \"type\" : null,\n    \"defaultValue\" : null,\n    \"validateType\" : null,\n    \"error\" : null,\n    \"expression\" : null,\n    \"children\" : null\n  }, {\n    \"name\" : \"startTime\",\n    \"value\" : null,\n    \"description\" : null,\n    \"required\" : false,\n    \"dataType\" : \"Long\",\n    \"type\" : null,\n    \"defaultValue\" : null,\n    \"validateType\" : null,\n    \"error\" : null,\n    \"expression\" : null,\n    \"children\" : null\n  }, {\n    \"name\" : \"endTime\",\n    \"value\" : null,\n    \"description\" : null,\n    \"required\" : false,\n    \"dataType\" : \"Long\",\n    \"type\" : null,\n    \"defaultValue\" : null,\n    \"validateType\" : null,\n    \"error\" : null,\n    \"expression\" : null,\n    \"children\" : null\n  } ],\n  \"options\" : [ ],\n  \"requestBody\" : \"\",\n  \"headers\" : [ ],\n  \"paths\" : [ ],\n  \"responseBody\" : \"{\\n    \\\"code\\\": 200,\\n    \\\"data\\\": {\\n        \\\"list\\\": []\\n    },\\n    \\\"time\\\": \\\"2023-08-31 02:53:50\\\",\\n    \\\"originalUrl\\\": \\\"http://192.168.44.55:9999/?query=SELECT entity_tag as entity_tag, COUNTIF(DISTINCT(app_name),notEmpty(app_name)) as appCount, COUNTIF(DISTINCT(domain),notEmpty(domain)) as domainCount, COUNTIF(DISTINCT(ip),notEmpty(ip)) as ipCount, plus(ipCount,plus(appCount,domainCount)) AS totalCount FROM( SELECT e.app_name AS app_name, e.domain AS domain, e.ip AS ip, e.update_time AS update_time, arrayJoin(e.entity_tags) AS entity_tag FROM cn_entity_relation e  JOIN cn_ip_dynamic_attribute dr ON (e.ip = dr.ip)  WHERE e.update_time >= 1677898430 AND e.update_time < 1693450430 AND notEmpty(e.entity_tags) AND dr.update_time >= 1677898430 AND dr.update_time < 1693450430  AND (dr.port = 80)  ) GROUP BY entity_tag ORDER BY totalCount DESC limit 5&format=json&option=real-time\\\",\\n    \\\"message\\\": \\\"success\\\",\\n    \\\"status\\\": 200\\n}\",\n  \"description\" : null,\n  \"requestBodyDefinition\" : null,\n  \"responseBodyDefinition\" : {\n    \"name\" : \"\",\n    \"value\" : \"\",\n    \"description\" : \"\",\n    \"required\" : false,\n    \"dataType\" : \"Object\",\n    \"type\" : null,\n    \"defaultValue\" : null,\n    \"validateType\" : \"\",\n    \"error\" : \"\",\n    \"expression\" : \"\",\n    \"children\" : [ {\n      \"name\" : \"code\",\n      \"value\" : \"200\",\n      \"description\" : \"\",\n      \"required\" : false,\n      \"dataType\" : \"Integer\",\n      \"type\" : null,\n      \"defaultValue\" : null,\n      \"validateType\" : \"\",\n      \"error\" : \"\",\n      \"expression\" : \"\",\n      \"children\" : [ ]\n    }, {\n      \"name\" : \"data\",\n      \"value\" : \"\",\n      \"description\" : \"\",\n      \"required\" : false,\n      \"dataType\" : \"Object\",\n      \"type\" : null,\n      \"defaultValue\" : null,\n      \"validateType\" : \"\",\n      \"error\" : \"\",\n      \"expression\" : \"\",\n      \"children\" : [ {\n        \"name\" : \"list\",\n        \"value\" : \"\",\n        \"description\" : \"\",\n        \"required\" : false,\n        \"dataType\" : \"Array\",\n        \"type\" : null,\n        \"defaultValue\" : null,\n        \"validateType\" : \"\",\n        \"error\" : \"\",\n        \"expression\" : \"\",\n        \"children\" : [ ]\n      } ]\n    }, {\n      \"name\" : \"time\",\n      \"value\" : \"2023-08-31 02:53:50\",\n      \"description\" : \"\",\n      \"required\" : false,\n      \"dataType\" : \"String\",\n      \"type\" : null,\n      \"defaultValue\" : null,\n      \"validateType\" : \"\",\n      \"error\" : \"\",\n      \"expression\" : \"\",\n      \"children\" : [ ]\n    }, {\n      \"name\" : \"originalUrl\",\n      \"value\" : \"http://192.168.44.55:9999/?query=SELECT entity_tag as entity_tag, COUNTIF(DISTINCT(app_name),notEmpty(app_name)) as appCount, COUNTIF(DISTINCT(domain),notEmpty(domain)) as domainCount, COUNTIF(DISTINCT(ip),notEmpty(ip)) as ipCount, plus(ipCount,plus(appCount,domainCount)) AS totalCount FROM( SELECT e.app_name AS app_name, e.domain AS domain, e.ip AS ip, e.update_time AS update_time, arrayJoin(e.entity_tags) AS entity_tag FROM cn_entity_relation e  JOIN cn_ip_dynamic_attribute dr ON (e.ip = dr.ip)  WHERE e.update_time >= 1677898430 AND e.update_time < 1693450430 AND notEmpty(e.entity_tags) AND dr.update_time >= 1677898430 AND dr.update_time < 1693450430  AND (dr.port = 80)  ) GROUP BY entity_tag ORDER BY totalCount DESC limit 5&format=json&option=real-time\",\n      \"description\" : \"\",\n      \"required\" : false,\n      \"dataType\" : \"String\",\n      \"type\" : null,\n      \"defaultValue\" : null,\n      \"validateType\" : \"\",\n      \"error\" : \"\",\n      \"expression\" : \"\",\n      \"children\" : [ ]\n    }, {\n      \"name\" : \"message\",\n      \"value\" : \"success\",\n      \"description\" : \"\",\n      \"required\" : false,\n      \"dataType\" : \"String\",\n      \"type\" : null,\n      \"defaultValue\" : null,\n      \"validateType\" : \"\",\n      \"error\" : \"\",\n      \"expression\" : \"\",\n      \"children\" : [ ]\n    }, {\n      \"name\" : \"status\",\n      \"value\" : \"200\",\n      \"description\" : \"\",\n      \"required\" : false,\n      \"dataType\" : \"Integer\",\n      \"type\" : null,\n      \"defaultValue\" : null,\n      \"validateType\" : \"\",\n      \"error\" : \"\",\n      \"expression\" : \"\",\n      \"children\" : [ ]\n    } ]\n  }\n}\r\n================================\r\nimport cn.hutool.log.Log;\r\nimport cn.hutool.core.util.ObjectUtil;\r\nimport net.geedge.common.utils.FreeMarkerUtil as freeMarkerUtil;\r\nimport net.geedge.common.utils.JSONUtil as jsonUtil;\r\nimport net.geedge.common.utils.R as R;\r\nimport net.geedge.modules.entity.service.EntityExplorerService as EntityExplorerService;\r\nimport \'@/public/toHump\' as toHump;\r\nimport \'@/pre/timeHandleHalf\' as timeHandle;\r\nimport \'@/public/getGalaxyResult\' as getGalaxyResult;\r\n/**\r\n * 入参整理 包括调用公共函数\r\n */\r\nvar parameter={\r\n    \"resource\":resource,\r\n    \"startTime\":startTime,\r\n    \"endTime\":endTime,\r\n    \"dr\":false,\r\n    \"fqdnCategory\":false,\r\n    \"whois\":false,\r\n    \"appCategory\":false\r\n}\r\ntimeHandle(parameter);\r\nparameter.resource = EntityExplorerService.parseExpression(parameter.resource);\r\n/**\r\n * 如果resource表达式中涉及联表字段，将进行联表查询。例：Whois.Domain\r\n */\r\nparameter.filter = parameter.resource;\r\nvar param = EntityExplorerService.getAssociationQueryParam(parameter.resource);\r\nvar columns = [\"dr\",\"fqdnCategory\",\"whois\",\"appCategory\"];\r\nif(ObjectUtil.isNotEmpty(param)){\r\n    for (item in columns) {\r\n        for (k,v in param) {\r\n            parameter[k] = v;\r\n            if (v.asString().contains(item)){\r\n                parameter[item] = true;\r\n                break;\r\n            }\r\n        }\r\n    }\r\n}\r\nvar isAssociationQuery = EntityExplorerService.isAssociationQuery(parameter.resource);\r\nif(isAssociationQuery){\r\n    var sql = \"{\\\"sql\\\":\\\"SELECT entity_tags AS entity_tags, COUNT(DISTINCT(app_name)) FILTER (WHERE app_name != \'\') AS appCount, COUNT(DISTINCT(domain)) FILTER (WHERE domain != \'\') AS domainCount, COUNT(DISTINCT(ip)) FILTER (WHERE ip != \'\') AS ipCount, COUNT(DISTINCT(app_name)) FILTER (WHERE app_name != \'\') + COUNT(DISTINCT(domain)) FILTER (WHERE domain != \'\') + COUNT(DISTINCT(ip)) FILTER (WHERE ip != \'\') AS totalCount FROM( SELECT e.app_name AS app_name, e.domain AS domain, e.ip AS ip, e.update_time AS update_time, e.entity_tags AS entity_tags FROM clickhouse.cyber_narrator_galaxy.cn_entity_relation e <#if fqdnCategory> JOIN mysql.cn_api.cn_fqdn_category_built_in fqdnCategory ON (e.domain = fqdnCategory.fqdn <#if fqdnCategory\\.category_name?default(\'\')?trim? length gt 0> AND ${fqdnCategory\\.category_name} </#if> <#if fqdnCategory\\.category_group?default(\'\')?trim? length gt 0> AND ${fqdnCategory\\.category_group} </#if> <#if fqdnCategory\\.reputation_level?default(\'\')?trim? length gt 0> AND ${fqdnCategory\\.reputation_level}</#if>) </#if>	 <#if whois>JOIN mysql.cn_api.cn_fqdn_who_is_built_in whois ON (e.domain = whois.fqdn <#if whois\\.whois_domain?default(\'\')?trim? length gt 0> AND ${whois\\.whois_domain} </#if> <#if whois\\.whois_registrant_name?default(\'\')?trim? length gt 0> AND ${whois\\.whois_registrant_name}</#if>) </#if> <#if appCategory>JOIN mysql.cn_api.cn_app_category_built_in appCategory ON (e.app_name = appCategory.app_name <#if appCategory\\.app_category?default(\'\')?trim? length gt 0> AND ${appCategory\\.app_category} </#if> <#if appCategory\\.app_subcategory?default(\'\')?trim? length gt 0> AND ${appCategory\\.app_subcategory} </#if> <#if appCategory\\.app_risk?default(\'\')?trim? length gt 0> AND ${appCategory\\.app_risk}</#if>) </#if> <#if dr\\.port?default(\'\')?trim? length gt 0>JOIN clickhouse.cyber_narrator_galaxy.cn_ip_dynamic_attribute dr ON (e.ip = dr.ip AND ${dr\\.port}) </#if> WHERE e.update_time >= ${startTime} AND e.update_time < ${endTime} AND entity_tags != \'\' <#if filter?default(\'\')?trim? length gt 0> ${filter} </#if> ) GROUP BY entity_tags ORDER BY totalCount DESC\\\"}\"\r\n    var aTemplate  = freeMarkerUtil.processTemplateIntoString(\"v1EntityExplorerTopAggTag\",sql,parameter);\r\n    var ares = jsonUtil.toBean(aTemplate,HashMap.class);\r\n    var log = Log.get();\r\n    log.info(ares.sql);\r\n    var aresult = db[\'trino\'].select(ares.sql);\r\n    var list=new ArrayList();\r\n    if(ObjectUtil.isNotEmpty(aresult)){\r\n        if(aresult.length > 0){\r\n            for(key,value in aresult){\r\n                var obj = {};\r\n                obj.value = value.entity_tags;\r\n                obj.uniqueEntities = value.totalCount;\r\n                list.add(obj);\r\n            }\r\n        }\r\n    }\r\n    var data = {};\r\n    data.list = list;\r\n    var url = db.select(\"select cvalue from sys_config where ckey = \'galaxy_url\'\");\r\n    return R.ok(data).put(\"originalUrl\",url.cvalue+ares.sql);\r\n}\r\n/**\r\n * 接口sql相关内容整理 通过freemarker模板生成最后执行语句\r\n */\r\nvar q = \"{\\r\\n    \\\"query\\\": \\\"SELECT entity_tag as entity_tag, COUNTIF(DISTINCT(app_name),notEmpty(app_name)) as appCount, COUNTIF(DISTINCT(domain),notEmpty(domain)) as domainCount, COUNTIF(DISTINCT(ip),notEmpty(ip)) as ipCount, plus(ipCount,plus(appCount,domainCount)) AS totalCount FROM( SELECT e.app_name AS app_name, e.domain AS domain, e.ip AS ip, e.update_time AS update_time, arrayJoin(e.entity_tags) AS entity_tag FROM cn_entity_relation e <#if dr> JOIN cn_ip_dynamic_attribute dr ON (e.ip = dr.ip) </#if> WHERE e.update_time >= ${startTime} AND e.update_time < ${endTime} AND notEmpty(e.entity_tags)<#if dr> AND dr.update_time >= ${startTime} AND dr.update_time < ${endTime} </#if><#if filter?default(\\\'\\\')?trim? length gt 0> AND (${filter}) </#if> ) GROUP BY entity_tag ORDER BY totalCount DESC limit 100\\\",\\r\\n    \\\"option\\\": \\\"real-time\\\",\\r\\n    \\\"format\\\": \\\"json\\\"\\r\\n}\"\r\n\r\nvar result  = freeMarkerUtil.processTemplateIntoString(\"v1EntityExplorerTopAggTag\",q,parameter);\r\n\r\n/**\r\n * 调用galaxy接口获取结果信息\r\n */\r\nvar res = jsonUtil.toBean(result,HashMap.class);\r\nvar result = getGalaxyResult(res);\r\n/**\r\n * 处理galaxy返回的数据结果 将新结果返回前端界面\r\n */\r\nvar data = {};\r\nvar list = new ArrayList();\r\nif(result.success){\r\n    for(key,value in result.data){\r\n        var obj = {};\r\n        obj.value = value.entity_tag;\r\n        obj.uniqueEntities = value.totalCount;\r\n        list.add(obj);\r\n    }\r\n}\r\ndata.list = list;\r\nreturn R.ok(data).put(\"originalUrl\",result.originalUrl).put(\"status\",200);');
-INSERT INTO `magic_api_file` VALUES ('/api/v1/entity/explorer/top/端口-实体基数统计.ms', '{\n  \"properties\" : { },\n  \"id\" : \"2c0346e6e37f45b6bcb1f0fb0841a1c7\",\n  \"script\" : null,\n  \"groupId\" : \"80392cde8e944a6e9c947b4ad8661c29\",\n  \"name\" : \"端口-实体基数统计\",\n  \"createTime\" : null,\n  \"updateTime\" : 1694415495276,\n  \"lock\" : null,\n  \"createBy\" : null,\n  \"updateBy\" : \"ljy\",\n  \"path\" : \"/aggPort\",\n  \"method\" : \"GET\",\n  \"parameters\" : [ {\n    \"name\" : \"resource\",\n    \"value\" : \"\",\n    \"description\" : null,\n    \"required\" : false,\n    \"dataType\" : \"String\",\n    \"type\" : null,\n    \"defaultValue\" : null,\n    \"validateType\" : null,\n    \"error\" : null,\n    \"expression\" : null,\n    \"children\" : null\n  }, {\n    \"name\" : \"startTime\",\n    \"value\" : null,\n    \"description\" : null,\n    \"required\" : false,\n    \"dataType\" : \"String\",\n    \"type\" : null,\n    \"defaultValue\" : null,\n    \"validateType\" : null,\n    \"error\" : null,\n    \"expression\" : null,\n    \"children\" : null\n  }, {\n    \"name\" : \"endTime\",\n    \"value\" : null,\n    \"description\" : null,\n    \"required\" : false,\n    \"dataType\" : \"String\",\n    \"type\" : null,\n    \"defaultValue\" : null,\n    \"validateType\" : null,\n    \"error\" : null,\n    \"expression\" : null,\n    \"children\" : null\n  } ],\n  \"options\" : [ ],\n  \"requestBody\" : \"\",\n  \"headers\" : [ ],\n  \"paths\" : [ ],\n  \"responseBody\" : \"{\\n    \\\"code\\\": 200,\\n    \\\"data\\\": {\\n        \\\"list\\\": [{\\n            \\\"port\\\": \\\"443\\\",\\n            \\\"l7Protocol\\\": \\\"\\\",\\n            \\\"uniqueEntities\\\": \\\"998\\\"\\n        }, {\\n            \\\"port\\\": \\\"80\\\",\\n            \\\"l7Protocol\\\": \\\"\\\",\\n            \\\"uniqueEntities\\\": \\\"386\\\"\\n        }, {\\n            \\\"port\\\": \\\"21\\\",\\n            \\\"l7Protocol\\\": \\\"\\\",\\n            \\\"uniqueEntities\\\": \\\"21\\\"\\n        }, {\\n            \\\"port\\\": \\\"7680\\\",\\n            \\\"l7Protocol\\\": \\\"\\\",\\n            \\\"uniqueEntities\\\": \\\"21\\\"\\n        }, {\\n            \\\"port\\\": \\\"3389\\\",\\n            \\\"l7Protocol\\\": \\\"\\\",\\n            \\\"uniqueEntities\\\": \\\"19\\\"\\n        }]\\n    },\\n    \\\"time\\\": \\\"2023-08-31 02:54:46\\\",\\n    \\\"originalUrl\\\": \\\"http://192.168.44.55:9999/?query=SELECT port AS port, l7_protocol AS l7_protocol, COUNTIF(DISTINCT(app_name),notEmpty(app_name)) as appCount, COUNTIF(DISTINCT(domain),notEmpty(domain)) as domainCount, COUNTIF(DISTINCT(ip),notEmpty(ip)) as ipCount, plus(ipCount,plus(appCount,domainCount)) AS totalCount FROM( SELECT e.app_name AS app_name, e.domain AS domain, e.ip AS ip, e.update_time AS update_time, e.ip_admin_area AS ip_admin_area, dr.port AS port, dr.l7_protocol AS l7_protocol FROM cn_entity_relation e JOIN cn_ip_dynamic_attribute dr ON (e.ip = dr.ip) WHERE e.update_time >= 1677898486 AND e.update_time < 1693450486) GROUP BY port,l7_protocol ORDER BY totalCount DESC limit 5&format=json&option=real-time\\\",\\n    \\\"message\\\": \\\"success\\\",\\n    \\\"status\\\": 200\\n}\",\n  \"description\" : null,\n  \"requestBodyDefinition\" : null,\n  \"responseBodyDefinition\" : {\n    \"name\" : \"\",\n    \"value\" : \"\",\n    \"description\" : \"\",\n    \"required\" : false,\n    \"dataType\" : \"Object\",\n    \"type\" : null,\n    \"defaultValue\" : null,\n    \"validateType\" : \"\",\n    \"error\" : \"\",\n    \"expression\" : \"\",\n    \"children\" : [ {\n      \"name\" : \"code\",\n      \"value\" : \"200\",\n      \"description\" : \"\",\n      \"required\" : false,\n      \"dataType\" : \"Integer\",\n      \"type\" : null,\n      \"defaultValue\" : null,\n      \"validateType\" : \"\",\n      \"error\" : \"\",\n      \"expression\" : \"\",\n      \"children\" : [ ]\n    }, {\n      \"name\" : \"data\",\n      \"value\" : \"\",\n      \"description\" : \"\",\n      \"required\" : false,\n      \"dataType\" : \"Object\",\n      \"type\" : null,\n      \"defaultValue\" : null,\n      \"validateType\" : \"\",\n      \"error\" : \"\",\n      \"expression\" : \"\",\n      \"children\" : [ {\n        \"name\" : \"list\",\n        \"value\" : \"\",\n        \"description\" : \"\",\n        \"required\" : false,\n        \"dataType\" : \"Array\",\n        \"type\" : null,\n        \"defaultValue\" : null,\n        \"validateType\" : \"\",\n        \"error\" : \"\",\n        \"expression\" : \"\",\n        \"children\" : [ {\n          \"name\" : \"\",\n          \"value\" : \"\",\n          \"description\" : \"\",\n          \"required\" : false,\n          \"dataType\" : \"Object\",\n          \"type\" : null,\n          \"defaultValue\" : null,\n          \"validateType\" : \"\",\n          \"error\" : \"\",\n          \"expression\" : \"\",\n          \"children\" : [ {\n            \"name\" : \"port\",\n            \"value\" : \"443\",\n            \"description\" : \"\",\n            \"required\" : false,\n            \"dataType\" : \"String\",\n            \"type\" : null,\n            \"defaultValue\" : null,\n            \"validateType\" : \"\",\n            \"error\" : \"\",\n            \"expression\" : \"\",\n            \"children\" : [ ]\n          }, {\n            \"name\" : \"l7Protocol\",\n            \"value\" : \"\",\n            \"description\" : \"\",\n            \"required\" : false,\n            \"dataType\" : \"String\",\n            \"type\" : null,\n            \"defaultValue\" : null,\n            \"validateType\" : \"\",\n            \"error\" : \"\",\n            \"expression\" : \"\",\n            \"children\" : [ ]\n          }, {\n            \"name\" : \"uniqueEntities\",\n            \"value\" : \"998\",\n            \"description\" : \"\",\n            \"required\" : false,\n            \"dataType\" : \"String\",\n            \"type\" : null,\n            \"defaultValue\" : null,\n            \"validateType\" : \"\",\n            \"error\" : \"\",\n            \"expression\" : \"\",\n            \"children\" : [ ]\n          } ]\n        } ]\n      } ]\n    }, {\n      \"name\" : \"time\",\n      \"value\" : \"2023-08-31 02:54:46\",\n      \"description\" : \"\",\n      \"required\" : false,\n      \"dataType\" : \"String\",\n      \"type\" : null,\n      \"defaultValue\" : null,\n      \"validateType\" : \"\",\n      \"error\" : \"\",\n      \"expression\" : \"\",\n      \"children\" : [ ]\n    }, {\n      \"name\" : \"originalUrl\",\n      \"value\" : \"http://192.168.44.55:9999/?query=SELECT port AS port, l7_protocol AS l7_protocol, COUNTIF(DISTINCT(app_name),notEmpty(app_name)) as appCount, COUNTIF(DISTINCT(domain),notEmpty(domain)) as domainCount, COUNTIF(DISTINCT(ip),notEmpty(ip)) as ipCount, plus(ipCount,plus(appCount,domainCount)) AS totalCount FROM( SELECT e.app_name AS app_name, e.domain AS domain, e.ip AS ip, e.update_time AS update_time, e.ip_admin_area AS ip_admin_area, dr.port AS port, dr.l7_protocol AS l7_protocol FROM cn_entity_relation e JOIN cn_ip_dynamic_attribute dr ON (e.ip = dr.ip) WHERE e.update_time >= 1677898486 AND e.update_time < 1693450486) GROUP BY port,l7_protocol ORDER BY totalCount DESC limit 5&format=json&option=real-time\",\n      \"description\" : \"\",\n      \"required\" : false,\n      \"dataType\" : \"String\",\n      \"type\" : null,\n      \"defaultValue\" : null,\n      \"validateType\" : \"\",\n      \"error\" : \"\",\n      \"expression\" : \"\",\n      \"children\" : [ ]\n    }, {\n      \"name\" : \"message\",\n      \"value\" : \"success\",\n      \"description\" : \"\",\n      \"required\" : false,\n      \"dataType\" : \"String\",\n      \"type\" : null,\n      \"defaultValue\" : null,\n      \"validateType\" : \"\",\n      \"error\" : \"\",\n      \"expression\" : \"\",\n      \"children\" : [ ]\n    }, {\n      \"name\" : \"status\",\n      \"value\" : \"200\",\n      \"description\" : \"\",\n      \"required\" : false,\n      \"dataType\" : \"Integer\",\n      \"type\" : null,\n      \"defaultValue\" : null,\n      \"validateType\" : \"\",\n      \"error\" : \"\",\n      \"expression\" : \"\",\n      \"children\" : [ ]\n    } ]\n  }\n}\r\n================================\r\nimport cn.hutool.log.Log;\r\nimport cn.hutool.core.util.ObjectUtil;\r\nimport net.geedge.common.utils.FreeMarkerUtil as freeMarkerUtil;\r\nimport net.geedge.common.utils.JSONUtil as jsonUtil;\r\nimport net.geedge.common.utils.R as R;\r\nimport \'@/public/toHump\' as toHump;\r\nimport net.geedge.modules.entity.service.EntityExplorerService as EntityExplorerService;\r\nimport \'@/pre/timeHandleHalf\' as timeHandle;\r\nimport \'@/public/getGalaxyResult\' as getGalaxyResult;\r\n/**\r\n * 入参整理 包括调用公共函数\r\n */\r\nvar parameter={\r\n    \"resource\":resource,\r\n    \"startTime\":startTime,\r\n    \"endTime\":endTime,\r\n    \"dr\":false,\r\n    \"fqdnCategory\":false,\r\n    \"whois\":false,\r\n    \"appCategory\":false\r\n}\r\ntimeHandle(parameter);\r\nparameter.resource = EntityExplorerService.parseExpression(parameter.resource);\r\n/**\r\n * 如果resource表达式中涉及联表字段，将进行联表查询。例：Whois.Domain\r\n */\r\nparameter.filter = parameter.resource;\r\nvar param = EntityExplorerService.getAssociationQueryParam(parameter.resource);\r\nvar columns = [\"dr\",\"fqdnCategory\",\"whois\",\"appCategory\"];\r\nif(ObjectUtil.isNotEmpty(param)){\r\n    for (item in columns) {\r\n        for (k,v in param) {\r\n            parameter[k] = v;\r\n            if (v.asString().contains(item)){\r\n                parameter[item] = true;\r\n                break;\r\n            }\r\n        }\r\n    }\r\n}\r\nvar isAssociationQuery = EntityExplorerService.isAssociationQuery(parameter.resource);\r\nif(isAssociationQuery){\r\n    var sql = \"{\\\"sql\\\":\\\"SELECT port AS port, l7_protocol AS l7_protocol, COUNT(DISTINCT(app_name)) FILTER (WHERE app_name != \'\') AS appCount, COUNT(DISTINCT(domain)) FILTER (WHERE domain != \'\') AS domainCount, COUNT(DISTINCT(ip)) FILTER (WHERE ip != \'\') AS ipCount, COUNT(DISTINCT(app_name)) FILTER (WHERE app_name != \'\') + COUNT(DISTINCT(domain)) FILTER (WHERE domain != \'\') + COUNT(DISTINCT(ip)) FILTER (WHERE ip != \'\') AS totalCount FROM( SELECT e.app_name AS app_name, e.domain AS domain, e.ip AS ip, e.update_time AS update_time, dr.port AS port, dr.l7_protocol AS l7_protocol FROM clickhouse.cyber_narrator_galaxy.cn_entity_relation e JOIN clickhouse.cyber_narrator_galaxy.cn_ip_dynamic_attribute dr ON (e.ip = dr.ip AND dr.update_time >= ${startTime} AND dr.update_time < ${endTime} <#if dr\\.port?default(\'\')?trim? length gt 0> AND ${dr\\.port}</#if>) <#if fqdnCategory> JOIN mysql.cn_api.cn_fqdn_category_built_in fqdnCategory ON (e.domain = fqdnCategory.fqdn <#if fqdnCategory\\.category_name?default(\'\')?trim? length gt 0> AND ${fqdnCategory\\.category_name} </#if> <#if fqdnCategory\\.category_group?default(\'\')?trim? length gt 0> AND ${fqdnCategory\\.category_group} </#if> <#if fqdnCategory\\.reputation_level?default(\'\')?trim? length gt 0> AND ${fqdnCategory\\.reputation_level}</#if>) </#if> <#if whois> JOIN mysql.cn_api.cn_fqdn_who_is_built_in whois ON (e.domain = whois.fqdn <#if whois\\.whois_domain?default(\'\')?trim? length gt 0> AND ${whois\\.whois_domain} </#if> <#if whois\\.whois_registrant_name?default(\'\')?trim? length gt 0> AND ${whois\\.whois_registrant_name}</#if>) </#if> <#if appCategory> JOIN mysql.cn_api.cn_app_category_built_in appCategory ON (e.app_name = appCategory.app_name <#if appCategory\\.app_category?default(\'\')?trim? length gt 0> AND ${appCategory\\.app_category} </#if> <#if appCategory\\.app_subcategory?default(\'\')?trim? length gt 0> AND ${appCategory\\.app_subcategory} </#if> <#if appCategory\\.app_risk?default(\'\')?trim? length gt 0> AND ${appCategory\\.app_risk}</#if>) </#if> WHERE e.update_time >= ${startTime} AND e.update_time < ${endTime} <#if filter?default(\'\')?trim? length gt 0> ${filter} </#if> ) GROUP BY port,l7_protocol ORDER BY totalCount DESC\\\"}\"\r\n    var aTemplate  = freeMarkerUtil.processTemplateIntoString(\"v1EntityExplorerTopAggPort\",sql,parameter);\r\n    var ares = jsonUtil.toBean(aTemplate,HashMap.class);\r\n    var log = Log.get();\r\n    log.info(ares.sql);\r\n    var aresult = db[\'trino\'].select(ares.sql);\r\n    var list=new ArrayList();\r\n    if(ObjectUtil.isNotEmpty(aresult)){\r\n        if(aresult.length > 0){\r\n            for(key,value in aresult){\r\n                var obj = {};\r\n                obj.port = value.port;\r\n                obj.l7Protocol = value.l7_protocol;\r\n                obj.uniqueEntities = value.totalCount;\r\n                list.add(obj);\r\n            }\r\n        }\r\n    }\r\n    var data = {};\r\n    data.list = list;\r\n    var url = db.select(\"select cvalue from sys_config where ckey = \'galaxy_url\'\");\r\n    return R.ok(data).put(\"originalUrl\",url.cvalue+ares.sql).put(\"status\",200);\r\n}\r\n/**\r\n * 接口sql相关内容整理 通过freemarker模板生成最后执行语句\r\n */\r\nvar q = \"{\\r\\n    \\\"query\\\": \\\"SELECT port AS port, l7_protocol AS l7_protocol, COUNTIF(DISTINCT(app_name),notEmpty(app_name)) as appCount, COUNTIF(DISTINCT(domain),notEmpty(domain)) as domainCount, COUNTIF(DISTINCT(ip),notEmpty(ip)) as ipCount, plus(ipCount,plus(appCount,domainCount)) AS totalCount FROM( SELECT e.app_name AS app_name, e.domain AS domain, e.ip AS ip, e.update_time AS update_time, e.ip_admin_area AS ip_admin_area, dr.port AS port, dr.l7_protocol AS l7_protocol FROM cn_entity_relation e JOIN cn_ip_dynamic_attribute dr ON (e.ip = dr.ip) WHERE e.update_time >= ${startTime} AND e.update_time < ${endTime}<#if dr> AND dr.update_time >= ${startTime} AND dr.update_time < ${endTime} </#if><#if filter?default(\\\'\\\')?trim? length gt 0> AND (${filter}) </#if>) GROUP BY port,l7_protocol ORDER BY totalCount DESC limit 100\\\",\\r\\n    \\\"option\\\": \\\"real-time\\\",\\r\\n    \\\"format\\\": \\\"json\\\"\\r\\n}\"\r\n\r\nvar result  = freeMarkerUtil.processTemplateIntoString(\"v1EntityExplorerTopAggTag\",q,parameter);\r\n\r\n/**\r\n * 调用galaxy接口获取结果信息\r\n */\r\nvar res = jsonUtil.toBean(result,HashMap.class);\r\nvar result = getGalaxyResult(res);\r\n/**\r\n * 处理galaxy返回的数据结果 将新结果返回前端界面\r\n */\r\nvar data = {};\r\nvar list = new ArrayList();\r\nif(result.success){\r\n    for(key,value in result.data){\r\n        var obj = {};\r\n        obj.port = value.port;\r\n        obj.l7Protocol = value.l7_protocol;\r\n        obj.uniqueEntities = value.totalCount;\r\n        list.add(obj);\r\n    }\r\n}\r\ndata.list = list;\r\nreturn R.ok(data).put(\"originalUrl\",result.originalUrl).put(\"status\",200);');
+INSERT INTO `magic_api_file` VALUES ('/api/v1/entity/explorer/top/端口-实体基数统计.ms', '{\n  \"properties\" : { },\n  \"id\" : \"2c0346e6e37f45b6bcb1f0fb0841a1c7\",\n  \"script\" : null,\n  \"groupId\" : \"80392cde8e944a6e9c947b4ad8661c29\",\n  \"name\" : \"端口-实体基数统计\",\n  \"createTime\" : null,\n  \"updateTime\" : 1695103446102,\n  \"lock\" : null,\n  \"createBy\" : null,\n  \"updateBy\" : \"ljy\",\n  \"path\" : \"/aggPort\",\n  \"method\" : \"GET\",\n  \"parameters\" : [ {\n    \"name\" : \"resource\",\n    \"value\" : \"\",\n    \"description\" : null,\n    \"required\" : false,\n    \"dataType\" : \"String\",\n    \"type\" : null,\n    \"defaultValue\" : null,\n    \"validateType\" : null,\n    \"error\" : null,\n    \"expression\" : null,\n    \"children\" : null\n  }, {\n    \"name\" : \"startTime\",\n    \"value\" : null,\n    \"description\" : null,\n    \"required\" : false,\n    \"dataType\" : \"String\",\n    \"type\" : null,\n    \"defaultValue\" : null,\n    \"validateType\" : null,\n    \"error\" : null,\n    \"expression\" : null,\n    \"children\" : null\n  }, {\n    \"name\" : \"endTime\",\n    \"value\" : null,\n    \"description\" : null,\n    \"required\" : false,\n    \"dataType\" : \"String\",\n    \"type\" : null,\n    \"defaultValue\" : null,\n    \"validateType\" : null,\n    \"error\" : null,\n    \"expression\" : null,\n    \"children\" : null\n  } ],\n  \"options\" : [ ],\n  \"requestBody\" : \"\",\n  \"headers\" : [ ],\n  \"paths\" : [ ],\n  \"responseBody\" : \"{\\n    \\\"code\\\": 200,\\n    \\\"data\\\": {\\n        \\\"list\\\": [{\\n            \\\"port\\\": \\\"443\\\",\\n            \\\"l7Protocol\\\": \\\"\\\",\\n            \\\"uniqueEntities\\\": \\\"998\\\"\\n        }, {\\n            \\\"port\\\": \\\"80\\\",\\n            \\\"l7Protocol\\\": \\\"\\\",\\n            \\\"uniqueEntities\\\": \\\"386\\\"\\n        }, {\\n            \\\"port\\\": \\\"21\\\",\\n            \\\"l7Protocol\\\": \\\"\\\",\\n            \\\"uniqueEntities\\\": \\\"21\\\"\\n        }, {\\n            \\\"port\\\": \\\"7680\\\",\\n            \\\"l7Protocol\\\": \\\"\\\",\\n            \\\"uniqueEntities\\\": \\\"21\\\"\\n        }, {\\n            \\\"port\\\": \\\"3389\\\",\\n            \\\"l7Protocol\\\": \\\"\\\",\\n            \\\"uniqueEntities\\\": \\\"19\\\"\\n        }]\\n    },\\n    \\\"time\\\": \\\"2023-08-31 02:54:46\\\",\\n    \\\"originalUrl\\\": \\\"http://192.168.44.55:9999/?query=SELECT port AS port, l7_protocol AS l7_protocol, COUNTIF(DISTINCT(app_name),notEmpty(app_name)) as appCount, COUNTIF(DISTINCT(domain),notEmpty(domain)) as domainCount, COUNTIF(DISTINCT(ip),notEmpty(ip)) as ipCount, plus(ipCount,plus(appCount,domainCount)) AS totalCount FROM( SELECT e.app_name AS app_name, e.domain AS domain, e.ip AS ip, e.update_time AS update_time, e.ip_admin_area AS ip_admin_area, dr.port AS port, dr.l7_protocol AS l7_protocol FROM cn_entity_relation e JOIN cn_ip_dynamic_attribute dr ON (e.ip = dr.ip) WHERE e.update_time >= 1677898486 AND e.update_time < 1693450486) GROUP BY port,l7_protocol ORDER BY totalCount DESC limit 5&format=json&option=real-time\\\",\\n    \\\"message\\\": \\\"success\\\",\\n    \\\"status\\\": 200\\n}\",\n  \"description\" : null,\n  \"requestBodyDefinition\" : null,\n  \"responseBodyDefinition\" : {\n    \"name\" : \"\",\n    \"value\" : \"\",\n    \"description\" : \"\",\n    \"required\" : false,\n    \"dataType\" : \"Object\",\n    \"type\" : null,\n    \"defaultValue\" : null,\n    \"validateType\" : \"\",\n    \"error\" : \"\",\n    \"expression\" : \"\",\n    \"children\" : [ {\n      \"name\" : \"code\",\n      \"value\" : \"200\",\n      \"description\" : \"\",\n      \"required\" : false,\n      \"dataType\" : \"Integer\",\n      \"type\" : null,\n      \"defaultValue\" : null,\n      \"validateType\" : \"\",\n      \"error\" : \"\",\n      \"expression\" : \"\",\n      \"children\" : [ ]\n    }, {\n      \"name\" : \"data\",\n      \"value\" : \"\",\n      \"description\" : \"\",\n      \"required\" : false,\n      \"dataType\" : \"Object\",\n      \"type\" : null,\n      \"defaultValue\" : null,\n      \"validateType\" : \"\",\n      \"error\" : \"\",\n      \"expression\" : \"\",\n      \"children\" : [ {\n        \"name\" : \"list\",\n        \"value\" : \"\",\n        \"description\" : \"\",\n        \"required\" : false,\n        \"dataType\" : \"Array\",\n        \"type\" : null,\n        \"defaultValue\" : null,\n        \"validateType\" : \"\",\n        \"error\" : \"\",\n        \"expression\" : \"\",\n        \"children\" : [ {\n          \"name\" : \"\",\n          \"value\" : \"\",\n          \"description\" : \"\",\n          \"required\" : false,\n          \"dataType\" : \"Object\",\n          \"type\" : null,\n          \"defaultValue\" : null,\n          \"validateType\" : \"\",\n          \"error\" : \"\",\n          \"expression\" : \"\",\n          \"children\" : [ {\n            \"name\" : \"port\",\n            \"value\" : \"443\",\n            \"description\" : \"\",\n            \"required\" : false,\n            \"dataType\" : \"String\",\n            \"type\" : null,\n            \"defaultValue\" : null,\n            \"validateType\" : \"\",\n            \"error\" : \"\",\n            \"expression\" : \"\",\n            \"children\" : [ ]\n          }, {\n            \"name\" : \"l7Protocol\",\n            \"value\" : \"\",\n            \"description\" : \"\",\n            \"required\" : false,\n            \"dataType\" : \"String\",\n            \"type\" : null,\n            \"defaultValue\" : null,\n            \"validateType\" : \"\",\n            \"error\" : \"\",\n            \"expression\" : \"\",\n            \"children\" : [ ]\n          }, {\n            \"name\" : \"uniqueEntities\",\n            \"value\" : \"998\",\n            \"description\" : \"\",\n            \"required\" : false,\n            \"dataType\" : \"String\",\n            \"type\" : null,\n            \"defaultValue\" : null,\n            \"validateType\" : \"\",\n            \"error\" : \"\",\n            \"expression\" : \"\",\n            \"children\" : [ ]\n          } ]\n        } ]\n      } ]\n    }, {\n      \"name\" : \"time\",\n      \"value\" : \"2023-08-31 02:54:46\",\n      \"description\" : \"\",\n      \"required\" : false,\n      \"dataType\" : \"String\",\n      \"type\" : null,\n      \"defaultValue\" : null,\n      \"validateType\" : \"\",\n      \"error\" : \"\",\n      \"expression\" : \"\",\n      \"children\" : [ ]\n    }, {\n      \"name\" : \"originalUrl\",\n      \"value\" : \"http://192.168.44.55:9999/?query=SELECT port AS port, l7_protocol AS l7_protocol, COUNTIF(DISTINCT(app_name),notEmpty(app_name)) as appCount, COUNTIF(DISTINCT(domain),notEmpty(domain)) as domainCount, COUNTIF(DISTINCT(ip),notEmpty(ip)) as ipCount, plus(ipCount,plus(appCount,domainCount)) AS totalCount FROM( SELECT e.app_name AS app_name, e.domain AS domain, e.ip AS ip, e.update_time AS update_time, e.ip_admin_area AS ip_admin_area, dr.port AS port, dr.l7_protocol AS l7_protocol FROM cn_entity_relation e JOIN cn_ip_dynamic_attribute dr ON (e.ip = dr.ip) WHERE e.update_time >= 1677898486 AND e.update_time < 1693450486) GROUP BY port,l7_protocol ORDER BY totalCount DESC limit 5&format=json&option=real-time\",\n      \"description\" : \"\",\n      \"required\" : false,\n      \"dataType\" : \"String\",\n      \"type\" : null,\n      \"defaultValue\" : null,\n      \"validateType\" : \"\",\n      \"error\" : \"\",\n      \"expression\" : \"\",\n      \"children\" : [ ]\n    }, {\n      \"name\" : \"message\",\n      \"value\" : \"success\",\n      \"description\" : \"\",\n      \"required\" : false,\n      \"dataType\" : \"String\",\n      \"type\" : null,\n      \"defaultValue\" : null,\n      \"validateType\" : \"\",\n      \"error\" : \"\",\n      \"expression\" : \"\",\n      \"children\" : [ ]\n    }, {\n      \"name\" : \"status\",\n      \"value\" : \"200\",\n      \"description\" : \"\",\n      \"required\" : false,\n      \"dataType\" : \"Integer\",\n      \"type\" : null,\n      \"defaultValue\" : null,\n      \"validateType\" : \"\",\n      \"error\" : \"\",\n      \"expression\" : \"\",\n      \"children\" : [ ]\n    } ]\n  }\n}\r\n================================\r\nimport cn.hutool.log.Log;\r\nimport cn.hutool.core.util.ObjectUtil;\r\nimport net.geedge.common.utils.FreeMarkerUtil as freeMarkerUtil;\r\nimport net.geedge.common.utils.JSONUtil as jsonUtil;\r\nimport net.geedge.common.utils.R as R;\r\nimport \'@/public/toHump\' as toHump;\r\nimport net.geedge.modules.entity.service.EntityExplorerService as EntityExplorerService;\r\nimport \'@/pre/timeHandleHalf\' as timeHandle;\r\nimport \'@/public/getGalaxyResult\' as getGalaxyResult;\r\n/**\r\n * 入参整理 包括调用公共函数\r\n */\r\nvar parameter={\r\n    \"resource\":resource,\r\n    \"startTime\":startTime,\r\n    \"endTime\":endTime,\r\n    \"dr\":false,\r\n    \"fqdnCategory\":false,\r\n    \"whois\":false,\r\n    \"appCategory\":false\r\n}\r\ntimeHandle(parameter);\r\nparameter.resource = EntityExplorerService.parseExpression(parameter.resource);\r\n/**\r\n * 如果resource表达式中涉及联表字段，将进行联表查询。例：Whois.Domain\r\n */\r\nparameter.filter = parameter.resource;\r\nvar param = EntityExplorerService.getAssociationQueryParam(parameter.resource);\r\nvar columns = [\"dr\",\"fqdnCategory\",\"whois\",\"appCategory\"];\r\nif(ObjectUtil.isNotEmpty(param)){\r\n    for (item in columns) {\r\n        for (k,v in param) {\r\n            parameter[k] = v;\r\n            if (v.asString().contains(item)){\r\n                parameter[item] = true;\r\n                break;\r\n            }\r\n        }\r\n    }\r\n}\r\nvar isAssociationQuery = EntityExplorerService.isAssociationQuery(parameter.resource);\r\nif(isAssociationQuery){\r\n    var sql = \"{\\\"sql\\\":\\\"SELECT port AS port, l7_protocol AS l7_protocol, COUNT(DISTINCT(app_name)) FILTER (WHERE app_name != \'\') AS appCount, COUNT(DISTINCT(domain)) FILTER (WHERE domain != \'\') AS domainCount, COUNT(DISTINCT(ip)) FILTER (WHERE ip != \'\') AS ipCount, COUNT(DISTINCT(app_name)) FILTER (WHERE app_name != \'\') + COUNT(DISTINCT(domain)) FILTER (WHERE domain != \'\') + COUNT(DISTINCT(ip)) FILTER (WHERE ip != \'\') AS totalCount FROM( SELECT e.app_name AS app_name, e.domain AS domain, e.ip AS ip, e.update_time AS update_time, dr.port AS port, dr.l7_protocol AS l7_protocol FROM clickhouse.cyber_narrator_galaxy.cn_entity_relation e JOIN clickhouse.cyber_narrator_galaxy.cn_ip_dynamic_attribute dr ON (e.ip = dr.ip AND dr.update_time >= ${startTime} AND dr.update_time < ${endTime} <#if dr\\.port?default(\'\')?trim? length gt 0> AND ${dr\\.port}</#if>) <#if fqdnCategory> JOIN mysql.cn_api.cn_fqdn_category_built_in fqdnCategory ON (e.domain = fqdnCategory.fqdn <#if fqdnCategory\\.category_name?default(\'\')?trim? length gt 0> AND ${fqdnCategory\\.category_name} </#if> <#if fqdnCategory\\.category_group?default(\'\')?trim? length gt 0> AND ${fqdnCategory\\.category_group} </#if> <#if fqdnCategory\\.reputation_level?default(\'\')?trim? length gt 0> AND ${fqdnCategory\\.reputation_level}</#if>) </#if> <#if whois> JOIN mysql.cn_api.cn_fqdn_who_is_built_in whois ON (e.domain = whois.fqdn <#if whois\\.whois_domain?default(\'\')?trim? length gt 0> AND ${whois\\.whois_domain} </#if> <#if whois\\.whois_registrant_name?default(\'\')?trim? length gt 0> AND ${whois\\.whois_registrant_name}</#if>) </#if> <#if appCategory> JOIN mysql.cn_api.cn_app_category_built_in appCategory ON (e.app_name = appCategory.app_name <#if appCategory\\.app_category?default(\'\')?trim? length gt 0> AND ${appCategory\\.app_category} </#if> <#if appCategory\\.app_subcategory?default(\'\')?trim? length gt 0> AND ${appCategory\\.app_subcategory} </#if> <#if appCategory\\.app_risk?default(\'\')?trim? length gt 0> AND ${appCategory\\.app_risk}</#if>) </#if> WHERE e.update_time >= ${startTime} AND e.update_time < ${endTime} <#if filter?default(\'\')?trim? length gt 0> ${filter} </#if> ) GROUP BY port,l7_protocol ORDER BY totalCount DESC\\\"}\"\r\n    var aTemplate  = freeMarkerUtil.processTemplateIntoString(\"v1EntityExplorerTopAggPort\",sql,parameter);\r\n    var ares = jsonUtil.toBean(aTemplate,HashMap.class);\r\n    var log = Log.get();\r\n    log.info(ares.sql);\r\n    var aresult = db[\'trino\'].select(ares.sql);\r\n    var list=new ArrayList();\r\n    if(ObjectUtil.isNotEmpty(aresult)){\r\n        if(aresult.length > 0){\r\n            for(key,value in aresult){\r\n                var obj = {};\r\n                obj.port = value.port;\r\n                obj.l7Protocol = value.l7_protocol;\r\n                obj.uniqueEntities = value.totalCount;\r\n                list.add(obj);\r\n            }\r\n        }\r\n    }\r\n    var data = {};\r\n    data.list = list;\r\n    var url = db.select(\"select cvalue from sys_config where ckey = \'galaxy_url\'\");\r\n    return R.ok(data).put(\"originalUrl\",url.cvalue+ares.sql).put(\"status\",200);\r\n}\r\n/**\r\n * 接口sql相关内容整理 通过freemarker模板生成最后执行语句\r\n */\r\nvar q = \"{\\r\\n    \\\"query\\\": \\\"SELECT port AS port, l7_protocol AS l7_protocol, COUNTIF(DISTINCT(app_name),notEmpty(app_name)) as appCount, COUNTIF(DISTINCT(domain),notEmpty(domain)) as domainCount, COUNTIF(DISTINCT(ip),notEmpty(ip)) as ipCount, plus(ipCount,plus(appCount,domainCount)) AS totalCount FROM( SELECT e.app_name AS app_name, e.domain AS domain, e.ip AS ip, e.update_time AS update_time, e.ip_admin_area AS ip_admin_area, dr.port AS port, dr.l7_protocol AS l7_protocol FROM cn_entity_relation e JOIN cn_ip_dynamic_attribute dr ON (e.ip = dr.ip) WHERE e.update_time >= ${startTime} AND e.update_time < ${endTime} AND dr.update_time >= ${startTime} AND dr.update_time < ${endTime} <#if filter?default(\\\'\\\')?trim? length gt 0> AND (${filter}) </#if>) GROUP BY port,l7_protocol ORDER BY totalCount DESC limit 100\\\",\\r\\n    \\\"option\\\": \\\"real-time\\\",\\r\\n    \\\"format\\\": \\\"json\\\"\\r\\n}\"\r\n\r\nvar result  = freeMarkerUtil.processTemplateIntoString(\"v1EntityExplorerTopAggTag\",q,parameter);\r\n\r\n/**\r\n * 调用galaxy接口获取结果信息\r\n */\r\nvar res = jsonUtil.toBean(result,HashMap.class);\r\nvar result = getGalaxyResult(res);\r\n/**\r\n * 处理galaxy返回的数据结果 将新结果返回前端界面\r\n */\r\nvar data = {};\r\nvar list = new ArrayList();\r\nif(result.success){\r\n    for(key,value in result.data){\r\n        var obj = {};\r\n        obj.port = value.port;\r\n        obj.l7Protocol = value.l7_protocol;\r\n        obj.uniqueEntities = value.totalCount;\r\n        list.add(obj);\r\n    }\r\n}\r\ndata.list = list;\r\nreturn R.ok(data).put(\"originalUrl\",result.originalUrl).put(\"status\",200);');
 INSERT INTO `magic_api_file` VALUES ('/api/v1/entity/graph/', 'this is directory');
 INSERT INTO `magic_api_file` VALUES ('/api/v1/entity/graph/group.json', '{\n  \"properties\" : { },\n  \"id\" : \"1fc43fa8284b4b83b4db6869949ba6fc\",\n  \"name\" : \"graph\",\n  \"type\" : \"api\",\n  \"parentId\" : \"d12747533faa48698e64aec0aae936bd\",\n  \"path\" : \"/graph\",\n  \"createTime\" : 1687943421947,\n  \"updateTime\" : null,\n  \"createBy\" : \"ljy\",\n  \"updateBy\" : null,\n  \"paths\" : [ ],\n  \"options\" : [ ]\n}');
 INSERT INTO `magic_api_file` VALUES ('/api/v1/entity/graph/relation/', 'this is directory');