09-26-2012 07:28 AM
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!!!!
09-26-2012 07:57 AM
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.
09-26-2012 08:13 AM
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!
09-27-2012 12:02 AM
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.
01-17-2013 11:06 AM
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.