{"id":1590,"date":"2024-09-21T12:29:33","date_gmt":"2024-09-21T10:29:33","guid":{"rendered":"https:\/\/cln.io\/blog\/?p=1590"},"modified":"2024-10-12T16:49:03","modified_gmt":"2024-10-12T14:49:03","slug":"monitor-on-premise-tines-database","status":"publish","type":"post","link":"https:\/\/cln.io\/blog\/monitor-on-premise-tines-database\/","title":{"rendered":"Tines: On-premises deployments, monitor your stories sizes in the database and disk (events + payloads) by querying the database itself and processing it with Tines"},"content":{"rendered":"\n<nav aria-label=\"Table of Contents\" class=\"wp-block-table-of-contents\"><ol><li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/cln.io\/blog\/monitor-on-premise-tines-database\/#the-problem\">The problem<\/a><\/li><li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/cln.io\/blog\/monitor-on-premise-tines-database\/#solution\">Solution<\/a><\/li><li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/cln.io\/blog\/monitor-on-premise-tines-database\/#example-output\">Example output<\/a><\/li><li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/cln.io\/blog\/monitor-on-premise-tines-database\/#the-implementation\">The implementation <\/a><ol><li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/cln.io\/blog\/monitor-on-premise-tines-database\/#the-shell-script\">The shell script<\/a><\/li><li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/cln.io\/blog\/monitor-on-premise-tines-database\/#the-sql-scripts\">The SQL scripts<\/a><ol><li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/cln.io\/blog\/monitor-on-premise-tines-database\/#track-event-size-and-payload-sizes-per-story\">Track event size and payload sizes per story<\/a><\/li><li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/cln.io\/blog\/monitor-on-premise-tines-database\/#keep-an-eye-on-old-events\">Keep an eye on old events<\/a><\/li><li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/cln.io\/blog\/monitor-on-premise-tines-database\/#keep-an-eye-on-old-payloads-1\">Keep an eye on old payloads<\/a><\/li><\/ol><\/li><li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/cln.io\/blog\/monitor-on-premise-tines-database\/#notes\">Notes<\/a><\/li><li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/cln.io\/blog\/monitor-on-premise-tines-database\/#the-tines-processing\">The Tines processing<\/a><ol><li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/cln.io\/blog\/monitor-on-premise-tines-database\/#the-tines-flow\">The Tines flow<\/a><\/li><\/ol><\/li><\/ol><\/li><\/ol><\/nav>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"the-problem\">The problem<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">As much as I like Tines (deployed on-premise) their self-hosted metrics\/statistics could use some improvements, there is no 1-to-1 link or view between stories and their sizes in the database (events + payloads) so you are going in blind, and in some cases you discover your Postgres database has grown from a manageable 50GB to a whopping 400GB&#8217;s so it was time to implement some monitoring on the usage of Tines stories.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"solution\">Solution<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Have a cronjob fire up a bash script that scans a directory for SQL queries to execute.<\/li>\n\n\n\n<li>Have a SQL script to collect the container id&#8217;s and their associated events sizes and payload sizes<\/li>\n\n\n\n<li>Have the script beam this data back to a tines webhook, keyed under the scripts filename<\/li>\n\n\n\n<li>Process the JSON data with Tines, and build a nice markdown table.\n<ul class=\"wp-block-list\">\n<li>up to you to determine what happens next<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"569\" src=\"https:\/\/cln.io\/blog\/wp-content\/uploads\/2024\/09\/tinescron-1024x569.png\" alt=\"\" class=\"wp-image-1591\" srcset=\"https:\/\/cln.io\/blog\/wp-content\/uploads\/2024\/09\/tinescron-1024x569.png 1024w, https:\/\/cln.io\/blog\/wp-content\/uploads\/2024\/09\/tinescron-300x167.png 300w, https:\/\/cln.io\/blog\/wp-content\/uploads\/2024\/09\/tinescron-768x427.png 768w, https:\/\/cln.io\/blog\/wp-content\/uploads\/2024\/09\/tinescron-1536x854.png 1536w, https:\/\/cln.io\/blog\/wp-content\/uploads\/2024\/09\/tinescron.png 2032w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n<\/div>\n\n\n<h2 class=\"wp-block-heading\" id=\"example-output\">Example output<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">(Markdown screenshot, HTML table below)<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"303\" src=\"https:\/\/cln.io\/blog\/wp-content\/uploads\/2024\/09\/image-1-1024x303.png\" alt=\"\" class=\"wp-image-1597\" srcset=\"https:\/\/cln.io\/blog\/wp-content\/uploads\/2024\/09\/image-1-1024x303.png 1024w, https:\/\/cln.io\/blog\/wp-content\/uploads\/2024\/09\/image-1-300x89.png 300w, https:\/\/cln.io\/blog\/wp-content\/uploads\/2024\/09\/image-1-768x227.png 768w, https:\/\/cln.io\/blog\/wp-content\/uploads\/2024\/09\/image-1-1536x454.png 1536w, https:\/\/cln.io\/blog\/wp-content\/uploads\/2024\/09\/image-1-2048x606.png 2048w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<figure class=\"wp-block-table aligncenter has-fixed-layout is-style-stripes\"><table class=\"has-fixed-layout\"><thead><tr><th class=\"has-text-align-left\" data-align=\"left\">Name<\/th><th>Total Size (GB)<\/th><th>Total Size (MB)<\/th><th>Events Size (GB)<\/th><th>Events Size (MB)<\/th><th>Output Payload Size (GB)<\/th><th>Output Payload Size (MB)<\/th><th>Keep Events For (Days)<\/th><\/tr><\/thead><tbody><tr><td class=\"has-text-align-left\" data-align=\"left\"><strong>Total<\/strong><\/td><td>180<\/td><td>184<\/td><td>68<\/td><td>69632<\/td><td>112<\/td><td>114688<\/td><td>N\/A<\/td><\/tr><tr><td class=\"has-text-align-left\" data-align=\"left\">Event Enrichment<\/td><td>83<\/td><td>84992<\/td><td>64<\/td><td>65536<\/td><td>18<\/td><td>18432<\/td><td>14<\/td><\/tr><tr><td class=\"has-text-align-left\" data-align=\"left\">Data Analysis<\/td><td>11<\/td><td>11264<\/td><td>1<\/td><td>1024<\/td><td>9<\/td><td>9216<\/td><td>21<\/td><\/tr><tr><td class=\"has-text-align-left\" data-align=\"left\">Threat Analysis<\/td><td>3<\/td><td>3072<\/td><td>1<\/td><td>1024<\/td><td>2<\/td><td>2048<\/td><td>7<\/td><\/tr><tr><td class=\"has-text-align-left\" data-align=\"left\">Log Search<\/td><td>4<\/td><td>4096<\/td><td>1<\/td><td>1024<\/td><td>3<\/td><td>3072<\/td><td>31<\/td><\/tr><tr><td class=\"has-text-align-left\" data-align=\"left\">Alert Handling<\/td><td>3<\/td><td>3072<\/td><td>1<\/td><td>1024<\/td><td>3<\/td><td>3072<\/td><td>14<\/td><\/tr><tr><td class=\"has-text-align-left\" data-align=\"left\">Campaign Import<\/td><td>1<\/td><td>1024<\/td><td>0<\/td><td>0<\/td><td>1<\/td><td>1024<\/td><td>21<\/td><\/tr><tr><td class=\"has-text-align-left\" data-align=\"left\">Email Notifications<\/td><td>1<\/td><td>1024<\/td><td>0<\/td><td>0<\/td><td>0<\/td><td>0<\/td><td>31<\/td><\/tr><tr><td class=\"has-text-align-left\" data-align=\"left\">System Wait<\/td><td>0<\/td><td>0<\/td><td>0<\/td><td>0<\/td><td>0<\/td><td>0<\/td><td>7<\/td><\/tr><tr><td class=\"has-text-align-left\" data-align=\"left\">Security Monitoring<\/td><td>0<\/td><td>0<\/td><td>0<\/td><td>0<\/td><td>0<\/td><td>0<\/td><td>14<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"the-implementation\">The implementation <\/h2>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"the-shell-script\">The shell script<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">NOTE: Script was &#8220;made&#8221; by OpenAI o1-preview<br>NOTE: This targets Tines DB running in a docker<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">You want to tweak the variables<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"bash\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\"># Variables\nDOCKER_CONTAINER_NAME=\"tines_db\"\nWEBHOOK_URL=\"https:\/\/tines.internal\/webhook\/abc123xyz4567890\/abcdef1234567890abcdef1234567890\"\nOUTPUT_FILE_PREFIX=\"tines_table_monitor\"\nLOG_FILE=\"tines_monitor.log\"\nQUERIES_DIR=\"queries\"<\/pre>\n\n\n\n<pre class=\"EnlighterJSRAW enlighter-collapse\"  style=\"max-height: 100px;\" data-enlighter-language=\"bash\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"monitor.sh\" data-enlighter-group=\"\">#!\/bin\/bash\nset -e\n# Uncomment this line to enable script tracing for debugging\n# set -x  \n# Variables\nDOCKER_CONTAINER_NAME=\"tines_db\"\nWEBHOOK_URL=\"https:\/\/tines.internal\/webhook\/abc123xyz4567890\/abcdef1234567890abcdef1234567890\"\nOUTPUT_FILE_PREFIX=\"tines_table_monitor\"\nLOG_FILE=\"tines_monitor.log\"\nQUERIES_DIR=\"queries\"\n# Function to log messages with timestamp\nlog() {\n  echo \"$(date '+%Y-%m-%d %H:%M:%S') - $1\" | tee -a \"$LOG_FILE\"\n}\n# Start script execution\nlog \"Starting Tines monitor script.\"\n# Retrieve the Docker container ID\nlog \"Retrieving Docker container ID for container named '$DOCKER_CONTAINER_NAME'.\"\nCONTAINER_ID=$(docker ps -q --filter \"name=$DOCKER_CONTAINER_NAME\")\nif [[ -z \"$CONTAINER_ID\" ]]; then\n  log \"Error: No running Docker container found with name '$DOCKER_CONTAINER_NAME'.\"\n  exit 1\nelse\n  log \"Found Docker container ID: $CONTAINER_ID.\"\nfi\n# Function to execute SQL query inside Docker and return JSON output\nexecute_sql_query() {\n  set +e  # Disable exit on error within this function\n  local query=\"$1\"\n  local output\n  local exit_code\n  output=$(docker exec \"$CONTAINER_ID\" \\\n    psql -t -A -U tines -d tines_production -c \"$query\" 2>&amp;1)\n  exit_code=$?\n  if [[ $exit_code -ne 0 ]]; then\n    log \"Error: Failed to execute SQL query.\"\n    log \"Exit Code: $exit_code\"\n    log \"Output: $output\"\n    exit 1\n  fi\n  set -e  # Re-enable exit on error\n  # Remove any PSQL notices or empty lines\n  output=$(echo \"$output\" | sed '\/^$\/d')\n  # Validate JSON or handle NULL output\n  if [[ -z \"$output\" ]] || [[ \"$output\" == \"null\" ]]; then\n    log \"Notice: Query returned NULL or empty result.\"\n    output=\"[]\"  # Set output to an empty JSON array\n  else\n    if ! echo \"$output\" | jq empty >\/dev\/null 2>&amp;1; then\n      log \"Error: Invalid JSON output.\"\n      log \"Output: $output\"\n      exit 1\n    fi\n  fi\n  echo \"$output\"\n}\n# Function to send output to webhook\nsend_to_webhook() {\n  local data=\"$1\"\n  local key=\"$2\"\n  local payload=\"{\\\"$key\\\": $data}\"\n  log \"Sending data to webhook with key '$key'.\"\n  CURL_RESPONSE=$(curl -s -w \"%{http_code}\" -X POST -H \"Content-Type: application\/json\" --data \"$payload\" \"$WEBHOOK_URL\" -o \/dev\/null)\n  if [[ \"$CURL_RESPONSE\" -ne 201 ]]; then\n    log \"Error: Failed to send data to webhook. HTTP status code: $CURL_RESPONSE.\"\n    exit 1\n  else\n    log \"Data sent to webhook successfully with key '$key'. HTTP status code: $CURL_RESPONSE.\"\n  fi\n}\n# Check if queries directory exists\nif [[ ! -d \"$QUERIES_DIR\" ]]; then\n  log \"Error: Queries directory '$QUERIES_DIR' does not exist.\"\n  exit 1\nfi\n# Process each .sql file in the queries directory\nfor sql_file in \"$QUERIES_DIR\"\/*.sql; do\n  # Check if any .sql files exist\n  if [[ ! -e \"$sql_file\" ]]; then\n    log \"Notice: No .sql files found in '$QUERIES_DIR'.\"\n    break\n  fi\n  # Get the base filename without extension to use as key\n  filename=$(basename \"$sql_file\")\n  key=\"${filename%.*}\"  # Remove extension\n  log \"Processing SQL file '$sql_file' with key '$key'.\"\n  # Read the SQL query from the file\n  query=$(cat \"$sql_file\")\n  # Execute the SQL query\n  log \"Executing SQL query from file '$sql_file'.\"\n  output=$(execute_sql_query \"$query\")\n  log \"SQL query executed successfully.\"\n  # Save output to file\n  output_file=\"${OUTPUT_FILE_PREFIX}_${key}.json\"\n  log \"Saving query output to file '$output_file'.\"\n  echo \"$output\" > \"$output_file\"\n  if [[ $? -ne 0 ]]; then\n    log \"Error: Failed to write output to '$output_file'.\"\n    exit 1\n  else\n    log \"Output saved to '$output_file'.\"\n  fi\n  # Send output to webhook\n  send_to_webhook \"$output\" \"$key\"\ndone\nlog \"Tines monitor script completed successfully.\"\n<\/pre>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"the-sql-scripts\">The SQL scripts<\/h3>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"track-event-size-and-payload-sizes-per-story\">Track event size and payload sizes per story<\/h4>\n\n\n\n<pre class=\"EnlighterJSRAW enlighter-collapse\" data-enlighter-language=\"sql\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">SELECT json_agg(row_to_json(t))\nFROM (\n    SELECT\n        s.id AS storyID,\n        s.story_container_id AS story_container_ID,\n        -- Calculate the size of the events table data\n        SUM(COALESCE(pg_column_size(e), 0)) AS eventsBaseSize,\n        -- Sum sizes of all large columns in events\n        SUM(COALESCE(octet_length(e.payload::text), 0)) +\n        SUM(COALESCE(octet_length(e.generated_payload::text), 0)) +\n        SUM(COALESCE(octet_length(e.previous_events_ids::text), 0)) +\n        SUM(COALESCE(octet_length(e.new_previous_events_ids::text), 0)) +\n        SUM(COALESCE(octet_length(e.received_payload_ids::text), 0)) AS eventsLargeColumnsSize,\n        -- Total events size\n        SUM(COALESCE(pg_column_size(e), 0)) +\n        SUM(COALESCE(octet_length(e.payload::text), 0)) +\n        SUM(COALESCE(octet_length(e.generated_payload::text), 0)) +\n        SUM(COALESCE(octet_length(e.previous_events_ids::text), 0)) +\n        SUM(COALESCE(octet_length(e.new_previous_events_ids::text), 0)) +\n        SUM(COALESCE(octet_length(e.received_payload_ids::text), 0)) AS eventsSize,\n        -- Calculate the size of the output_payloads table data\n        SUM(COALESCE(pg_column_size(o), 0)) AS outputPayloadsBaseSize,\n        -- Sum sizes of all large columns in output_payloads\n        SUM(COALESCE(octet_length(o.output::text), 0)) AS outputPayloadsLargeColumnsSize,\n        -- Total output_payloads size\n        SUM(COALESCE(pg_column_size(o), 0)) +\n        SUM(COALESCE(octet_length(o.output::text), 0)) AS outputPayloadSize,\n        -- Total size\n        (\n            SUM(COALESCE(pg_column_size(e), 0)) +\n            SUM(COALESCE(octet_length(e.payload::text), 0)) +\n            SUM(COALESCE(octet_length(e.generated_payload::text), 0)) +\n            SUM(COALESCE(octet_length(e.previous_events_ids::text), 0)) +\n            SUM(COALESCE(octet_length(e.new_previous_events_ids::text), 0)) +\n            SUM(COALESCE(octet_length(e.received_payload_ids::text), 0)) +\n            SUM(COALESCE(pg_column_size(o), 0)) +\n            SUM(COALESCE(octet_length(o.output::text), 0))\n        ) AS totalSize\n    FROM\n        stories s\n        JOIN agents a ON a.story_id = s.id\n        JOIN events e ON e.agent_id = a.id\n        LEFT JOIN output_payloads o ON o.id = e.output_payload_id\n    GROUP BY\n        s.id, s.story_container_id\n) t;\n<\/pre>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"keep-an-eye-on-old-events\">Keep an eye on old events<\/h4>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">SELECT json_agg(row_to_json(t))\nFROM (\n  SELECT id, created_at \n  FROM events \n  ORDER BY created_at ASC \n  LIMIT 200\n) t;<\/pre>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"keep-an-eye-on-old-payloads-1\">Keep an eye on old payloads<\/h4>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">SELECT json_agg(row_to_json(t))\nFROM (\n  SELECT id, created_at \n  FROM output_payloads \n  ORDER BY created_at ASC \n  LIMIT 200\n) t;<\/pre>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"notes\">Notes<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Depending on your deployment you will have to increase \/dev\/shm in the Postgress container to be able to deal with the larger queries.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"the-tines-processing\">The Tines processing<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">So we send the JSON output of the scrip to Tines.<br>As each SQL script requires different processing, we check the .SQL file that send us the data, in our case the monitor_table_size.sql as this is our primary focus<\/p>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"the-tines-flow\">The Tines flow<\/h4>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"676\" height=\"1024\" src=\"https:\/\/cln.io\/blog\/wp-content\/uploads\/2024\/09\/image-2-676x1024.png\" alt=\"\" class=\"wp-image-1600\" srcset=\"https:\/\/cln.io\/blog\/wp-content\/uploads\/2024\/09\/image-2-676x1024.png 676w, https:\/\/cln.io\/blog\/wp-content\/uploads\/2024\/09\/image-2-198x300.png 198w, https:\/\/cln.io\/blog\/wp-content\/uploads\/2024\/09\/image-2-768x1163.png 768w, https:\/\/cln.io\/blog\/wp-content\/uploads\/2024\/09\/image-2.png 894w\" sizes=\"auto, (max-width: 676px) 100vw, 676px\" \/><\/figure>\n\n\n\n<pre class=\"EnlighterJSRAW enlighter-collapse\" data-enlighter-language=\"json\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"false\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">{\"standardLibVersion\":\"59\",\"actionRuntimeVersion\":\"13\",\"agents\":[{\"disabled\":false,\"name\":\"monitor.sh\",\"description\":null,\"options\":\"{\\\"path\\\":\\\"7dd3290ffab74fdce0217c9523c8f6ca\\\",\\\"secret\\\":\\\"d5ffb328d2e25f763ac453eb981282ea\\\",\\\"verbs\\\":\\\"get,post\\\"}\",\"position\":{\"x\":495,\"y\":-15},\"type\":\"webhook\",\"timeSavedUnit\":\"minutes\",\"timeSavedValue\":0,\"monitorAllEvents\":false,\"monitorFailures\":true,\"monitorNoEventsEmitted\":null,\"caseConfiguration\":{\"subStatus\":null},\"recordType\":null,\"recordWriters\":[],\"form\":null,\"createdFromTemplateGuid\":null,\"createdFromTemplateVersion\":null,\"originStoryIdentifier\":\"self-hosted:258783525fbbd9621a6129e1531a01b9:ce872e3c829ff9d228d017c76b7317c1\"},{\"disabled\":false,\"name\":\"payload\",\"description\":null,\"options\":\"{\\\"mode\\\":\\\"message_only\\\",\\\"loop\\\":false,\\\"payload\\\":\\\"=VALUES(input.payload) |> FIRST(%)\\\"}\",\"position\":{\"x\":495,\"y\":240},\"type\":\"eventTransformation\",\"timeSavedUnit\":\"minutes\",\"timeSavedValue\":0,\"monitorAllEvents\":false,\"monitorFailures\":true,\"monitorNoEventsEmitted\":null,\"caseConfiguration\":{\"subStatus\":null},\"recordType\":null,\"recordWriters\":[],\"form\":null,\"createdFromTemplateGuid\":null,\"createdFromTemplateVersion\":null,\"originStoryIdentifier\":\"cloud:8a398de9ced29eb779cc34d883c19760:a339e89b8aa7a74c7ab8fb476ecd5456\"},{\"disabled\":false,\"name\":\"[Tines] Query story\",\"description\":null,\"options\":\"{\\\"url\\\":\\\"&lt;&lt;RESOURCE.tines_api.url>>\/api\/v1\/stories\/&lt;&lt;explode.entry.story_container_id>>\\\",\\\"content_type\\\":\\\"application_json\\\",\\\"method\\\":\\\"get\\\",\\\"payload\\\":\\\"\\\",\\\"headers\\\":{\\\"content-type\\\":\\\"application\/json\\\",\\\"x-user-email\\\":\\\"\\\",\\\"x-user-token\\\":\\\"&lt;&lt;CREDENTIAL.tines_api_key_colin>>\\\"}}\",\"position\":{\"x\":495,\"y\":465},\"type\":\"httpRequest\",\"timeSavedUnit\":\"minutes\",\"timeSavedValue\":0,\"monitorAllEvents\":false,\"monitorFailures\":true,\"monitorNoEventsEmitted\":null,\"caseConfiguration\":{\"subStatus\":null},\"recordType\":null,\"recordWriters\":[],\"form\":null,\"createdFromTemplateGuid\":null,\"createdFromTemplateVersion\":null,\"originStoryIdentifier\":\"cloud:8a398de9ced29eb779cc34d883c19760:a339e89b8aa7a74c7ab8fb476ecd5456\"},{\"disabled\":false,\"name\":\"explode\",\"description\":null,\"options\":\"{\\\"mode\\\":\\\"explode\\\",\\\"path\\\":\\\"=payload\\\",\\\"to\\\":\\\"entry\\\",\\\"limit\\\":\\\"500\\\"}\",\"position\":{\"x\":495,\"y\":345},\"type\":\"eventTransformation\",\"timeSavedUnit\":\"minutes\",\"timeSavedValue\":0,\"monitorAllEvents\":false,\"monitorFailures\":false,\"monitorNoEventsEmitted\":null,\"caseConfiguration\":{\"subStatus\":null},\"recordType\":null,\"recordWriters\":[],\"form\":null,\"createdFromTemplateGuid\":null,\"createdFromTemplateVersion\":null,\"originStoryIdentifier\":\"cloud:8a398de9ced29eb779cc34d883c19760:a339e89b8aa7a74c7ab8fb476ecd5456\"},{\"disabled\":false,\"name\":\"monitor_table_size.sql\",\"description\":null,\"options\":\"{\\\"rules\\\":[{\\\"type\\\":\\\"field==value\\\",\\\"value\\\":\\\"monitor_table_size\\\",\\\"path\\\":\\\"&lt;&lt;KEYS(input.payload) |> FIRST(%)>>\\\"}]}\",\"position\":{\"x\":495,\"y\":120},\"type\":\"trigger\",\"timeSavedUnit\":\"minutes\",\"timeSavedValue\":0,\"monitorAllEvents\":false,\"monitorFailures\":false,\"monitorNoEventsEmitted\":null,\"caseConfiguration\":{\"subStatus\":null},\"recordType\":null,\"recordWriters\":[],\"form\":null,\"createdFromTemplateGuid\":null,\"createdFromTemplateVersion\":null,\"originStoryIdentifier\":\"cloud:8a398de9ced29eb779cc34d883c19760:a339e89b8aa7a74c7ab8fb476ecd5456\"},{\"disabled\":false,\"name\":\"prep4implode\",\"description\":null,\"options\":\"{\\\"mode\\\":\\\"message_only\\\",\\\"loop\\\":false,\\\"payload\\\":\\\"=explode.entry\\\\n|> MERGE(%, tines_query_story.body)\\\\n|> \\\\n\\\\n SET_KEY(%, \\\\\\\"eventssize_mb\\\\\\\", DIVIDED_BY(explode.entry.eventssize,1000000.0))\\\\n  |>\\\\n   SET_KEY(%, \\\\\\\"eventssize_gb\\\\\\\", DIVIDED_BY(explode.entry.eventssize,1000000000.0))\\\\n \\\\n|>\\\\n\\\\n SET_KEY(%, \\\\\\\"outputpayloadsize_mb\\\\\\\", DIVIDED_BY(explode.entry.outputpayloadsize,1000000.0))\\\\n  |>\\\\n   SET_KEY(%, \\\\\\\"outputpayloadsize_gb\\\\\\\", DIVIDED_BY(explode.entry.outputpayloadsize,1000000000.0))\\\\n\\\\n|>\\\\n\\\\n SET_KEY(%, \\\\\\\"totalsize_mb\\\\\\\", DIVIDED_BY(explode.entry.totalsize,1000000.0))\\\\n  |>\\\\n   SET_KEY(%, \\\\\\\"totalsize_gb\\\\\\\", DIVIDED_BY(explode.entry.totalsize,1000000000.0))\\\\n \\\"}\",\"position\":{\"x\":495,\"y\":555},\"type\":\"eventTransformation\",\"timeSavedUnit\":\"minutes\",\"timeSavedValue\":0,\"monitorAllEvents\":false,\"monitorFailures\":false,\"monitorNoEventsEmitted\":null,\"caseConfiguration\":{\"subStatus\":null},\"recordType\":null,\"recordWriters\":[],\"form\":null,\"createdFromTemplateGuid\":null,\"createdFromTemplateVersion\":null,\"originStoryIdentifier\":\"cloud:8a398de9ced29eb779cc34d883c19760:a339e89b8aa7a74c7ab8fb476ecd5456\"},{\"disabled\":false,\"name\":\"implode\",\"description\":null,\"options\":\"{\\\"mode\\\":\\\"implode\\\",\\\"item_path\\\":\\\"=prep4implode\\\",\\\"guid_path\\\":\\\"=explode.guid\\\",\\\"size_path\\\":\\\"=explode.size\\\"}\",\"position\":{\"x\":495,\"y\":660},\"type\":\"eventTransformation\",\"timeSavedUnit\":\"minutes\",\"timeSavedValue\":0,\"monitorAllEvents\":false,\"monitorFailures\":false,\"monitorNoEventsEmitted\":null,\"caseConfiguration\":{\"subStatus\":null},\"recordType\":null,\"recordWriters\":[],\"form\":null,\"createdFromTemplateGuid\":null,\"createdFromTemplateVersion\":null,\"originStoryIdentifier\":\"cloud:8a398de9ced29eb779cc34d883c19760:a339e89b8aa7a74c7ab8fb476ecd5456\"},{\"disabled\":false,\"name\":\"sort\",\"description\":null,\"options\":\"{\\\"mode\\\":\\\"message_only\\\",\\\"loop\\\":false,\\\"payload\\\":\\\"=SORT(\\\\n  \\\\n  implode,\\\\n  \\\\n  \\\\\\\"totalsize\\\\\\\")\\\\n  \\\\n  \\\\n|> REVERSE(%)\\\"}\",\"position\":{\"x\":720,\"y\":-15},\"type\":\"eventTransformation\",\"timeSavedUnit\":\"minutes\",\"timeSavedValue\":0,\"monitorAllEvents\":false,\"monitorFailures\":false,\"monitorNoEventsEmitted\":null,\"caseConfiguration\":{\"subStatus\":null},\"recordType\":null,\"recordWriters\":[],\"form\":null,\"createdFromTemplateGuid\":null,\"createdFromTemplateVersion\":null,\"originStoryIdentifier\":\"cloud:8a398de9ced29eb779cc34d883c19760:a339e89b8aa7a74c7ab8fb476ecd5456\"},{\"disabled\":false,\"name\":\"sum\",\"description\":null,\"options\":\"{\\\"mode\\\":\\\"message_only\\\",\\\"loop\\\":false,\\\"payload\\\":{\\\"k\\\":\\\"=sort\\\\n|> MAP(%,\\\\\\\"totalsize\\\\\\\")\\\\n|> SUM(%)\\\",\\\"mb\\\":\\\"=sort\\\\n|> MAP(%,\\\\\\\"totalsize\\\\\\\")\\\\n|> DIVIDED_BY(SUM(%),1000000.0)\\\",\\\"gb\\\":\\\"=sort\\\\n|> MAP(%,\\\\\\\"totalsize\\\\\\\")\\\\n|> DIVIDED_BY(SUM(%),1000000000.0)\\\"}}\",\"position\":{\"x\":720,\"y\":90},\"type\":\"eventTransformation\",\"timeSavedUnit\":\"minutes\",\"timeSavedValue\":0,\"monitorAllEvents\":false,\"monitorFailures\":false,\"monitorNoEventsEmitted\":null,\"caseConfiguration\":{\"subStatus\":null},\"recordType\":null,\"recordWriters\":[],\"form\":null,\"createdFromTemplateGuid\":null,\"createdFromTemplateVersion\":null,\"originStoryIdentifier\":\"cloud:8a398de9ced29eb779cc34d883c19760:a339e89b8aa7a74c7ab8fb476ecd5456\"},{\"disabled\":false,\"name\":\"markdown\",\"description\":null,\"options\":\"{\\\"mode\\\":\\\"message_only\\\",\\\"loop\\\":false,\\\"payload\\\":\\\"| Name                                                     | Total Size (GB) | Total Size (MB) | Events Size (GB) | Events Size (MB) | Output Payload Size (GB) | Output Payload Size (MB) | Keep Events For (Days) |\\\\n|-----------------|------------|---------|------------|------------------|------------------|-------------------|----------------|\\\\n| **Total**  | &lt;&lt;ROUND(sum_check.sum_totalsize_gb)>> | &lt;&lt;ROUND(sum_check.sum_totalsize_mb)>> | &lt;&lt;ROUND(sum_check.sum_eventssize_gb)>> | &lt;&lt;ROUND(sum_check.sum_eventssize_mb)>> | &lt;&lt;ROUND(sum_check.sum_outputpayloadsize_gb)>> | &lt;&lt;ROUND(sum_check.sum_outputpayloadsize_mb)>> | N\/A |\\\\n&lt;%for x in DEFAULT(sort_remap,implode)%>| [&lt;&lt;RESOURCE.handy_lambdas.substring_dotdotdot(x.name,30)>>](https:\/\/tines.internal\/stories\/&lt;&lt;x.story_container_id>>) | &lt;&lt;ROUND(x.sum_totalsize_gb)>> | &lt;&lt;ROUND(x.sum_totalsize_mb)>> | &lt;&lt;ROUND(x.sum_eventssize_gb)>> | &lt;&lt;ROUND(x.sum_eventssize_mb)>> | &lt;&lt;ROUND(x.sum_outputpayloadsize_gb)>> | &lt;&lt;ROUND(x.sum_outputpayloadsize_mb)>> | &lt;&lt;x.keep_events_for \/ 86400>> |\\\\n&lt;%endfor%>\\\\n\\\"}\",\"position\":{\"x\":720,\"y\":660},\"type\":\"eventTransformation\",\"timeSavedUnit\":\"minutes\",\"timeSavedValue\":0,\"monitorAllEvents\":false,\"monitorFailures\":false,\"monitorNoEventsEmitted\":null,\"caseConfiguration\":{\"subStatus\":null},\"recordType\":null,\"recordWriters\":[],\"form\":null,\"createdFromTemplateGuid\":null,\"createdFromTemplateVersion\":null,\"originStoryIdentifier\":\"cloud:8a398de9ced29eb779cc34d883c19760:a339e89b8aa7a74c7ab8fb476ecd5456\"},{\"disabled\":false,\"name\":\"story_container_id_unique\",\"description\":null,\"options\":\"{\\\"mode\\\":\\\"message_only\\\",\\\"loop\\\":false,\\\"payload\\\":\\\"=MAP(implode,\\\\\\\"story_container_id\\\\\\\") |> UNIQ(%)\\\"}\",\"position\":{\"x\":720,\"y\":180},\"type\":\"eventTransformation\",\"timeSavedUnit\":\"minutes\",\"timeSavedValue\":0,\"monitorAllEvents\":false,\"monitorFailures\":false,\"monitorNoEventsEmitted\":null,\"caseConfiguration\":{\"subStatus\":null},\"recordType\":null,\"recordWriters\":[],\"form\":null,\"createdFromTemplateGuid\":null,\"createdFromTemplateVersion\":null,\"originStoryIdentifier\":\"cloud:8a398de9ced29eb779cc34d883c19760:a339e89b8aa7a74c7ab8fb476ecd5456\"},{\"disabled\":false,\"name\":\"remap\",\"description\":null,\"options\":\"{\\\"mode\\\":\\\"message_only\\\",\\\"loop\\\":\\\"=story_container_id_unique\\\",\\\"payload\\\":\\\"=implode\\\\n|> WHERE(%,\\\\\\\"story_container_id\\\\\\\",LOOP.value) |> FIRST(%)\\\\n\\\\n|> SET_KEY(%,\\\\\\\"sum_count\\\\\\\", SIZE((WHERE(implode,\\\\\\\"story_container_id\\\\\\\",LOOP.value))))\\\\n\\\\n|> SET_KEY(%,\\\\\\\"sum_eventssize\\\\\\\", SUM(MAP(WHERE(implode,\\\\\\\"story_container_id\\\\\\\",LOOP.value),\\\\\\\"eventssize\\\\\\\")))\\\\n\\\\n|> SET_KEY(%,\\\\\\\"sum_outputpayloadsize\\\\\\\", SUM(MAP(WHERE(implode,\\\\\\\"story_container_id\\\\\\\",LOOP.value),\\\\\\\"outputpayloadsize\\\\\\\")))\\\\n\\\\n|> SET_KEY(%,\\\\\\\"sum_totalsize_mb\\\\\\\", SUM(MAP(WHERE(implode,\\\\\\\"story_container_id\\\\\\\",LOOP.value),\\\\\\\"totalsize_mb\\\\\\\")) )\\\\n\\\\n|> SET_KEY(%,\\\\\\\"sum_eventssize_mb\\\\\\\", SUM(MAP(WHERE(implode,\\\\\\\"story_container_id\\\\\\\",LOOP.value),\\\\\\\"eventssize_mb\\\\\\\")))\\\\n\\\\n|> SET_KEY(%,\\\\\\\"sum_outputpayloadsize_mb\\\\\\\", SUM(MAP(WHERE(implode,\\\\\\\"story_container_id\\\\\\\",LOOP.value),\\\\\\\"outputpayloadsize_mb\\\\\\\")))\\\\n\\\\n|> SET_KEY(%,\\\\\\\"sum_totalsize_mb\\\\\\\", SUM(MAP(WHERE(implode,\\\\\\\"story_container_id\\\\\\\",LOOP.value),\\\\\\\"totalsize_mb\\\\\\\")) )\\\\n\\\\n|> SET_KEY(%,\\\\\\\"sum_eventssize_gb\\\\\\\", SUM(MAP(WHERE(implode,\\\\\\\"story_container_id\\\\\\\",LOOP.value),\\\\\\\"eventssize_gb\\\\\\\")))\\\\n\\\\n|> SET_KEY(%,\\\\\\\"sum_outputpayloadsize_gb\\\\\\\", SUM(MAP(WHERE(implode,\\\\\\\"story_container_id\\\\\\\",LOOP.value),\\\\\\\"outputpayloadsize_gb\\\\\\\")))\\\\n\\\\n|> SET_KEY(%,\\\\\\\"sum_totalsize_gb\\\\\\\", SUM(MAP(WHERE(implode,\\\\\\\"story_container_id\\\\\\\",LOOP.value),\\\\\\\"totalsize_gb\\\\\\\")) )\\\"}\",\"position\":{\"x\":720,\"y\":255},\"type\":\"eventTransformation\",\"timeSavedUnit\":\"minutes\",\"timeSavedValue\":0,\"monitorAllEvents\":false,\"monitorFailures\":false,\"monitorNoEventsEmitted\":null,\"caseConfiguration\":{\"subStatus\":null},\"recordType\":null,\"recordWriters\":[],\"form\":null,\"createdFromTemplateGuid\":null,\"createdFromTemplateVersion\":null,\"originStoryIdentifier\":\"cloud:8a398de9ced29eb779cc34d883c19760:a339e89b8aa7a74c7ab8fb476ecd5456\"},{\"disabled\":false,\"name\":\"sort remap\",\"description\":null,\"options\":\"{\\\"mode\\\":\\\"message_only\\\",\\\"loop\\\":false,\\\"payload\\\":\\\"=SORT(remap,\\\\\\\"sum_eventssize_gb\\\\\\\") |> REVERSE(%)\\\"}\",\"position\":{\"x\":720,\"y\":375},\"type\":\"eventTransformation\",\"timeSavedUnit\":\"minutes\",\"timeSavedValue\":0,\"monitorAllEvents\":false,\"monitorFailures\":false,\"monitorNoEventsEmitted\":null,\"caseConfiguration\":{\"subStatus\":null},\"recordType\":null,\"recordWriters\":[],\"form\":null,\"createdFromTemplateGuid\":null,\"createdFromTemplateVersion\":null,\"originStoryIdentifier\":\"cloud:8a398de9ced29eb779cc34d883c19760:a339e89b8aa7a74c7ab8fb476ecd5456\"},{\"disabled\":false,\"name\":\"sum check\",\"description\":null,\"options\":\"{\\\"mode\\\":\\\"message_only\\\",\\\"loop\\\":false,\\\"payload\\\":{\\\"sum_count\\\":\\\"=MAP(remap,\\\\\\\"sum_count\\\\\\\") |> SUM(%)\\\",\\\"sum_eventssize\\\":\\\"=MAP(remap,\\\\\\\"sum_eventssize\\\\\\\") |> SUM(%)\\\",\\\"sum_outputpayloadsize\\\":\\\"=MAP(remap,\\\\\\\"sum_outputpayloadsize\\\\\\\") |> SUM(%)\\\",\\\"sum_totalsize_mb\\\":\\\"=MAP(remap,\\\\\\\"sum_totalsize_mb\\\\\\\") |> SUM(%)\\\",\\\"sum_eventssize_mb\\\":\\\"=MAP(remap,\\\\\\\"sum_eventssize_mb\\\\\\\") |> SUM(%)\\\",\\\"sum_outputpayloadsize_mb\\\":\\\"=MAP(remap,\\\\\\\"sum_outputpayloadsize_mb\\\\\\\") |> SUM(%)\\\",\\\"sum_eventssize_gb\\\":\\\"=MAP(remap,\\\\\\\"sum_eventssize_gb\\\\\\\") |> SUM(%)\\\",\\\"sum_outputpayloadsize_gb\\\":\\\"=MAP(remap,\\\\\\\"sum_outputpayloadsize_gb\\\\\\\") |> SUM(%)\\\",\\\"sum_totalsize_gb\\\":\\\"=MAP(remap,\\\\\\\"sum_totalsize_gb\\\\\\\") |> SUM(%)\\\"}}\",\"position\":{\"x\":720,\"y\":510},\"type\":\"eventTransformation\",\"timeSavedUnit\":\"minutes\",\"timeSavedValue\":0,\"monitorAllEvents\":false,\"monitorFailures\":false,\"monitorNoEventsEmitted\":null,\"caseConfiguration\":{\"subStatus\":null},\"recordType\":null,\"recordWriters\":[],\"form\":null,\"createdFromTemplateGuid\":null,\"createdFromTemplateVersion\":null,\"originStoryIdentifier\":\"cloud:8a398de9ced29eb779cc34d883c19760:a339e89b8aa7a74c7ab8fb476ecd5456\"}],\"links\":[{\"sourceIdentifier\":\"1\",\"receiverIdentifier\":\"3\"},{\"sourceIdentifier\":\"0\",\"receiverIdentifier\":\"4\"},{\"sourceIdentifier\":\"4\",\"receiverIdentifier\":\"1\"},{\"sourceIdentifier\":\"5\",\"receiverIdentifier\":\"6\"},{\"sourceIdentifier\":\"6\",\"receiverIdentifier\":\"7\"},{\"sourceIdentifier\":\"7\",\"receiverIdentifier\":\"8\"},{\"sourceIdentifier\":\"8\",\"receiverIdentifier\":\"10\"},{\"sourceIdentifier\":\"10\",\"receiverIdentifier\":\"11\"},{\"sourceIdentifier\":\"11\",\"receiverIdentifier\":\"12\"},{\"sourceIdentifier\":\"12\",\"receiverIdentifier\":\"13\"},{\"sourceIdentifier\":\"13\",\"receiverIdentifier\":\"9\"},{\"sourceIdentifier\":\"2\",\"receiverIdentifier\":\"5\"},{\"sourceIdentifier\":\"3\",\"receiverIdentifier\":\"2\"}],\"diagramNotes\":[]}<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">as we are dealing with container_id&#8217;s we use Tines internal API&#8217;s to get metadata about what ID is what story<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"554\" src=\"https:\/\/cln.io\/blog\/wp-content\/uploads\/2024\/09\/image-9-1024x554.png\" alt=\"\" class=\"wp-image-1614\" srcset=\"https:\/\/cln.io\/blog\/wp-content\/uploads\/2024\/09\/image-9-1024x554.png 1024w, https:\/\/cln.io\/blog\/wp-content\/uploads\/2024\/09\/image-9-300x162.png 300w, https:\/\/cln.io\/blog\/wp-content\/uploads\/2024\/09\/image-9-768x415.png 768w, https:\/\/cln.io\/blog\/wp-content\/uploads\/2024\/09\/image-9-1536x830.png 1536w, https:\/\/cln.io\/blog\/wp-content\/uploads\/2024\/09\/image-9.png 1898w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">The other thing that we have to be mindful of is that groups (?) are split under the same container id, so we have to sum up the usage of the groups per container id.<br>(1) first we just calculate the sums per entry in the table<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"817\" src=\"https:\/\/cln.io\/blog\/wp-content\/uploads\/2024\/09\/image-3-1024x817.png\" alt=\"\" class=\"wp-image-1605\" srcset=\"https:\/\/cln.io\/blog\/wp-content\/uploads\/2024\/09\/image-3-1024x817.png 1024w, https:\/\/cln.io\/blog\/wp-content\/uploads\/2024\/09\/image-3-300x239.png 300w, https:\/\/cln.io\/blog\/wp-content\/uploads\/2024\/09\/image-3-768x613.png 768w, https:\/\/cln.io\/blog\/wp-content\/uploads\/2024\/09\/image-3-1536x1225.png 1536w, https:\/\/cln.io\/blog\/wp-content\/uploads\/2024\/09\/image-3.png 1838w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">we then implode all our calculated stuff per row.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">next we grab all the container ID&#8217;s that exist in the data<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"412\" src=\"https:\/\/cln.io\/blog\/wp-content\/uploads\/2024\/09\/image-4-1024x412.png\" alt=\"\" class=\"wp-image-1606\" srcset=\"https:\/\/cln.io\/blog\/wp-content\/uploads\/2024\/09\/image-4-1024x412.png 1024w, https:\/\/cln.io\/blog\/wp-content\/uploads\/2024\/09\/image-4-300x121.png 300w, https:\/\/cln.io\/blog\/wp-content\/uploads\/2024\/09\/image-4-768x309.png 768w, https:\/\/cln.io\/blog\/wp-content\/uploads\/2024\/09\/image-4.png 1396w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">While the approach may not be elegant, it&#8217;s effective. The key is in the iteration process. <br>We identify unique container IDs and then process them as follows:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>For each unique container ID, we sum up all the groups associated with it.<\/li>\n\n\n\n<li>Each container ID represents a distinct story.<\/li>\n\n\n\n<li>The groups within a container are combined to form the complete story.<\/li>\n<\/ol>\n\n\n\n<p class=\"wp-block-paragraph\">This results in the following structure:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Story 1: Combines data from Group 1, Group 2, and Group 3<\/li>\n\n\n\n<li>Story 2: Consists of a single group<\/li>\n\n\n\n<li>Story 3: Merges data from Group 1 and Group 2<\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"950\" height=\"586\" src=\"https:\/\/cln.io\/blog\/wp-content\/uploads\/2024\/09\/image-6.png\" alt=\"\" class=\"wp-image-1608\" srcset=\"https:\/\/cln.io\/blog\/wp-content\/uploads\/2024\/09\/image-6.png 950w, https:\/\/cln.io\/blog\/wp-content\/uploads\/2024\/09\/image-6-300x185.png 300w, https:\/\/cln.io\/blog\/wp-content\/uploads\/2024\/09\/image-6-768x474.png 768w\" sizes=\"auto, (max-width: 950px) 100vw, 950px\" \/><\/figure>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"651\" height=\"1024\" src=\"https:\/\/cln.io\/blog\/wp-content\/uploads\/2024\/09\/image-5-651x1024.png\" alt=\"\" class=\"wp-image-1607\" srcset=\"https:\/\/cln.io\/blog\/wp-content\/uploads\/2024\/09\/image-5-651x1024.png 651w, https:\/\/cln.io\/blog\/wp-content\/uploads\/2024\/09\/image-5-191x300.png 191w, https:\/\/cln.io\/blog\/wp-content\/uploads\/2024\/09\/image-5-768x1208.png 768w, https:\/\/cln.io\/blog\/wp-content\/uploads\/2024\/09\/image-5-976x1536.png 976w, https:\/\/cln.io\/blog\/wp-content\/uploads\/2024\/09\/image-5.png 1008w\" sizes=\"auto, (max-width: 651px) 100vw, 651px\" \/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\"><br>We then do a sum for the top entry of the table of all the data.<br>NOTE: do check if these numbers are aligned with your database sizes!<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"309\" src=\"https:\/\/cln.io\/blog\/wp-content\/uploads\/2024\/09\/image-7-1024x309.png\" alt=\"\" class=\"wp-image-1609\" srcset=\"https:\/\/cln.io\/blog\/wp-content\/uploads\/2024\/09\/image-7-1024x309.png 1024w, https:\/\/cln.io\/blog\/wp-content\/uploads\/2024\/09\/image-7-300x91.png 300w, https:\/\/cln.io\/blog\/wp-content\/uploads\/2024\/09\/image-7-768x232.png 768w, https:\/\/cln.io\/blog\/wp-content\/uploads\/2024\/09\/image-7-1536x464.png 1536w, https:\/\/cln.io\/blog\/wp-content\/uploads\/2024\/09\/image-7-2048x618.png 2048w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">then we just loop all our summarized data into markdown<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"339\" src=\"https:\/\/cln.io\/blog\/wp-content\/uploads\/2024\/09\/image-8-1024x339.png\" alt=\"\" class=\"wp-image-1610\" srcset=\"https:\/\/cln.io\/blog\/wp-content\/uploads\/2024\/09\/image-8-1024x339.png 1024w, https:\/\/cln.io\/blog\/wp-content\/uploads\/2024\/09\/image-8-300x99.png 300w, https:\/\/cln.io\/blog\/wp-content\/uploads\/2024\/09\/image-8-768x255.png 768w, https:\/\/cln.io\/blog\/wp-content\/uploads\/2024\/09\/image-8-1536x509.png 1536w, https:\/\/cln.io\/blog\/wp-content\/uploads\/2024\/09\/image-8.png 1792w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">and tadaa &#x1f389; you now have &#8220;monitoring&#8221; on how much each story is using in Tines DB size-wise.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img decoding=\"async\" src=\"https:\/\/cln.io\/blog\/wp-content\/uploads\/2024\/09\/growtika-ZfVyuV8l7WU-unsplash.jpg\" alt=\"\" class=\"wp-image-1618\"\/><figcaption class=\"wp-element-caption\">Photo by <a href=\"https:\/\/unsplash.com\/@growtika?utm_content=creditCopyText&amp;utm_medium=referral&amp;utm_source=unsplash\">Growtika<\/a> on <a href=\"https:\/\/unsplash.com\/photos\/a-group-of-blue-boxes-ZfVyuV8l7WU?utm_content=creditCopyText&amp;utm_medium=referral&amp;utm_source=unsplash\">Unsplash<\/a><\/figcaption><\/figure>\n","protected":false},"excerpt":{"rendered":"<p>The problem As much as I like Tines (deployed on-premise) their self-hosted metrics\/statistics could use some improvements, there is no 1-to-1 link or view between stories and their sizes in the database (events + payloads) [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":1648,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3,59],"tags":[],"class_list":["post-1590","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-automation","category-tines"],"_links":{"self":[{"href":"https:\/\/cln.io\/blog\/wp-json\/wp\/v2\/posts\/1590","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/cln.io\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/cln.io\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/cln.io\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/cln.io\/blog\/wp-json\/wp\/v2\/comments?post=1590"}],"version-history":[{"count":17,"href":"https:\/\/cln.io\/blog\/wp-json\/wp\/v2\/posts\/1590\/revisions"}],"predecessor-version":[{"id":1659,"href":"https:\/\/cln.io\/blog\/wp-json\/wp\/v2\/posts\/1590\/revisions\/1659"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/cln.io\/blog\/wp-json\/wp\/v2\/media\/1648"}],"wp:attachment":[{"href":"https:\/\/cln.io\/blog\/wp-json\/wp\/v2\/media?parent=1590"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/cln.io\/blog\/wp-json\/wp\/v2\/categories?post=1590"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/cln.io\/blog\/wp-json\/wp\/v2\/tags?post=1590"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}