Inconsistent data

Elastic Stack Kibana
,
1

Hello,
I'm getting different count of records when switching between lens and discover, the only explanation I could think of is due to duplicates but after extracting a CSV file, i did not find any duplicates.

Lens count (unique): 25139

image
image1252×526 35.8 KB

Discover count : 25078

image
image878×309 18.1 KB

This is really confusing because the unique count (in lens) should be less if there are any duplicates, yet today the unique count has more records, couple of days ago for the same period (00 to 10 AM) it was the opposite.

2

Hi @SamehSaeed, In Discover have you applied any filter ? Not sure about call_history in the image? Make sure you're applying same filter and same query both of the places.

3

Hi @ashishtiwari1993 , I have applied the same filter in both, actually I can see both results in lens if I use "unique count" and normal count (same as in discover) , still not sure why unique count gets more records :

Unique count : 25,531

image
image1276×582 43.1 KB

Normal count : 25,382

image
image1209×533 37.5 KB

Discover (since you cant apply unique filter) : 25,382

image
image1636×434 40.2 KB

4

I found some duplicate values at the time of creating the new index "Asia/Riyadh" is UTC+3 so around 2:45 AM.
I don't know how I can remove these duplicates however, selecting unique count should get unique records even if I have duplicates in the index correct?

image
image1096×361 8.3 KB

5

Added visualisation

6

Added lens

7

Is there any record with multiple values for the field seqId?

8

No, each record has only one seqid value .

logstash configuration :

input {
    jdbc {
	 #
        jdbc_driver_library => "****\ojdbc11.jar"
        jdbc_driver_class => "Java::oracle.jdbc.driver.OracleDriver"
        jdbc_connection_string => "****"
        jdbc_user => "****"
		jdbc_password => "****"
		jdbc_validate_connection => true
		jdbc_default_timezone => "Asia/Riyadh"
		jdbc_validation_timeout => 120
        schedule => "*/10 * * * *"
		connection_retry_attempts => 3
		last_run_metadata_path => "****/config/metadata/callhistory-lastrun" 
		tracking_column => "seqid"
		tracking_column_type => "numeric"
		use_column_value => "true"
        statement => "
SELECT /*+ PARALLEL8 */
    CALLER_ID,
    SERVED_NUMBER,
    ISD_SKILL_GROUP,
    DNIS,
    MASTER_DNIS,
    AGENT_NODE,
    AGENT_ID,
    ANI_CUST_TYPE,
    ANI_CUST_SUB_TYPE,
    CALL_LANG,
    CUSTOMER_PRIORITY,
    CUSTOMER_TYPE,
    END_REASON_CODE,
    SERVICE_TYPE,
    SOURCE_IP,
    VXML_GATEWAY_IP,
    SESSION_ID,
    CALL_START_TIME,
	CALL_END_TIME,
	IDB_DATE_TIME,
	CISCO_IP,
    SEQID,
    CALL_HISTORY,
    CHANNEL, 
    PHONE_TYPE
FROM
    (
        SELECT
            CALLER_ID,
            SERVED_NMBR AS SERVED_NUMBER,
            ISD_SKILL_GROUP,
            DNIS,
            MASTER_DNIS,
            AGENT_NODE,
            AGENT_ID,
            ANI_CUST_TYPE,
            ANI_CUST_SUB_TYPE,
            CALL_LANG,
            CUSTOMER_PRIORITY,
            CUSTOMER_TYPE,
            END_REASON_CODE,
            SERVICE_TYPE,
            SOURCE_IP,
            VXML_GATEWAY_IP,
            SESSION_ID,
            CALL_START_DATE_TIME AS CALL_START_TIME,
			CALL_END_DATE_TIME AS CALL_END_TIME,
			IDB_DATE_TIME,
			CISCO_IP,
            SEQID,
            CALL_HISTORY,
            CHANNEL,
            CASE
                WHEN (INSTR(CALL_HISTORY, 'Check GSM Customer Service/prepaid', -1) > 0 
                      OR INSTR(CALL_HISTORY, 'Check GSM Customer Type/prepaid', -1) > 0 
                      OR INSTR(CALL_HISTORY, 'Check Disconnect Customer Type/prepaid', -1) > 0)
                THEN 'PREPAID'
                WHEN INSTR(CALL_HISTORY, 'Check GSM Customer Type For IE Customers/prepaid', -1) > 0
                THEN 'IE PREPAID'
                WHEN (INSTR(CALL_HISTORY, 'Check GSM Customer Service/postpaid', -1) > 0 
                      OR INSTR(CALL_HISTORY, 'Check GSM Customer Type/postpaid', -1) > 0 
                      OR INSTR(CALL_HISTORY, 'Check Disconnect Customer Type/postpaid', -1) > 0)
                THEN 'POSTPAID'
                WHEN INSTR(CALL_HISTORY, 'Check GSM Customer Type For IE Customers/postpaid', -1) > 0
                THEN 'IE POSTPAID'
                WHEN (INSTR(CALL_HISTORY, 'Check Phone Type/landline', -1) > 0 
                      OR INSTR(CALL_HISTORY, 'Check Disconnect Customer Type/landline', -1) > 0)
                THEN 'LANDLINE'
                WHEN INSTR(CALL_HISTORY, 'Check Phone Type For IE Customers/landline', -1) > 0
                THEN 'IE LANDLINE'
            END AS PHONE_TYPE
        FROM
            CALL_HISTORY_DETAILS_ETL_TEST
        WHERE
            SEQID > :sql_last_value
    )

		  "

		tags => ["callhistorytest"]
    }
}
filter {
}
output{

 
    elasticsearch {	
        hosts => ["https://localhost:9200/"]
		index => "bkgroupindex_callhistory-%{+YYYY.MM}"
		document_id => "%{seqid}"
		user => "****"
		password => "****"
		ssl => true
		ssl_certificate_verification => false
    }
   
}