Summary: This article explains how to validate the existence of records in an OpenSearch cluster from a CSV file, performing cluster health checks, logging results in a timestamped log file, and exporting a results CSV for auditing and reporting.
What the script does: It reads recordId and recordDate pairs from a CSV, performs a quick preliminary check on the _cluster/health endpoint, queries OpenSearch to verify exact existence by recordId.keyword and date range for the entire day, generates a timestamped log, and produces a CSV with PASS or FAIL status for each row.
Recommended folder structure: main folder opensearch-checks, .env file with configuration, inputs folder containing records.csv, logs and outputs folders created automatically, and the check_records.py script.
Example of inputs records.csv: first line headers recordId,recordDate; following lines records with UUID format and date in AAAA-MM-DD format such as 2025-07-31.
Mandatory variables in .env: OPENSEARCH_URL with the cluster URL, OPENSEARCH_USER, OPENSEARCH_PASS, INDEX_PATTERN pointing to index_pattern/_search, INPUT_CSV with the path to the input CSV, LOG_DIR and OUTPUT_DIR with paths for logs and results. The script loads strictly from .env without default values in the code.
Minimal OpenSearch client: the script includes a lightweight client that uses urllib to avoid external HTTP dependencies. It supports basic authentication and by default in development examples disables SSL verification to facilitate internal testing. In production, verification should be enabled and trusted certificates used.
Health check: the script executes a GET query to _cluster/health and accepts green and yellow states. If the state is red or the cluster cannot be contacted, the process fails fast avoiding unnecessary queries.
Existence query: for each recordId and recordDate pair, a bool query is made with must of term on recordId.keyword and range on recordDate with gte recordDate 00:00:00.000 and lte recordDate 23:59:59.999. It evaluates whether hits.total.value is greater than zero to determine existence.
Output and logging: logs and a results CSV are created with names including timestamp in AAAA-MM-DD_HH-MM-SS format. The results CSV contains the columns recordId, recordDate, and status where status can be PASS or FAIL. The log records one entry per processed row indicating PASS or FAIL.
How to run: install dependency to read .env with pip install python-dotenv and then run python check_records.py from the project folder, ensuring .env and inputs/records.csv are present.
Security notes: in production environments enable SSL verification, use certificates issued by a trusted CA, and limit credentials. Consider using roles and more robust authentication mechanisms according to the architecture.
Typical use cases: audits, ETL pipelines, reconciliation tasks, post-bulk-ingestion verifications, and monitoring processes that require confirming the presence of records by identifier and date.
Best practices: validate CSV headers before processing, skip rows with missing values, use pagination or adjusted size if extending the logic to document retrieval, and centralize logs for subsequent analysis.
About Q2BSTUDIO: Q2BSTUDIO is a custom software and application development company specialized in artificial intelligence solutions, cybersecurity, and AWS and Azure cloud services. We offer custom software and business intelligence services, including Power BI implementations, AI agents, and AI solutions for companies that improve processes and enable data-driven decisions.
Why choose us: we have experience in custom applications and custom software, expertise in artificial intelligence and AI agent projects, and cybersecurity practices integrated into the development cycle. We also provide AWS and Azure cloud services and business intelligence solutions to integrate Power BI and advanced analytics.
Strategic keywords: custom applications, custom software, artificial intelligence, cybersecurity, AWS and Azure cloud services, business intelligence services, AI for companies, AI agents, Power BI.
Contact and services: if you need to adapt this script to your environment, integrate validations into CI CD pipelines, or deploy complete analytics and security solutions, Q2BSTUDIO can help with consulting, development, and managed operations.
Final summary: with a simple and secure approach, you can validate the existence of records in OpenSearch from a CSV, obtain traceability through logs and CSV results, and ensure operability through a preliminary cluster health check. For extensions and professional support, request Q2BSTUDIO custom development services, artificial intelligence integration, and cybersecurity strategies.
