Sql query giving null ouput

Elastic Stack Elasticsearch
1

Hi All,

I am stuck with sql query output, can anyone please help

POST _sql?format=txt
{
 "query": "SELECT SERVICE_NAME,CASE WHEN MSA_STATUS.keyword ='PUBLISH' AND MSA_FLOW_DIR.keyword ='EAI - SCRM specific processing'    THEN count(*) END AS PUBLISHTRB,CASE WHEN MSA_STATUS.keyword ='REQUEST' AND MSA_FLOW_DIR.keyword ='TRB_TO_EAI' THEN count(*) END AS TOTALTRB,CASE WHEN MSA_STATUS.keyword ='REQUEST' AND MSA_FLOW_DIR.keyword ='TRB_TO_EAI' THEN count(*)ELSE 0 END  - CASE WHEN MSA_STATUS.keyword ='PUBLISH' AND MSA_FLOW_DIR.keyword ='EAI - SCRM specific processing'    THEN count(*) ELSE 0 END AS SUPPRESSTRB FROM \"iib-eai-remo:temp*\" WHERE APPNAME LIKE '%TRB%' AND SERVICE_NAME = 'CANSUB' AND \"@timestamp\" >= NOW() -  INTERVAL 1 HOURS AND (CASE WHEN MSA_STATUS.keyword ='PUBLISH' AND MSA_FLOW_DIR.keyword ='EAI - SCRM specific processing'    THEN 1 END IS NOT NULL OR CASE WHEN MSA_STATUS.keyword ='REQUEST' AND MSA_FLOW_DIR.keyword ='TRB_TO_EAI' THEN 1 END IS NOT NULL ) GROUp BY SERVICE_NAME,MSA_STATUS.keyword,MSA_FLOW_DIR.keyword  "
}

I am getting below response :

 SERVICE_NAME  |  PUBLISHTRB   |   TOTALTRB    |  SUPPRESSTRB  
---------------+---------------+---------------+---------------
CANSUB         |620            |null           |-620           
CANSUB         |null           |1039           |1039

I was expecting below output

 SERVICE_NAME  |  PUBLISHTRB   |   TOTALTRB    |  SUPPRESSTRB  
---------------+---------------+---------------+---------------
CANSUB         |620            |1039                  | 419
2

@Christian_Dahlqvist can you help please

3

This forum is manned by volunteers so please do not ping people not already involved in the thread. You also need to be patient. If you have not received any response after a few business days it is generally fine to ping for attention.

2 Likes
4

try this

POST _sql?format=txt
{
  "query": """
            SELECT SERVICE_NAME,
            ISNULL(SUM (CASE WHEN MSA_STATUS.keyword ='PUBLISH' AND MSA_FLOW_DIR.keyword ='EAI - SCRM specific processing' THEN 1 ELSE 0 END), 0) AS PUBLISHTRB,
            ISNULL(SUM( CASE WHEN MSA_STATUS.keyword ='REQUEST' AND MSA_FLOW_DIR.keyword ='TRB_TO_EAI' THEN 1 ELSE 0 END), 0) AS TOTALTRB,
            (ISNULL(SUM (CASE WHEN MSA_STATUS.keyword ='PUBLISH' AND MSA_FLOW_DIR.keyword ='EAI - SCRM specific processing' THEN 1 ELSE 0 END), 0) - ISNULL(SUM( CASE WHEN MSA_STATUS.keyword ='REQUEST' AND MSA_FLOW_DIR.keyword ='TRB_TO_EAI' THEN 1 ELSE 0 END), 0)) AS SUPPRESSTRB
            FROM \"iib-eai-remo:temp*\"
            WHERE APPNAME LIKE '%TRB%' AND SERVICE_NAME = 'CANSUB' AND "@timestamp" >= NOW() -  INTERVAL 1 HOURS 
            GROUP BY SERVICE_NAME
            """
}
1 Like
5

@ylasri Thanks but this is working in v 7.10 but not in v7.7 elasticsearch

6

Then I would recommend upgrading to Elasticsearch 7.10. :slight_smile:

1 Like
7

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.