{
  "id": "phLhFkIrLiPbB9qa",
  "meta": {
    "instanceId": "f5498a3694b22d168149cbccf75115a48b85efb0858a1c814a63a2bf03cf6cd1",
    "templateCredsSetupCompleted": true
  },
  "name": "SolarWinds Incident Collector - Last 100 Tickets",
  "tags": [],
  "nodes": [
    {
      "id": "df9d3719-e892-43c6-95fa-ef5082132192",
      "name": "Schedule Every Hour",
      "type": "n8n-nodes-base.scheduleTrigger",
      "notes": "Runs automatically every hour",
      "position": [
        9776,
        1760
      ],
      "parameters": {
        "rule": {
          "interval": [
            {
              "field": "hours"
            }
          ]
        }
      },
      "typeVersion": 1.1
    },
    {
      "id": "61f7691f-952d-4a8d-bafa-5a42fa0920ee",
      "name": "Manual Trigger",
      "type": "n8n-nodes-base.manualTrigger",
      "notes": "Click 'Execute Workflow' to run on-demand",
      "position": [
        9776,
        1952
      ],
      "parameters": {},
      "typeVersion": 1
    },
    {
      "id": "e9bd67a2-b704-46c0-a504-f05dd2c7f9da",
      "name": "Config - Base URL",
      "type": "n8n-nodes-base.code",
      "notes": "SolarWinds Service Desk API configuration",
      "position": [
        10000,
        1760
      ],
      "parameters": {
        "jsCode": "// SolarWinds Service Desk API base URL\nconst BASE_URL = 'https://api.samanage.com';\n\nreturn [{\n  json: {\n    incidents_url: `${BASE_URL}/incidents.json`\n  }\n}];"
      },
      "typeVersion": 2
    },
    {
      "id": "0a162b61-0750-42a7-a903-4d0e6c1ef0d6",
      "name": "Get Incidents Page 1",
      "type": "n8n-nodes-base.httpRequest",
      "notes": "Fetch first 100 incidents",
      "position": [
        10224,
        1760
      ],
      "parameters": {
        "url": "={{ $json.incidents_url }}?per_page=100&page=1",
        "options": {
          "timeout": 60000
        },
        "sendHeaders": true,
        "authentication": "genericCredentialType",
        "genericAuthType": "httpHeaderAuth",
        "headerParameters": {
          "parameters": [
            {
              "name": "Accept",
              "value": "application/vnd.samanage.v2.1+json"
            }
          ]
        }
      },
      "credentials": {
        "httpHeaderAuth": {
          "id": "hKX96Jn4RH7DVcpg",
          "name": "Solarwinds Service Desk"
        }
      },
      "typeVersion": 4.1
    },
    {
      "id": "a6c34627-0136-4a4a-a05b-213b930bfc78",
      "name": "Truncate Tables",
      "type": "n8n-nodes-base.microsoftSql",
      "position": [
        10448,
        1760
      ],
      "parameters": {
        "query": "TRUNCATE TABLE solarwinds_incidents;\nTRUNCATE TABLE solarwinds_summary;",
        "operation": "executeQuery"
      },
      "credentials": {
        "microsoftSql": {
          "id": "cQ8zhPt5LM1BWGFY",
          "name": "Microsoft SQL account 2"
        }
      },
      "typeVersion": 1.1
    },
    {
      "id": "9fb6774b-57db-43a1-bd4f-f5ada499db93",
      "name": "Build Incidents Insert",
      "type": "n8n-nodes-base.code",
      "position": [
        10672,
        1760
      ],
      "parameters": {
        "jsCode": "// FIXED - Build SQL INSERT handling n8n's item-by-item response\nconst timestamp = new Date().toISOString();\n\nfunction escapeSql(str) {\n  if (str === null || str === undefined) return 'NULL';\n  return \"'\" + String(str).replace(/'/g, \"''\").replace(/[\\r\\n]+/g, ' ').substring(0, 4000) + \"'\";\n}\n\n// n8n returns each API item separately - use .all() to get ALL items\nconst allItems = $('Get Incidents Page 1').all();\n\nconsole.log('\\n=== SOLARWINDS INCIDENTS PROCESSING ===');\nconsole.log(`Total items from n8n: ${allItems.length}`);\n\nif (allItems.length === 0) {\n  console.log('❌ No items returned from Get Incidents Page 1');\n  return [{json: {query: 'SELECT 1;', count: 0, message: 'No incidents'}}];\n}\n\n// Extract the json from each item\nconst incidents = allItems.map(item => item.json);\n\nconsole.log(`\\n✅ Extracted ${incidents.length} incidents`);\nconsole.log('\\n=== FIRST INCIDENT SAMPLE ===');\nconsole.log(JSON.stringify(incidents[0], null, 2).substring(0, 500));\n\nconst insertValues = [];\n\nfor (const incident of incidents) {\n  const incidentId = escapeSql(incident.id || '');\n  const incidentNumber = escapeSql(incident.number || '');\n  const title = escapeSql(incident.name || 'No Title');\n  const description = escapeSql(incident.description_no_html || incident.description || '');\n  \n  // State mapping\n  const rawState = (incident.state || 'Open').toLowerCase();\n  let state = 'Open';\n  if (rawState === 'resolved' || rawState === 'closed') state = 'Resolved';\n  else if (rawState === 'in progress' || rawState === 'assigned' || rawState === 'on hold') state = 'In Progress';\n  const stateEscaped = escapeSql(state);\n  \n  // Priority handling\n  let priorityName = 'Medium';\n  if (incident.priority) {\n    if (typeof incident.priority === 'object' && incident.priority.name) {\n      priorityName = incident.priority.name;\n    } else if (typeof incident.priority === 'string') {\n      priorityName = incident.priority;\n    }\n  }\n  const priority = escapeSql(priorityName);\n  \n  // Category handling\n  let categoryName = 'General';\n  if (incident.category) {\n    if (typeof incident.category === 'object' && incident.category.name) {\n      categoryName = incident.category.name;\n    } else if (typeof incident.category === 'string') {\n      categoryName = incident.category;\n    }\n  }\n  const category = escapeSql(categoryName);\n  \n  // Subcategory handling\n  let subcategoryName = '';\n  if (incident.subcategory) {\n    if (typeof incident.subcategory === 'object' && incident.subcategory.name) {\n      subcategoryName = incident.subcategory.name;\n    } else if (typeof incident.subcategory === 'string') {\n      subcategoryName = incident.subcategory;\n    }\n  }\n  const subcategory = escapeSql(subcategoryName);\n  \n  // Assignee\n  const assignee = escapeSql(incident.assignee?.name || '');\n  \n  // Requester\n  const requester = escapeSql(incident.requester?.name || 'Unknown');\n  \n  // Department\n  const department = escapeSql(incident.department?.name || '');\n  \n  // Timestamps\n  const createdAt = escapeSql(incident.created_at || timestamp);\n  const updatedAt = escapeSql(incident.updated_at || timestamp);\n  const dueAt = incident.due_at ? escapeSql(incident.due_at) : 'NULL';\n  \n  // Resolved info\n  const isResolved = (incident.resolved_by && incident.resolved_by.name) ? 1 : 0;\n  const resolvedBy = (incident.resolved_by?.name) ? escapeSql(incident.resolved_by.name) : 'NULL';\n  \n  // Portal URL\n  let portalUrl = '';\n  if (incident.id) {\n    portalUrl = `https://api.samanage.com/incidents/${incident.id}`;\n  } else if (incident.href_account_domain) {\n    portalUrl = incident.href_account_domain;\n  }\n  const portalUrlEscaped = escapeSql(portalUrl);\n  \n  insertValues.push(\n    `(${incidentId}, ${incidentNumber}, ${title}, ${description}, ${stateEscaped}, ${priority}, ${category}, ${subcategory}, ${assignee}, ${requester}, ${department}, ${createdAt}, ${updatedAt}, ${dueAt}, ${isResolved}, ${resolvedBy}, ${portalUrlEscaped}, ${escapeSql(timestamp)})`\n  );\n}\n\nconsole.log(`\\n✅ Prepared ${insertValues.length} incidents for SQL insertion`);\n\nif (insertValues.length === 0) {\n  return [{json: {query: 'SELECT 1;', count: 0}}];\n}\n\n// Batch the inserts (SQL Server handles ~1000 rows per INSERT)\nconst batchSize = 500;\nconst batches = [];\nfor (let i = 0; i < insertValues.length; i += batchSize) {\n  batches.push(insertValues.slice(i, i + batchSize));\n}\n\nconst queries = batches.map(batch => {\n  return `INSERT INTO solarwinds_incidents (incident_id, incident_number, title, description, state, priority, category, subcategory, assignee, requester, department, created_at, updated_at, due_at, is_resolved, resolved_by, portal_url, last_updated) VALUES\\n${batch.join(',\\n')};`;\n});\n\nconst fullQuery = queries.join('\\n\\n');\n\nconsole.log(`Inserting ${insertValues.length} incidents in ${batches.length} batch(es)`);\nconsole.log('\\n=== SQL QUERY PREVIEW (first 300 chars) ===');\nconsole.log(fullQuery.substring(0, 300) + '...');\n\nreturn [{json: {\n  query: fullQuery,\n  count: insertValues.length,\n  batchCount: batches.length\n}}];"
      },
      "typeVersion": 2
    },
    {
      "id": "c5133e6f-b947-406c-b4a7-8cecd342ea1e",
      "name": "Insert Incidents",
      "type": "n8n-nodes-base.microsoftSql",
      "position": [
        10896,
        1760
      ],
      "parameters": {
        "query": "={{ $json.query }}",
        "operation": "executeQuery"
      },
      "credentials": {
        "microsoftSql": {
          "id": "cQ8zhPt5LM1BWGFY",
          "name": "Microsoft SQL account 2"
        }
      },
      "typeVersion": 1.1
    },
    {
      "id": "d78b947d-33d6-4e34-a5b0-9011e3e26841",
      "name": "Build Summary Insert",
      "type": "n8n-nodes-base.code",
      "position": [
        11120,
        1760
      ],
      "parameters": {
        "jsCode": "// Calculate summary statistics - FIXED to use .all()\nconst allItems = $('Get Incidents Page 1').all();\nconst incidents = allItems.map(item => item.json);\nconst timestamp = new Date().toISOString();\n\nfunction escapeSql(str) {\n  if (str === null || str === undefined) return 'NULL';\n  return \"'\" + String(str).replace(/'/g, \"''\") + \"'\";\n}\n\nconsole.log(`\\n=== CALCULATING SUMMARY ===`);\nconsole.log(`Total incidents: ${incidents.length}`);\n\nlet totalIncidents = 0;\nlet openIncidents = 0;\nlet inProgressIncidents = 0;\nlet resolvedIncidents = 0;\n\nlet criticalPriority = 0;\nlet highPriority = 0;\nlet mediumPriority = 0;\nlet lowPriority = 0;\n\nfor (const incident of incidents) {\n  totalIncidents++;\n  \n  // State counting\n  const rawState = (incident.state || 'Open').toLowerCase();\n  if (rawState === 'resolved' || rawState === 'closed') {\n    resolvedIncidents++;\n  } else if (rawState === 'in progress' || rawState === 'assigned' || rawState === 'on hold') {\n    inProgressIncidents++;\n  } else {\n    openIncidents++;\n  }\n  \n  // Priority counting\n  let priorityName = 'Medium';\n  if (incident.priority) {\n    if (typeof incident.priority === 'object' && incident.priority.name) {\n      priorityName = incident.priority.name;\n    } else if (typeof incident.priority === 'string') {\n      priorityName = incident.priority;\n    }\n  }\n  \n  const priority = priorityName.toLowerCase();\n  if (priority.includes('critical')) criticalPriority++;\n  else if (priority.includes('high')) highPriority++;\n  else if (priority.includes('medium')) mediumPriority++;\n  else if (priority.includes('low')) lowPriority++;\n}\n\nconst portalUrl = 'https://api.samanage.com';\n\nconst insertQuery = `INSERT INTO solarwinds_summary (total_incidents, open_incidents, in_progress_incidents, resolved_incidents, critical_priority, high_priority, medium_priority, low_priority, portal_url, last_updated) VALUES (${totalIncidents}, ${openIncidents}, ${inProgressIncidents}, ${resolvedIncidents}, ${criticalPriority}, ${highPriority}, ${mediumPriority}, ${lowPriority}, ${escapeSql(portalUrl)}, ${escapeSql(timestamp)});`;\n\nconsole.log(`\\nSummary Statistics:`);\nconsole.log(`  Total: ${totalIncidents}`);\nconsole.log(`  Open: ${openIncidents}`);\nconsole.log(`  In Progress: ${inProgressIncidents}`);\nconsole.log(`  Resolved: ${resolvedIncidents}`);\nconsole.log(`  Priority - Critical: ${criticalPriority}, High: ${highPriority}, Medium: ${mediumPriority}, Low: ${lowPriority}`);\n\nreturn [{json: {query: insertQuery}}];"
      },
      "typeVersion": 2
    },
    {
      "id": "4db34a42-4799-4e35-8e79-fa579554e974",
      "name": "Insert Summary",
      "type": "n8n-nodes-base.microsoftSql",
      "position": [
        11344,
        1760
      ],
      "parameters": {
        "query": "={{ $json.query }}",
        "operation": "executeQuery"
      },
      "credentials": {
        "microsoftSql": {
          "id": "cQ8zhPt5LM1BWGFY",
          "name": "Microsoft SQL account 2"
        }
      },
      "typeVersion": 1.1
    },
    {
      "id": "0f99827d-9b12-4b92-9c8f-94ad0deb27a6",
      "name": "Webhook",
      "type": "n8n-nodes-base.webhook",
      "position": [
        9776,
        1568
      ],
      "webhookId": "53554e8e-62f4-4b23-9139-cf3a595f51a5",
      "parameters": {
        "path": "53554e8e-62f4-4b23-9139-cf3a595f51a5",
        "options": {}
      },
      "typeVersion": 2.1
    }
  ],
  "active": true,
  "pinData": {},
  "settings": {
    "callerPolicy": "workflowsFromSameOwner",
    "errorWorkflow": "2kE4fsL384DKsDZj",
    "timeSavedMode": "fixed",
    "availableInMCP": false,
    "executionOrder": "v1"
  },
  "versionId": "94eb8caa-4a26-4e24-ac0b-9ae4884814eb",
  "connections": {
    "Webhook": {
      "main": [
        [
          {
            "node": "Config - Base URL",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Manual Trigger": {
      "main": [
        [
          {
            "node": "Config - Base URL",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Truncate Tables": {
      "main": [
        [
          {
            "node": "Build Incidents Insert",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Insert Incidents": {
      "main": [
        [
          {
            "node": "Build Summary Insert",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Config - Base URL": {
      "main": [
        [
          {
            "node": "Get Incidents Page 1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Schedule Every Hour": {
      "main": [
        [
          {
            "node": "Config - Base URL",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Build Summary Insert": {
      "main": [
        [
          {
            "node": "Insert Summary",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Get Incidents Page 1": {
      "main": [
        [
          {
            "node": "Truncate Tables",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Build Incidents Insert": {
      "main": [
        [
          {
            "node": "Insert Incidents",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}