Skip to content

Instantly share code, notes, and snippets.

@ibmmqmet

ibmmqmet/Configuration View-1569875137366.json

Last active November 28, 2019 20:51
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save ibmmqmet/76cbcfa80e153cd7dd62b1dfcb4632b7 to your computer and use it in GitHub Desktop.
Save ibmmqmet/76cbcfa80e153cd7dd62b1dfcb4632b7 to your computer and use it in GitHub Desktop.
Download ZIP
Storing MQ configuration in MySQL
Raw
README.md

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

Raw
Configuration View-1569875137366.json
{
"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
}
Raw
dmpmqsql
#!/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
Raw
mqscJ
#!/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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment