I am trying to analyze why my below elastic query fails intermittently.
It fails like 1 out of 3 times. The query seems correct as it works other times. Below is the python script along with the logs.
we are running the below script every minute and using elastic version
elasticsearch==2.4.1
elasticsearch-curator==4.3.1
Hence need your help to identify the problem. Thank you.
from elasticsearch import Elasticsearch
from elasticsearch import helpers
from elasticsearch.helpers import scan
from elasticsearch.exceptions import NotFoundError, TransportError, ConflictError
from argparse import ArgumentParser
from collections import defaultdict
import json
import logging
import sys
import datetime
IA_TYPE = 'initiating-action'
YES = 'Yes'
LOGGER = logging.getLogger('activityUsername')
def processDocuments(es, startDate, endDate):
iaSearchBody = {
"query" :{
"bool": {
"must": [{
"bool" : {
"must_not" : [ {
"exists" : {
"field": "InitiatingAction.credential-identity.activity-username"
}
}]
},
"must" : {
"query" : {
"bool" : {
"must" : [ {
"range" : {
"InitiatingAction.start" : {
"to" : endDate
}
}
},
{
"range" : {
"InitiatingAction.end" : {
"from" : startDate
}
}
}]
}
}
}
},
{
"has_child": {
"query" : {
"bool" : {
"must" : {
}
}
},
"child_type" : "activity",
"inner_hits" : {
"size" : 2,
"sort" : [ {
"Activity.start" : {
"order" : "asc"
}
} ]
}
}
}]
}
},
"sort" :[{
"InitiatingAction.start" :{
"order": "desc"
}
}]
}
try:
iaSearches = es.search(index="_all", size=10000, body=iaSearchBody)
except TransportError as e:
LOGGER.error(e.error)
LOGGER.error(e.info)
LOGGER.error(e.status_code)
exit()
except Exception as e:
LOGGER.error(e)
exit()
updateBody = []
count = 0
iaBillable = None
if int(iaSearches['hits']['total']) > 0:
for index in iaSearches['hits']['hits']:
iaBillable = None
try:
activityUsername = index['inner_hits']['activity']['hits']['hits'][0]['_source']['Activity']['credential-identity']['value']
if ('is-billable' in index['inner_hits']['activity']['hits']['hits'][0]['_source']['Activity']['credential-identity']):
iaBillable = YES
except (KeyError, IndexError):
try:
activityUsername = index['inner_hits']['activity']['hits']['hits'][1]['_source']['Activity']['credential-identity']['value']
if ('is-billable' in index['inner_hits']['activity']['hits']['hits'][1]['_source']['Activity']['credential-identity']):
iaBillable = YES
except (KeyError, IndexError):
try:
activityUsername = index['_source']['InitiatingAction']['credential-identity']['username']
iaBillable = index['_source']['InitiatingAction']['credential-identity']['is-billable']
except (KeyError, IndexError):
continue
updateBody.append({"update": {"_index": index['_index'], '_id': index['_id'],'_type': IA_TYPE}})
updateBody.append({"doc": {"InitiatingAction":{"credential-identity":{"activity-username" : activityUsername}}}})
if (iaBillable != None):
updateBody.append({"update": {"_index": index['_index'], '_id': index['_id'],'_type': IA_TYPE}})
updateBody.append({"doc": {"InitiatingAction":{"credential-identity":{"is-billable" : iaBillable}}}})
count += 1
if (count == 100):
updateDocuments(es, updateBody)
updateBody = []
count = 0
if len(updateBody) > 0:
updateDocuments(es, updateBody)
def updateDocuments(es, updateBody):
try:
es.bulk(body=updateBody, refresh="true")
except TransportError as e:
LOGGER.error(e.error)
LOGGER.error(e.info)
LOGGER.error(e.status_code)
exit()
except Exception as e:
LOGGER.error(e)
exit()
################################################################################
datenow = datetime.datetime.utcnow()
defaultdate = datenow.strftime("%Y-%m-%d")
defaulttime = datenow.strftime("%H:%M:%S")
defaultendDate = defaultdate + 'T' + defaulttime + '.000Z'
datepast = datetime.datetime.utcnow() - datetime.timedelta(minutes=30)
defaultpastdate = datepast.strftime("%Y-%m-%d")
defaultpasttime = datepast.strftime("%H:%M:%S")
defaultstartDate = defaultpastdate + 'T' + defaultpasttime + '.000Z'
parser = ArgumentParser()
parser.add_argument('-hn', '--hostname', dest='host', nargs='?', default='localhost',
help='Hostname or IP for the ES connection (default: localhost).', metavar='HOSTNAME/IP')
parser.add_argument('-p', '--port', dest='port', nargs='?', type=int, default=9200,
help='Port for the ES connection (default: 9200).', metavar='PORT')
parser.add_argument('-t', '--timeout', dest='timeout', nargs='?', type=int, default=60,
help='Timeout (seconds) for API calls to ES (default 60).', metavar='SECONDS')
parser.add_argument('-mr', '--max-retries', dest='maxRetries', nargs='?', type=int, default=10,
help='Max number of retries after a timeout (default 10). Requires --retry-on-timeout to be True.', metavar='RETRIES')
parser.add_argument('-rot', '--retry-on-timeout', dest='retryOnTimeout', nargs='?', type=bool, default=True,
help='Allow retries to take place after a timeout (default True).', metavar='RETRIES')
parser.add_argument('-sd', '--start-date', dest='startDate', nargs='?', type=str, default=defaultstartDate,
help='Start date to add activity-username to IAs (format is YYYY-MM-DDTHH:MM:SS.MMMZ).', metavar='STARTDATE')
parser.add_argument('-ed', '--end-date', dest='endDate', nargs='?', type=str, default= defaultendDate,
help='End date to add activity-username to IAs (format is YYYY-MM-DDTHH:MM:SS.MMMZ).', metavar='ENDDATE')
arguments = parser.parse_args()
startDate = arguments.startDate
endDate = arguments.endDate
rawHosts = arguments.host
defaultPort = arguments.port
hosts = []
for rawHost in rawHosts.split(","):
components = rawHost.split(":")
port = components[1] if len(components) > 1 else defaultPort
hosts.append({'host': components[0], 'port': port})
logging.basicConfig(format='addActivityUsernameInIA: %(name)s - %(levelname)s - %(message)s')
LOGGER.setLevel(logging.INFO)
LOGGER.info('Executing with the following arguments: \n --hostname = ' + arguments.host + ', \n --port = ' + str(arguments.port)
+ ', \n --timeout = ' + str(arguments.timeout) + ', \n --max-retries = ' + str(arguments.maxRetries) + ', \n --retry-on-timeout = ' + str(arguments.retryOnTimeout)
+ ', \n --start-date = ' + arguments.startDate + ', \n --end-date = ' + arguments.endDate)
# Connect to ES.
es = Elasticsearch(
hosts,
timeout=arguments.timeout,
max_retries=arguments.maxRetries,
retry_on_timeout=arguments.retryOnTimeout
)
if not es.ping():
raise ValueError('Connection to ElasticSearch failed.')
LOGGER.info('calling with the processDocuments build 104')
processDocuments(es, startDate, endDate)
##################################################################
addActivityUsernameInIA: activityUsername - INFO - Executing with the following arguments:
--hostname = svw29-desktop.ciam.cloud.cas.org:9200,
--port = 9200,
--timeout = 60,
--max-retries = 10,
--retry-on-timeout = True,
--start-date = 2022-08-17T10:37:01.000Z,
--end-date = 2022-08-17T11:07:01.000Z
addActivityUsernameInIA: activityUsername - INFO - calling with the processDocuments build 104
addActivityUsernameInIA: activityUsername - INFO - Executing with the following arguments:
--hostname = svw29-desktop.ciam.cloud.cas.org:9200,
--port = 9200,
--timeout = 60,
--max-retries = 10,
--retry-on-timeout = True,
--start-date = 2022-08-17T10:38:01.000Z,
--end-date = 2022-08-17T11:08:01.000Z
addActivityUsernameInIA: activityUsername - INFO - calling with the processDocuments build 104
addActivityUsernameInIA: elasticsearch - WARNING - GET /_all/_search?size=10000 [status:400 request:0.003s]
addActivityUsernameInIA: activityUsername - ERROR - search_phase_execution_exception
addActivityUsernameInIA: activityUsername - ERROR - {'error': {'reason': 'all shards failed', 'phase': 'query', 'grouped': True, 'type': 'search_phase_execution_exception', 'root_cause': [{'type': 'query_parsing_exception', 'col': 96, 'reason': 'No query registered for [must]', 'line': 1, 'index': 'cst-idx-2020-01'}], 'failed_shards': [{'shard': 0, 'index': 'cst-idx-2020-01', 'node': 'Zq15DDiNQzGt-n7Ft2JgNg', 'reason': {'type': 'query_parsing_exception', 'col': 96, 'reason': 'No query registered for [must]', 'line': 1, 'index': 'cst-idx-2020-01'}}]}, 'status': 400}
addActivityUsernameInIA: activityUsername - ERROR - 400
Aug 17 07:08:01 148543935b58 /USR/SBIN/CRON[6639]: (root) CMD (python3 /usr/local/bin/addActivityUsernameInIA.py --hostname $ELASTICSEARCH_SINK_HOSTNAMES >/proc/1/fd/1 2>/proc/1/fd/2)
addActivityUsernameInIA: activityUsername - INFO - Executing with the following arguments:
--hostname = svw29-desktop.ciam.cloud.cas.org:9200,
--port = 9200,
--timeout = 60,
--max-retries = 10,
--retry-on-timeout = True,
--start-date = 2022-08-17T10:39:02.000Z,
--end-date = 2022-08-17T11:09:02.000Z
addActivityUsernameInIA: activityUsername - INFO - calling with the processDocuments build 104
addActivityUsernameInIA: elasticsearch - WARNING - GET /_all/_search?size=10000 [status:400 request:0.003s]
addActivityUsernameInIA: activityUsername - ERROR - search_phase_execution_exception
addActivityUsernameInIA: activityUsername - ERROR - {'status': 400, 'error': {'root_cause': [{'type': 'query_parsing_exception', 'col': 39, 'index': 'cst-idx-2020-01', 'reason': 'No query registered for [must]', 'line': 1}], 'failed_shards': [{'shard': 0, 'node': 'Zq15DDiNQzGt-n7Ft2JgNg', 'reason': {'type': 'query_parsing_exception', 'col': 39, 'index': 'cst-idx-2020-01', 'reason': 'No query registered for [must]', 'line': 1}, 'index': 'cst-idx-2020-01'}], 'reason': 'all shards failed', 'type': 'search_phase_execution_exception', 'grouped': True, 'phase': 'query'}}
addActivityUsernameInIA: activityUsername - ERROR - 400
Aug 17 07:09:01 148543935b58 /USR/SBIN/CRON[6643]: (root) CMD (python3 /usr/local/bin/addActivityUsernameInIA.py --hostname $ELASTICSEARCH_SINK_HOSTNAMES >/proc/1/fd/1 2>/proc/1/fd/2)
documentRelocator: documentRelocator - INFO - Executing with the following arguments:
--index = cst-idx-orphaned-activities,
--hostname = svw29-desktop.ciam.cloud.cas.org:9200,
--port = 9200,
--timeout = 60,
--max-retries = 10,
--retry-on-timeout = True
addActivityUsernameInIA: activityUsername - INFO - Executing with the following arguments:
--hostname = svw29-desktop.ciam.cloud.cas.org:9200,
--port = 9200,
--timeout = 60,
--max-retries = 10,
--retry-on-timeout = True,
--start-date = 2022-08-17T10:40:01.000Z,
--end-date = 2022-08-17T11:10:01.000Z
addActivityUsernameInIA: activityUsername - INFO - calling with the processDocuments build 104
addActivityUsernameInIA: elasticsearch - WARNING - GET /_all/_search?size=10000 [status:400 request:0.003s]
addActivityUsernameInIA: activityUsername - ERROR - search_phase_execution_exception
addActivityUsernameInIA: activityUsername - ERROR - {'error': {'root_cause': [{'line': 1, 'reason': 'No query registered for [must]', 'type': 'query_parsing_exception', 'index': 'cst-idx-2020-01', 'col': 39}], 'grouped': True, 'reason': 'all shards failed', 'type': 'search_phase_execution_exception', 'failed_shards': [{'shard': 0, 'reason': {'line': 1, 'reason': 'No query registered for [must]', 'type': 'query_parsing_exception', 'index': 'cst-idx-2020-01', 'col': 39}, 'index': 'cst-idx-2020-01', 'node': 'Zq15DDiNQzGt-n7Ft2JgNg'}], 'phase': 'query'}, 'status': 400}
addActivityUsernameInIA: activityUsername - ERROR - 400
Aug 17 07:10:01 148543935b58 /USR/SBIN/CRON[6648]: (root) CMD (python3 /usr/local/bin/addActivityUsernameInIA.py --hostname $ELASTICSEARCH_SINK_HOSTNAMES >/proc/1/fd/1 2>/proc/1/fd/2)
Aug 17 07:10:01 148543935b58 /USR/SBIN/CRON[6649]: (root) CMD (python3 /usr/local/bin/documentRelocator.py --hostname $ELASTICSEARCH_SINK_HOSTNAMES --index cst-idx-orphaned-activities >/proc/1/fd/1 2>/proc/1/fd/2)
addActivityUsernameInIA: activityUsername - INFO - Executing with the following arguments:
--hostname = svw29-desktop.ciam.cloud.cas.org:9200,
--port = 9200,
--timeout = 60,
--max-retries = 10,
--retry-on-timeout = True,
--start-date = 2022-08-17T10:41:01.000Z,
--end-date = 2022-08-17T11:11:01.000Z
addActivityUsernameInIA: activityUsername - INFO - calling with the processDocuments build 104
Aug 17 07:11:01 148543935b58 /USR/SBIN/CRON[6655]: (root) CMD (python3 /usr/local/bin/addActivityUsernameInIA.py --hostname $ELASTICSEARCH_SINK_HOSTNAMES >/proc/1/fd/1 2>/proc/1/fd/2)
addActivityUsernameInIA: activityUsername - INFO - Executing with the following arguments:
--hostname = svw29-desktop.ciam.cloud.cas.org:9200,
--port = 9200,
--timeout = 60,
--max-retries = 10,
--retry-on-timeout = True,
--start-date = 2022-08-17T10:42:01.000Z,
--end-date = 2022-08-17T11:12:01.000Z
addActivityUsernameInIA: activityUsername - INFO - calling with the processDocuments build 104
addActivityUsernameInIA: elasticsearch - WARNING - GET /_all/_search?size=10000 [status:400 request:0.002s]
addActivityUsernameInIA: activityUsername - ERROR - search_phase_execution_exception
addActivityUsernameInIA: activityUsername - ERROR - {'error': {'reason': 'all shards failed', 'failed_shards': [{'reason': {'reason': 'No query registered for [must]', 'index': 'cst-idx-2020-01', 'col': 96, 'type': 'query_parsing_exception', 'line': 1}, 'shard': 0, 'index': 'cst-idx-2020-01', 'node': 'Zq15DDiNQzGt-n7Ft2JgNg'}], 'root_cause': [{'reason': 'No query registered for [must]', 'index': 'cst-idx-2020-01', 'col': 96, 'type': 'query_parsing_exception', 'line': 1}], 'phase': 'query', 'type': 'search_phase_execution_exception', 'grouped': True}, 'status': 400}
addActivityUsernameInIA: activityUsername - ERROR - 400
Aug 17 07:12:01 148543935b58 /USR/SBIN/CRON[6659]: (root) CMD (python3 /usr/local/bin/addActivityUsernameInIA.py --hostname $ELASTICSEARCH_SINK_HOSTNAMES >/proc/1/fd/1 2>/proc/1/fd/2)
Aug 17 07:13:01 148543935b58 /USR/SBIN/CRON[6663]: (root) CMD (python3 /usr/local/bin/addActivityUsernameInIA.py --hostname $ELASTICSEARCH_SINK_HOSTNAMES >/proc/1/fd/1 2>/proc/1/fd/2)
addActivityUsernameInIA: activityUsername - INFO - Executing with the following arguments:
--hostname = svw29-desktop.ciam.cloud.cas.org:9200,
--port = 9200,
--timeout = 60,
--max-retries = 10,
--retry-on-timeout = True,
--start-date = 2022-08-17T10:43:01.000Z,
--end-date = 2022-08-17T11:13:01.000Z
addActivityUsernameInIA: activityUsername - INFO - calling with the processDocuments build 104
addActivityUsernameInIA: activityUsername - INFO - Executing with the following arguments:
--hostname = svw29-desktop.ciam.cloud.cas.org:9200,
--port = 9200,
--timeout = 60,
--max-retries = 10,
--retry-on-timeout = True,
--start-date = 2022-08-17T10:44:02.000Z,
--end-date = 2022-08-17T11:14:02.000Z
addActivityUsernameInIA: activityUsername - INFO - calling with the processDocuments build 104
addActivityUsernameInIA: elasticsearch - WARNING - GET /_all/_search?size=10000 [status:400 request:0.002s]
addActivityUsernameInIA: activityUsername - ERROR - search_phase_execution_exception
addActivityUsernameInIA: activityUsername - ERROR - {'status': 400, 'error': {'grouped': True, 'failed_shards': [{'shard': 0, 'index': 'cst-idx-2020-01', 'reason': {'index': 'cst-idx-2020-01', 'reason': 'No query registered for [must]', 'col': 39, 'type': 'query_parsing_exception', 'line': 1}, 'node': 'Zq15DDiNQzGt-n7Ft2JgNg'}], 'root_cause': [{'index': 'cst-idx-2020-01', 'reason': 'No query registered for [must]', 'col': 39, 'type': 'query_parsing_exception', 'line': 1}], 'type': 'search_phase_execution_exception', 'phase': 'query', 'reason': 'all shards failed'}}
addActivityUsernameInIA: activityUsername - ERROR - 400
Aug 17 07:14:01 148543935b58 /USR/SBIN/CRON[6667]: (root) CMD (python3 /usr/local/bin/addActivityUsernameInIA.py --hostname $ELASTICSEARCH_SINK_HOSTNAMES >/proc/1/fd/1 2>/proc/1/fd/2)
addActivityUsernameInIA: activityUsername - INFO - Executing with the following arguments:
--hostname = svw29-desktop.ciam.cloud.cas.org:9200,
--port = 9200,
--timeout = 60,
--max-retries = 10,
--retry-on-timeout = True,
--start-date = 2022-08-17T10:45:01.000Z,
--end-date = 2022-08-17T11:15:01.000Z
addActivityUsernameInIA: activityUsername - INFO - calling with the processDocuments build 104
Aug 17 07:15:01 148543935b58 /USR/SBIN/CRON[6671]: (root) CMD (python3 /usr/local/bin/addActivityUsernameInIA.py --hostname $ELASTICSEARCH_SINK_HOSTNAMES >/proc/1/fd/1 2>/proc/1/fd/2)