arm-lab: PowerShellでREST APIをたたいてCSV（xls, xlsxも追記）ファイルをServicenowのテーブルに更新インポートする

ServicenowとCSVファイルで連携用データを提供するシステムの連携をPowershellで確立するシリーズ。

①データ連携元からURL指定でダウンロード（ただしSNOWから直接接続不可でLAN上にしかURLが公開されていない）→こちらの記事

②ServicenowからPowershellでデータをエクスポート→こちらの記事

③①と②のデータをマージしたCSVファイルを作成→これについてはシステムが違うと全く違うロジックになると思うので記事化しない。

④Servicenowへ「③」のデータをPowershellでインポート→本記事

という流れで進めてきた最終回「④」の部分が今回になる。

下準備（インポートに使うテーブル一式の準備）

まずREST API経由でデータをインポートするにはSNOWのGUI側でインポートセットを準備しておく下準備が必要となる。

②の前回記事でダウンロードしたxlsファイルをCSVファイルで保存し、フィルタメニューでSystem Import Sets -> Load Dataでデータは空でもよいので、

Label：inport_incident_from_csv
File：incident.csv

とし、Submit。

次のようになったら、Create transform map。

一旦次のように設定し、Save。

Name：Inport Incident By CSV（適当でもOK）
Run business rules：False（今回は面倒なので・・・必要に応じてTrue）
Target Table：Incident（インポート先）

保存したらRelated Linksが出てくるので「Auto Map Matching Fields」

素のSNOWからエクスポートしたファイルを使用しているので今回はほとんどそのままでOK。（カスタマイズ入っていたり、そもそもファイルの列が違ったりする場合はまじめにマッピングする。）

このままインポートすると問答無用で新規レコードを作成する事になるので、「Create new record on empty coalesce fields」をチェックしTrueに。

加えてなぜかこの変換マップを使用したインポートではSNOW側のsysidがマッピング出来ない（csv側のSys ID列とマッピングしようとしても選択できない）ので、number（「INC000xxx」みたいなやつ）をsys_idの代わりにする必要がある。

画面下部のField Mapsのところでnumberを探し「Coalesce」をtrueに変更し、Updateで保存ておく。

これでnumberをSysidと同じように扱い、有効なnumberが入っている行は、データ更新、そうでなければ新規レコード作成、という更新インポートが可能になる。

Updateすると、恐らく↓の画面に飛ぶのでImport setsのリンクへ。

作られているImport setのリンクを開く

Import set tableのところへ（ここまで本記事と同じ値を入れていれば）「u_inport_incident_by_csv」と記載があると思う。これをメモしておく。

ちなみにフィルタメニューにu_inport_incident_by_csv.listとすると、次のように表示される。

さらにこの画面からRelated LinksのTransform Mapsをたどると、先ほど定義した変換マップとImport set tableが関連づいている事がわかる。

つまりこれでImport set tableに一定の形式のデータを投入すれば、定義した変換マップによって処理できる下準備が整ったことになる。

文字コード対応のための下準備

System Propertied -> Import Exportで、CSV FormatのImport Charsetを「utf-8」に設定しておく。※Excelで保存したCSVはutf-8となるため。

検証用データ準備

まず、更新対象のデータをこんな感じで作成。

incident.csvファイルをローカルで次の様に作成。

Active,Activity due,Additional assignee list,Approval,Approval history,Approval set,Assigned to,Assignment group,Business duration,Service,Business resolve time,Duration,Resolve time,Caller,Category,Caused by Change,Child Incidents,Resolution code,Resolution notes,Closed,Closed by,Configuration item,Additional comments,Comments and Work notes,Company,Contact type,Contract,Correlation display,Correlation ID,Delivery plan,Delivery task,Description,Due date,Escalation,Expected start,Follow up,Group list,On hold reason,Impact,Incident state,Knowledge,Location,Made SLA,Notify,Number,Opened,Opened by,Order,Parent,Parent Incident,Priority,Problem,Reassignment count,Reopen count,Last reopened by,Last reopened at,Resolved,Resolved by,Change Request,Route reason,Service offering,Severity,Short description,Skills,SLA due,State,Subcategory,Task type,Created by,Created,Domain,Domain Path,Sys ID,Updates,Tags,Updated by,Updated,Effective number,Time worked,Universal Request,Upon approval,Upon reject,Urgency,User input,Watch list,Actual end,Work notes,Work notes list,Actual start
,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,INC0010004,,,,,,,,,,,,,,,,,,"更新インポート用インシデント→ここに文字が入れば更新成功",,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,"numberフィールドの無い行は新規起票されれば成功！",,,,,,,,,,,,,,,,,,,,,,,,,,

これをPowershellからPostしてINC0010004が更新され、もう一つ新しいインシデントレコードが作成されれば、成功である。

Powershellのコード

最終的に成功したコードはこんな感じ。

※上の方のパラメータは当然ながら各自の環境に合わせて変更が必要。

動作確認

さて、Powershellから実行してみる。

更新は成功。

新規起票も成功。　

苦労した点

＜日本語の文字化け対策＞

今回英語データのインポートはほとんど「【参考URL　スクリプト関連】」のところのやり方でうまく行った。日本語の文字化けに悩まされた。

Servicenow側でもCSVファイルの文字コードをutf-8に指定する必要がある、というところと、Powershellの方も

$FileEnc = [System.Text.Encoding]::GetEncoding('UTF-8').GetString($FileBytes)

と指定しても日本語の文字化けが解消されず、ちょっと途方に暮れかけた。

結果としては、↓で問題が解消された。

$FileEnc = [System.Text.Encoding]::GetEncoding('ISO-8859-1').GetString($FileBytes)

ちなみにCSVファイル自体はBOM有りでも無しでも問題なさそうであった。

＜変換マップを使った更新インポート＞

coalesce fieldsを指定する事で変換マップでも更新インポートが可能。（指定しないと問答無用で全件新規レコードが作成される。）

＜変換マップの仕組み＞

ServiceNowの方は私自身が変換マップを利用したインポートの仕組みを理解していなかったため、多少調査が必要であった。

Import set tableと変換マップが紐づいていれば、Import set tableと変換マップの使いまわしで同じ形式のデータインポートが可能、というところはPowershellやAPI経由のインポートだけでなく、手動でのインポートでも今後役に立てられそうだ。

正直手元での実用に向けてはまだまだ、

・データ件数が大量（1万件以上）になった場合でもこのまま動くか？

・タスクマネージャーと組み合わせた全体としての動作確立及びエラー検出。

・データ変換ロジックの確立。

等々課題は多いが、基本的な技術要素については実現可能であることが実証できた。

追記～Excelファイルをインポートする場合のコード

後日、csvではなく直接.xsl/.xlsxのファイルをインポートしようとしたもののなかなか苦労した。Invoke-RestMethodのInFileオプションでファイルを指定すれば良いだろうと思っていたが、なぜかうまく読み込めず、リクエストの投げ方やBodyの書き方に一工夫必要だったため、残しておく。

arm-lab

quick link

2021年3月7日日曜日

PowerShellでREST APIをたたいてCSV（xls, xlsxも追記）ファイルをServicenowのテーブルに更新インポートする