Splunk DB2 / AS400 Integration

[jjarevalo]

Hi, 

I am new to splunk and currently doing a POC to create a POC to monitor a DB2 database and an AS400 running on IBM.

Does anyone have had the chance to work on the same setup?

Basically, the objective is to have proactive alerts or some kind of monitoring dashboard for DB2 database and if possible self healing mechanism through the report?

I would really appreciate if someone have some ideas to share.

[rnowitzki · Accepted solution]

Hi @jjarevalo ,

I created a "AS400 Monitoring App" for Splunk a while ago. Customer was more interested in the Jobs processed by the AS400 instead of the DB2 Performance/Availability, but maybe this helps you:

We have a script that sits on the AS400 that has access to the DB2. For your requirement, maybe this is enough:

qsh -c "db2 -S \"SELECT TOTAL_JOBS_IN_SYSTEM,ACTIVE_JOBS_IN_SYSTEM,AVERAGE_CPU_UTILIZATION FROM TABLE(QSYS2.SYSTEM_STATUS(RESET_STATISTICS=>'YES'))\"" | grep -v -e "----" -e "SELECTED" -e "^$"

(maybe you have to change the SELECT statement details and/or the grep stuff afterwards, based on your setup)

This would give you something like:

TOTAL_JOBS_IN_SYSTEM ACTIVE_JOBS_IN_SYSTEM AVERAGE_CPU_UTILIZATION
3630                 223                   2.81

This script is called by a scripted input in Splunk. Can be a simple sh/python, that just does:
ssh user@as400_host "/path/to/script"

The python version would be using paramiko e.g.

If you call this on a regular basis, you could already create a simple dashboard and alerting. 
You could alert on high CPU usage,  you could alert if there are no Jobs processed (Active Jobs = 0, or Active Job count does not change over a period of time) and of course you could alert if the script does not return any data. It MIGHT mean the DB2/AS400 has an issue (or maybe your input app 😉 )

Hope it helps to give you an idea
BR
Ralph

[jjarevalo]

Thank you so much! This is very helpful!

If I understand it correctly, I need to create a user account on AS400 which has access on the system and runs the script on background is that correct? Do I still need DB Connect of Splunk?

[rnowitzki]

Hi @jjarevalo,

You could also run the SQL via DB Connect, which would be maybe even better, because you don't need to ssh into the AS400 and you have control from Splunk over the Query (content and schedule).
In our case DB Connect was not an option, so I set it up as described.

The script could run as cronjob, piping the results to a file which is monitored by a UF. Or you call the script as a scripted input, which would ingest the stdout to Splunk.

So, 3 options:
- Call the SQL with DB Connect
- Run the script as cron, monitor result file with UF.
- Call the script as scripted input.

BR
Ralph

[jjarevalo]

Thank you so much! It would be my last question and would definitely close the topic as you have provided the solution 🙂 it means a lot!

Not really about splunk but would you know how could I create an instance of AS400 that I can play with either on prem or aws?

[rnowitzki]

Hi @jjarevalo ,

Don't you have test/integration systems within your company/customer?

Can't really help here. The AS400s that I worked with were provided by the customers and were running for decades already 🙂

BR
Ralph

[jjarevalo]

thank you. I just do a workaround for now in order me to establish the AS400 connection. On the script input, sorry for the newbie question, do I need to install splunk on the same machine as as400 in order to use inputed script? How about if my as400 and splunk are on different machines?

[rnowitzki]

In the setup I described earlier, there is nothing Splunk related on the AS400. The script sshs to the AS400 and executes the qsh command/script.  (ssh user@as400_host "/path/to/script")

In Production this might not be the best option.

[jjarevalo]

by the way, for the query is it only 1 row that is being returned? How about if I would like to have a view of all jobs and processes running?

[rnowitzki]

Hi @jjarevalo ,

This DB query lists the status of all Jobs.  

qsh -c "db2 -S \"SELECT JOB_NAME, AUTHORIZATION_NAME, SUBSYSTEM, SUBSYSTEM_LIBRARY_NAME, JOB_STATUS, JOB_TYPE,ELAPSED_CPU_TIME,ELAPSED_CPU_PERCENTAGE FROM TABLE (QSYS2.ACTIVE_JOB_INFO()) \"" 

You can combine the one I posted earlier with this one in one script. So you have a general status and details on the jobs as well.

BR

Ralph 

[jjarevalo]

was able to execute the query on AS400 and just need to connect it to Splunk. Yey!

Can I also do it like for when a listener is down and get an an early alert? Mostly the POC is for the high utilization on AS400 but we found out that when we listener is down our replication tool is causing some increased number of jobs on AS400.

[rnowitzki]

If there is an error that indicates this, you could trigger an alert when you find this error.
Or when you see an increased number of jobs. Either you set a hard limit, or you calculate the average count of jobs and alert when the current number is, let's say,  50% higher.