Thank you for the great new Feature in Zammad 6.2 to connect external Databases. Unfortunately my inventory software (rxInventory) don’t support API calls, but with Node-Red it’s easy to go around that problem and connect any Database that is supported by Node-Red to Zammad. Als example I want to explain how I display printers in the ticket mask.
What you need is Node-Red with the following package for MS SQL in this example, but there are nodes for almost any database available:
node-red-contrib-mssql-plus
Then you need just 4 nodes in Node-Red to get it running:
If your node-red is installed on nodered.yourcompany.com, you can call it under https://nodered.yourcompany.com:1880/zammad?q=[search]
In Zammad it must be configured like here:
Here is the Code für those 4 nodes. You can just copy it and paste it in Node-Red.
[{
"id": "9466a2bfc59c27c3",
"type": "MSSQL",
"z": "88aa4726e2859a6c",
"mssqlCN": "ffa2d5ca.bdc378",
"name": "Printers",
"outField": "payload",
"returnType": 0,
"throwErrors": 1,
"query": "SELECT \r\n [Name]\r\n ,[IP]\r\n ,[Manufacturer]\r\n ,[Modell]\r\n ,[Department]\r\n ,[Employee]\r\n ,[Serial]\r\n ,Name+' '+Manufacturer+' '+Modell+' '+Department as Label\r\nFROM [rxInventory].[dbo].[Drucker]\r\nWHERE (\r\n Name like '%{{{payload.q}}}%' or\r\n Manufacturer like '%{{{payload.q}}}%' or\r\n Modell like '%{{{payload.q}}}%' or\r\n Department like '%{{{payload.q}}}%'\r\n )",
"modeOpt": "queryMode",
"modeOptType": "query",
"queryOpt": "payload",
"queryOptType": "editor",
"paramsOpt": "queryParams",
"paramsOptType": "none",
"rows": "rows",
"rowsType": "msg",
"params": [],
"x": 320,
"y": 3700,
"wires": [["962ee696a237fd70"]]
}, {
"id": "962ee696a237fd70",
"type": "json",
"z": "88aa4726e2859a6c",
"name": "",
"property": "payload",
"action": "str",
"pretty": true,
"x": 470,
"y": 3700,
"wires": [["2aff9b24bcc623b6"]]
}, {
"id": "944631c963b09e00",
"type": "http in",
"z": "88aa4726e2859a6c",
"name": "",
"url": "/zammad",
"method": "get",
"upload": false,
"swaggerDoc": "",
"x": 150,
"y": 3700,
"wires": [["9466a2bfc59c27c3"]]
}, {
"id": "2aff9b24bcc623b6",
"type": "http response",
"z": "88aa4726e2859a6c",
"name": "",
"statusCode": "",
"headers": {},
"x": 610,
"y": 3700,
"wires": []
}, {
"id": "ffa2d5ca.bdc378",
"type": "MSSQL-CN",
"tdsVersion": "7_4",
"name": "rxInventory",
"server": "DB\\DB",
"port": "1433",
"encyption": true,
"trustServerCertificate": true,
"database": "rxInventory",
"useUTC": false,
"connectTimeout": "15000",
"requestTimeout": "15000",
"cancelTimeout": "5000",
"pool": "5",
"parseJSON": true,
"enableArithAbort": true
}
]