This gist contains files to support the extraction of IBM MQ object configurations, pushing them into a MySQL database, and then displaying some of the information in Grafana.
For a full explanation of this process, please read the article at https://marketaylor.synology.me/?p=532
| { | |
| "annotations": { | |
| "list": [ | |
| { | |
| "builtIn": 1, | |
| "datasource": "-- Grafana --", | |
| "enable": true, | |
| "hide": true, | |
| "iconColor": "rgba(0, 211, 255, 1)", | |
| "name": "Annotations & Alerts", | |
| "type": "dashboard" | |
| } | |
| ] | |
| }, | |
| "editable": true, | |
| "gnetId": null, | |
| "graphTooltip": 0, | |
| "id": 11, | |
| "iteration": 1569867230049, | |
| "links": [], | |
| "panels": [ | |
| { | |
| "columns": [], | |
| "datasource": "MySQL", | |
| "fontSize": "100%", | |
| "gridPos": { | |
| "h": 8, | |
| "w": 19, | |
| "x": 0, | |
| "y": 0 | |
| }, | |
| "id": 6, | |
| "links": [ | |
| { | |
| "title": "", | |
| "url": "" | |
| } | |
| ], | |
| "options": {}, | |
| "pageSize": null, | |
| "scroll": true, | |
| "showHeader": true, | |
| "sort": { | |
| "col": 2, | |
| "desc": false | |
| }, | |
| "styles": [ | |
| { | |
| "alias": "Default Share Option", | |
| "dateFormat": "YYYY-MM-DD HH:mm:ss", | |
| "link": false, | |
| "pattern": "Default Share Option", | |
| "type": "string" | |
| }, | |
| { | |
| "alias": "", | |
| "colorMode": null, | |
| "colors": [ | |
| "rgba(245, 54, 54, 0.9)", | |
| "rgba(237, 129, 40, 0.89)", | |
| "rgba(50, 172, 45, 0.97)" | |
| ], | |
| "decimals": 2, | |
| "pattern": "/.*/", | |
| "thresholds": [], | |
| "type": "number", | |
| "unit": "short" | |
| } | |
| ], | |
| "targets": [ | |
| { | |
| "format": "table", | |
| "group": [], | |
| "metricColumn": "none", | |
| "rawQuery": true, | |
| "rawSql": "SELECT doc->>\"$.queueManager\" as 'QMgr',\n doc->>\"$.a.queue\" as 'Queue', \n doc->>\"$.a.usage\" as 'Usage',\n concat(doc->>\"$.a.altdate\",\":\",doc->>\"$.a.alttime\") as \"Alter Time\"\nFROM MQCFG.QLOCAL\nWHERE doc->>\"$.a.queue\" NOT LIKE 'SYSTEM.%' AND (\n CASE WHEN $USAGE = 'ALL' THEN \n doc->>\"$.a.usage\" LIKE '%'\n ELSE\n doc->>\"$.a.usage\" = $USAGE\n END ) AND (\n CASE WHEN $QMGR = 'ALL' THEN \n doc->>\"$.queueManager\" LIKE '%'\n ELSE\n doc->>\"$.queueManager\" = $QMGR\n END )\nORDER by Queue;\n", | |
| "refId": "A", | |
| "select": [ | |
| [ | |
| { | |
| "params": [ | |
| "value" | |
| ], | |
| "type": "column" | |
| } | |
| ] | |
| ], | |
| "timeColumn": "time", | |
| "where": [ | |
| { | |
| "name": "$__timeFilter", | |
| "params": [], | |
| "type": "macro" | |
| } | |
| ] | |
| } | |
| ], | |
| "timeFrom": null, | |
| "timeShift": null, | |
| "title": "Local Queues", | |
| "transform": "table", | |
| "type": "table" | |
| } | |
| ], | |
| "refresh": "30s", | |
| "schemaVersion": 19, | |
| "style": "dark", | |
| "tags": [], | |
| "templating": { | |
| "list": [ | |
| { | |
| "allValue": "'ALL'", | |
| "current": { | |
| "text": "All", | |
| "value": "$__all" | |
| }, | |
| "datasource": "MySQL", | |
| "definition": "SELECT DISTINCT(doc->>\"$.queueManager\") from MQCFG.QMGR", | |
| "hide": 0, | |
| "includeAll": true, | |
| "label": null, | |
| "multi": false, | |
| "name": "QMGR", | |
| "options": [], | |
| "query": "SELECT DISTINCT(doc->>\"$.queueManager\") from MQCFG.QMGR", | |
| "refresh": 2, | |
| "regex": "", | |
| "skipUrlSync": false, | |
| "sort": 1, | |
| "tagValuesQuery": "", | |
| "tags": [], | |
| "tagsQuery": "", | |
| "type": "query", | |
| "useTags": false | |
| }, | |
| { | |
| "allValue": "'ALL'", | |
| "current": { | |
| "text": "XMITQ", | |
| "value": "XMITQ" | |
| }, | |
| "datasource": "MySQL", | |
| "definition": "SELECT DISTINCT(doc->>\"$.a.usage\") from MQCFG.QLOCAL", | |
| "hide": 0, | |
| "includeAll": true, | |
| "label": null, | |
| "multi": false, | |
| "name": "USAGE", | |
| "options": [], | |
| "query": "SELECT DISTINCT(doc->>\"$.a.usage\") from MQCFG.QLOCAL", | |
| "refresh": 2, | |
| "regex": "", | |
| "skipUrlSync": false, | |
| "sort": 1, | |
| "tagValuesQuery": "", | |
| "tags": [], | |
| "tagsQuery": "", | |
| "type": "query", | |
| "useTags": false | |
| } | |
| ] | |
| }, | |
| "time": { | |
| "from": "now-6h", | |
| "to": "now" | |
| }, | |
| "timepicker": { | |
| "refresh_intervals": [ | |
| "5s", | |
| "10s", | |
| "30s", | |
| "1m", | |
| "5m", | |
| "15m", | |
| "30m", | |
| "1h", | |
| "2h", | |
| "1d" | |
| ] | |
| }, | |
| "timezone": "", | |
| "title": "Configuration View", | |
| "uid": "Y0cdue5Zk", | |
| "version": 22 | |
| } |
| #!/bin/bash | |
| # Prereq packages: | |
| # mysql-community-server (for the database itself) | |
| # mqsql-shell (for mysqlsh utility interface) | |
| # jq (for JSON filtering) | |
| # Access to a local MySQL service | |
| schema="MQCFG" | |
| user="root" | |
| pw="Passw0rd!" | |
| export MYSQL_PWD="$pw" | |
| # Optionally create a new database so we don't have old efforts lying around | |
| if false | |
| then | |
| mysql -u $user --force << EOF | |
| DROP DATABASE $schema; | |
| CREATE DATABASE $schema; | |
| EOF | |
| fi | |
| # The same web server (whose URL is fixed in the mqscJ script) can handle both of these qmgrs | |
| for qm in QM1 QM2 | |
| do | |
| # Which objects do we want to store | |
| for obj in QMGR QLOCAL QMODEL QALIAS QREMOTE CHL | |
| do | |
| echo "Collecting $obj for queue manager $qm" | |
| # Delete old data for this queue manager | |
| # Add "-vv" flag for more info on the deletions. This will fail, of course, if | |
| # the database has only just been created and the tables do not yet exist. | |
| mysql -u $user -e "DELETE FROM $schema.$obj WHERE doc->\"$.queueManager\" = \"$qm\";" | |
| if [ $obj = "QMGR" ] | |
| then | |
| objectList="" | |
| else | |
| objectList="*" | |
| fi | |
| # Call the REST API and reformat the output | |
| # - Delete fields that don't need to be stored | |
| # - Add the queue manager name as a field | |
| # - Rename the ".parameters" response (object attributes) to the much shorter ".a" | |
| # Would need to insert some error checking here too for a real system. | |
| # | |
| # The mysqlsh program handles the JSON import but it's a bit verbose | |
| # so there's a bunch of grep regexps to remove most of the expected stdout, while | |
| # leaving unexpected responses that might have come from stderr. | |
| # | |
| # If the table does not exist, the import will create it with 2 columns: "_id", "doc". | |
| # The second column contains the JSON text. | |
| ./mqscJ $qm DISPLAY $obj "$objectList" |\ | |
| jq -r '.commandResponse[] | | |
| del(.completionCode) | | |
| del(.reasonCode) | | |
| { queueManager: "'$qm'" , a: .parameters} | |
| ' |\ | |
| mysqlsh $user@localhost/$schema --quiet-start=2 --import - $obj 2>&1 |\ | |
| egrep -v "^\.|^Processed|^$|^Importing from " | |
| done | |
| done |
| #!/bin/bash | |
| # Purpose: | |
| # Build and execute an MQ REST admin command. | |
| # This script uses positional parameters deliberately, to make it look a little more like a traditional MQSC command. | |
| # For commands like ALTER or CREATE, the <additional parms> field is read in pairs: an attribute and its value. | |
| # There will certainly be issues here if parameters try to include special characters like '"' or have multiple words such | |
| # as in a DESCR field. This script is not going to deal nicely with escaping them. But it's good enough for what I need. | |
| # Usage: | |
| # mqscJ <qmgr> <command> <object type> <object name> <additional parms> | |
| # For example | |
| # mqscJ QM1 DISPLAY qlocal "app*" monq statq | |
| # mqscJ QM1 ALTER QLOCAL APP.Q.1 MAXDEPTH 3000 MONQ HIGH | |
| # Credentials for accessing the web server | |
| user=mqguest | |
| pass=passw0rd | |
| # Parameter validation | |
| if [ -z "$1" ] | |
| then | |
| echo "Must provide a queue manager name" | |
| exit 1 | |
| fi | |
| if [ -z "$2" ] | |
| then | |
| echo "Must provide a command" | |
| exit 1 | |
| fi | |
| # Other parameters are optional | |
| qmgr="$1" | |
| command=`echo $2 | tr '[a-z]' '[A-Z]'` | |
| qualifier=$3 | |
| object=$4 | |
| if [ ! -z "$object" ] | |
| then | |
| shift 4 | |
| parms=$* | |
| else | |
| parms="$5" | |
| fi | |
| # Build the REST elements into a temporary file | |
| cmd=/tmp/mqsc.json.$$ | |
| rm -f $cmd | |
| cat << EOF > $cmd | |
| { | |
| "type" : "runCommandJSON" | |
| ,"command" : "$command" | |
| EOF | |
| if [ ! -z "$qualifier" ] | |
| then | |
| echo ",\"qualifier\" : \"$qualifier\"" >> $cmd | |
| fi | |
| # Add the object name if supplied | |
| if [ ! -z "$object" ] | |
| then | |
| echo ",\"name\" : \"$object\"" >> $cmd | |
| fi | |
| # Add the parameters that are required to be returned from a DISPLAY command | |
| if [ "$command" == "DISPLAY" ] | |
| then | |
| if [ -z "$parms" ] | |
| then | |
| echo ",\"responseParameters\" : [\"ALL\"]" >> $cmd | |
| else | |
| pm="" | |
| for p in $parms | |
| do | |
| if [ -z "$pm" ] | |
| then | |
| comma="" | |
| else | |
| comma="," | |
| fi | |
| pm="$pm $comma \"$p\"" | |
| done | |
| echo ",\"responseParameters\" : [$pm]" >> $cmd | |
| fi | |
| else | |
| if [ ! -z "$parms" ] | |
| then | |
| # Use awk to format the request parameters | |
| # This will not work if the value has multiple words (eg in a DESCR field) | |
| echo $parms | | |
| awk ' | |
| BEGIN { | |
| comma = "" | |
| print ",\"parameters\": {" | |
| } | |
| { | |
| for (i=1;i<=NF/2;i++) { | |
| attr=$(2*i-1) | |
| value=$(2*i) | |
| # Is the value numeric? | |
| if (match(value, "^[0-9]+$")) { | |
| printf ("%s \"%s\" : %s\n",comma,attr,value) | |
| } else { | |
| printf ("%s \"%s\" : \"%s\"\n",comma,attr,value) | |
| } | |
| comma="," | |
| } | |
| } | |
| END { | |
| print "}" | |
| }' >> $cmd | |
| fi | |
| fi | |
| echo "}" >> $cmd | |
| # Now run curl to execute the generated file | |
| curl -Ss -X POST \ | |
| --header 'ibm-mq-rest-csrf-token: random' \ | |
| --header 'Content-Type: application/json' \ | |
| --header 'Accept: application/json' \ | |
| -k "https://localhost:9443/ibmmq/rest/v1/admin/action/qmgr/$qmgr/mqsc" \ | |
| -d @$cmd \ | |
| -u $user:$pass | |
| rc=$? | |
| # Delete the temp file | |
| rm -f $cmd | |
| # Don't check the return code from the executed command - make the caller of this | |
| # script do that by looking at the .reasonCode, .completionCode values. The exit code | |
| # from the script is from curl itself. | |
| exit $rc |