ServiceNow AIインシデント分析をPythonのStreamlitでWebアプリ化した手順まとめ

mkdir sn_ai_analyzer; cd sn_ai_analyzer
python -m venv venv
.\venv\Scripts\Activate.ps1
pip install streamlit requests openai cohere google-generativeai openpyxl pandas python-dotenv
pip freeze > requirements.txt

New-Item .env
```

中身をテキストエディタで開いて記述します。
```
SN_INSTANCE=https://your-instance.service-now.com
SN_USER=admin
SN_PASS=パスワード
OPENAI_API_KEY=sk-...
COHERE_API_KEY=...
GEMINI_API_KEY=...

New-Item servicenow.py

import requests
import pandas as pd
from requests.auth import HTTPBasicAuth

def fetch_incidents(instance, user, password, state=None, priority=None, limit=50):
    query_parts = []
    if state:    query_parts.append(f"state={state}")
    if priority: query_parts.append(f"priority={priority}")
    sysparm_query = "^".join(query_parts) if query_parts else ""

    url = f"{instance}/api/now/table/incident"
    params = {
        "sysparm_limit": limit,
        "sysparm_fields": "number,short_description,description,state,priority,assigned_to",
        "sysparm_query": sysparm_query,
    }
    try:
        resp = requests.get(url, auth=HTTPBasicAuth(user, password), params=params)
        resp.raise_for_status()
        return pd.DataFrame(resp.json()["result"])
    except requests.exceptions.HTTPError as e:
        raise Exception(f"APIエラー: {e}")
    except Exception as e:
        raise Exception(f"接続エラー: {e}")

query_parts = []　　
　　if state:    query_parts.append(f"state={state}")
    if priority: query_parts.append(f"priority={priority}")
    sysparm_query = "^".join(query_parts) if query_parts else ""

New-Item llm.py

import openai
import cohere
import google.generativeai as genai

def analyze(provider, prompt, incident_text, api_key):
    full_prompt = f"{prompt}\n\n---\n{incident_text}"

    if provider == "OpenAI":
        client = openai.OpenAI(api_key=api_key)
        r = client.chat.completions.create(
            model="gpt-5.4-mini",
            messages=[{"role": "user", "content": full_prompt}],
            max_completion_tokens=300,
        )
        return r.choices[0].message.content

    elif provider == "Cohere":
        client = cohere.ClientV2(api_key=api_key)
        r = client.chat(
            model="command-a-03-2025",
            messages=[{"role": "user", "content": full_prompt}],
        )
        return r.message.content[0].text

    elif provider == "Gemini":
        genai.configure(api_key=api_key)
        model = genai.GenerativeModel("gemini-3-flash-preview")
        return model.generate_content(full_prompt).text

New-Item app.py

import streamlit as st
import pandas as pd
from io import BytesIO
from dotenv import load_dotenv
import os
import openpyxl
from servicenow import fetch_incidents
from llm import analyze

load_dotenv()

st.set_page_config(page_title="SN AI Incident Analyzer", layout="wide")

with st.sidebar:
    st.header("接続設定")
    instance = st.text_input("Instance URL", os.getenv("SN_INSTANCE", ""))
    user     = st.text_input("Username",     os.getenv("SN_USER", ""))
    password = st.text_input("Password",     os.getenv("SN_PASS", ""), type="password")

    st.header("LLM選択")
    provider = st.selectbox("LLM", ["OpenAI", "Cohere", "Gemini"])
    api_key  = st.text_input("API Key", type="password")

    st.header("抽出条件")
    state    = st.selectbox("State", ["", "1", "2", "3"],
                 format_func=lambda x: {"": "All", "1": "Open", "2": "In Progress", "3": "On Hold"}.get(x, x))
    priority = st.selectbox("Priority", ["", "1", "2", "3", "4"])
    limit    = st.slider("取得件数", 5, 100, 20)

    run = st.button("分析実行", use_container_width=True)

prompt = st.text_area("プロンプト", "以下のインシデントを分析し、要約・推奨アクション・優先度を出力してください。", height=100)

if run:
    with st.spinner("ServiceNowからデータ取得中..."):
        df = fetch_incidents(instance, user, password, state or None, priority or None, limit)

    results = []
    for _, row in df.iterrows():
        text = f"番号: {row['number']}\n件名: {row['short_description']}\n詳細: {row.get('description', '')}"
        with st.spinner(f"{row['number']} を分析中..."):
            res = analyze(provider, prompt, text, api_key)
        results.append({"番号": row["number"], "件名": row["short_description"], "AI分析結果": res})

    result_df = pd.DataFrame(results)
    st.dataframe(result_df, use_container_width=True)

    buf = BytesIO()
    wb = openpyxl.Workbook()
    ws = wb.active
    ws.append(list(result_df.columns))
    for r in result_df.itertuples(index=False):
        ws.append(list(r))
    wb.save(buf)
    st.download_button("Excelダウンロード", buf.getvalue(), "incidents_ai.xlsx",
                       "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")

@echo off
cd /d C:\work\sn_ai_analyzer
call venv\Scripts\activate
streamlit run app.py

streamlit run app.py

以下のServiceNowインシデント情報を分析し、3点を簡潔に日本語で回答してください。
1. 要約（2文以内）
2. 推奨アクション（具体的に1〜2つ）
3. 対応優先度評価（High / Medium / Low）と理由

python -m venv venv
.\venv\Scripts\Activate.ps1
pip install -r requirements.txt

app.py
servicenow.py
llm.py
requirements.txt
start.bat
.env.example   ← .envのサンプルとして用意しておくと親切

SN_INSTANCE=https://your-instance.service-now.com
SN_USER=
SN_PASS=
OPENAI_API_KEY=
COHERE_API_KEY=
GEMINI_API_KEY=