Highlighted
KG-HP Trusted Contributor.
Trusted Contributor.
1334 views

Creating a script to query an external database

Jump to solution

Hello,

We have an enviroment with Sitescope integrated with APM. I need to automate an SQL query which will check if the events in an external database are the most recent (dateTime) or if there is a connectivity issue.

I have done shell scripting in the past with OMU's schedule monitor/template to run custom scripts. But I'm new with Sitescope & Windows environment.

How should I go about addressing this?

Labels (2)
0 Likes
1 Solution

Accepted Solutions
KG-HP Trusted Contributor.
Trusted Contributor.

Re: Creating a script to query an external database

Jump to solution

Hey Siggi. The solution worked like a charm after the restart. Thank you for your help on this issue:)

Cheers!

KG

0 Likes
12 Replies
Micro Focus Expert
Micro Focus Expert

Re: Creating a script to query an external database

Jump to solution

Hi KG-HP,

you can use the Database Query monitor to query the external database and create an alerting condition depending on your needs.

Greetings
Siggi

Customer Support
Micro Focus

If you find that this or any post resolves your issue, please be sure to mark it as an accepted solution.
0 Likes
KG-HP Trusted Contributor.
Trusted Contributor.

Re: Creating a script to query an external database

Jump to solution

Thanks Siggi,

I will try creating a database query monitor and update the forum.

Cheers,

KG

0 Likes
KG-HP Trusted Contributor.
Trusted Contributor.

Re: Creating a script to query an external database

Jump to solution

Hi Siggi,

I created a SQL query with a WITH clause querying the database table for all time entries for CIs older than 60 mins compared to present dateTime. Will a DB query monitor suffice to give me a list of servers whose time difference is > 60 mins?

Would be great if I could get a direction in which to create this monitor to get the list of defaulting servers.

 

Regards,

KG

0 Likes
Micro Focus Expert
Micro Focus Expert

Re: Creating a script to query an external database

Jump to solution

Hi KG,

you can easily test your query via SiteScope -> Tools -> Database Tools -> Database Connection Tool
and proceed from there.

This
> querying the database table for all time entries for CIs older than 60 mins compared to present dateTime
might easily work, however I don't know if the WITH clause or temporary entities can be used there,
simply give it a try.

Greetings
Siggi

Customer Support
Micro Focus

If you find that this or any post resolves your issue, please be sure to mark it as an accepted solution.
0 Likes
KG-HP Trusted Contributor.
Trusted Contributor.

Re: Creating a script to query an external database

Jump to solution

Thanks Siggi. I tried the query in the db tool and it worked with the WITH clause as well.

Now, the output of the query is atleast >1 row. While setting up an alert, it limits the text characters in the email. I tried all the email templates in sitescope to see if any provides the full output of the query. But I get '. . .' after 14 entries.
Is there a way to get the full output of the query as an alert?

Thanks!

KG 

0 Likes
Micro Focus Expert
Micro Focus Expert

Re: Creating a script to query an external database

Jump to solution

Hi KG,

thanks for the update and the good news.

Regarding your follow-up question,
can you please send me an example email so that I can see what exactly is written, and where it gets cut?

Thanks
Siggi

Customer Support
Micro Focus

If you find that this or any post resolves your issue, please be sure to mark it as an accepted solution.
0 Likes
KG-HP Trusted Contributor.
Trusted Contributor.

Re: Creating a script to query an external database

Jump to solution

Hey Siggi,

 

Below is a sample email alert for the query output. Notice the '. . .' in the result, I need to get the full output in the alert.

-------------------------------------------

This alert is from SiteScope at http://********/SiteScope

Monitor: 1224586907:DB CPU check
Tags:
Group: BMC DB Test
Status: 94.069 sec, 15 rows, a02353, 1436059440, 2015-07-04 20:24:00.0, 2017-01-17 10:43:31.787, 810139, CPUCpuUtil, 2.41215
a03750, 1464792840, 2016-06-01 09:54:00.0, 2017-01-17 10:43:31.787, 331249, CPUCpuUtil, 10.6915
a03761, ...
Sample #: 23

Time: 10:45 AM 17/01/17

For documentation about alerts, go to:
http://*********/SiteScope/sisdocs/doc_lib/index.htm?single=false&context=system_avail&topic=config_sis_alert

Query Results:

a02353, 1436059440, 2015-07-04 20:24:00.0, 2017-01-17 10:43:31.787, 810139, CPUCpuUtil, 2.41215
a03750, 1464792840, 2016-06-01 09:54:00.0, 2017-01-17 10:43:31.787, 331249, CPUCpuUtil, 10.6915
a03761, ...

------------------------------------------------------------------

Cheers!

KG

 

0 Likes
Micro Focus Expert
Micro Focus Expert

Re: Creating a script to query an external database

Jump to solution

Hi KG,

thanks for providing the sample output, this helped a lot!

The only property which might be able to influence this behaviour is
SiteScope -> Preferences -> Infrastructure Preferences
 Monitor Settings
  DB maximum value length (default : 200)

This setting is used to limit the output as shown:
..
 if (resultBuffer.length() > DB_MAX_SUMMARY) {
    resultBuffer.setLength(DB_MAX_SUMMARY);
    resultBuffer.append("...");
..

Simply increase the setting to for example 400 and give it a try.

Greetings
Siggi

Customer Support
Micro Focus

If you find that this or any post resolves your issue, please be sure to mark it as an accepted solution.
KG-HP Trusted Contributor.
Trusted Contributor.

Re: Creating a script to query an external database

Jump to solution

Thanks Siggi.

I did change the DB max value length to 600, but do not see any change in the email alert.

-------------------------------------------------------
Monitor: SiteScope\BMC DB Check\CPU check
Link to the Monitor: http://******/SiteScope/servlet/Main?activeid=1224587090&activerighttop=dashboard&view=new&dashboard_view=Details&dashboard_model=true
Status: 18.143 sec, 15 rows, a02353, 2015-07-04 20:24:00.0
a03750, 2016-06-01 09:54:00.0
a03761, 2016-06-01 09:29:00.0
sv63243, 2017-01-09 14:39:00.0
sv63244, 2017-01-09 14:39:00.0
sv63245, 2017-01-09 14:39:00.0
sv63531, 2016-04-...
Last Monitor Run: 5:59 AM 19/01/17

Thanks,

KG

0 Likes
Micro Focus Expert
Micro Focus Expert

Re: Creating a script to query an external database

Jump to solution

Hi KG,

did you restart SiteScope after the change of the parameter?

Greetings
Siggi

Customer Support
Micro Focus

If you find that this or any post resolves your issue, please be sure to mark it as an accepted solution.
0 Likes
KG-HP Trusted Contributor.
Trusted Contributor.

Re: Creating a script to query an external database

Jump to solution

Hey Siggi. The solution worked like a charm after the restart. Thank you for your help on this issue:)

Cheers!

KG

0 Likes
Micro Focus Expert
Micro Focus Expert

Re: Creating a script to query an external database

Jump to solution

Cool, thanks for the confirmation

Siggi

Customer Support
Micro Focus

If you find that this or any post resolves your issue, please be sure to mark it as an accepted solution.
0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.