Sitescope DB Query Monitor (126 Views)
Reply
Frequent Advisor
Amarnath_BAC
Posts: 74
Registered: ‎10-19-2010
Message 1 of 5 (126 Views)

Sitescope DB Query Monitor

Hi All,

 

 

Could any one share any oracle queries to be used in DB query monitors, which you have used in sitescope tested& working please. I am trying to build some query monitors.

 

 

Appreciate all your help!!!!

Please use plain text.
Trusted Contributor
Ken Strauss_2
Posts: 394
Registered: ‎01-21-2005
Message 2 of 5 (119 Views)

Re: Sitescope DB Query Monitor

The attached screenshot is of a working one from our testing area, names have been changed, and I copied the query up into the description field so you can see it.

 

 

Please use plain text.
Frequent Advisor
Amarnath_BAC
Posts: 74
Registered: ‎10-19-2010
Message 3 of 5 (115 Views)

Re: Sitescope DB Query Monitor

Hi,

 

Thanks for the information, I want to know what is the query used for.. could you please share some more queries and the benefit of that as well.

 

Also Please paste the query  in the reply or attach as text file,  Its difficult for me to see the query..

 

 

Appreciate your help!

 

Please use plain text.
Respected Contributor
dcsbeemer
Posts: 294
Registered: ‎07-19-2011
Message 4 of 5 (108 Views)

Re: Sitescope DB Query Monitor

Hi Amarnath_BAC

 

 

I don't know if this is best practice or not, but personally I try to only use SQL queries that either means if anything is returned, then it's bad or good (or alternatively, if nothing is returned then it's bad or good).

 

For an Oracle DB, it might be a query like this:

 

select return_code, OS_USERNAME, username, terminal, action_name, userhost, to_char(timestamp,'dd-mon hh24:mi') timestamp from sys.dba_audit_trail where timestamp > trunc(sysdate) and returncode <> 0 order by timestamp desc

 

 

All the query above does is return unsuccessful login attempts, with the latest row being shown in the Sitescope dashboard. You could change the SQL query further to only show unsuccessful login attempts for certain DB users.

 

In our environment, we have a DB counter monitor displaying a list of users and their status with the thresholds set to error if the status is anything but "OPEN", then have a DB Query monitor dependent on the DB counter monitor to return the latest unsuccessful login attempt and send an alert out.

It might sound a bit complicated, but it results in users knowing when certain critical DB accounts get locked out and why they were locked out.

Please use plain text.
Trusted Contributor
Ken Strauss_2
Posts: 394
Registered: ‎01-21-2005
Message 5 of 5 (88 Views)

Re: Sitescope DB Query Monitor

The majority of our DB Queries work like that as well.

The older versions of Sitescope only supported queries which returned row/line counts so we woudl work the the app owners and DBAs to craft queries which would result in either 0 rows or #rows and use that simple difference as our good/bad indicator.

 

Recently (ver 11.x I think) we've found that we can evaluate the text returned from these queries with the typical REGEX matching available in SiteScope monitors and it is working out quite well.

Please use plain text.
The opinions expressed above are the personal opinions of the authors, not of HP. By using this site, you accept the Terms of Use and Rules of Participation