Automatically finding objects/jobs that end with status "FAULT_OTHER"

[John Torres]

Is there a way to search a table or log/report for objects that end with a status "FAULT_OTHER"
(FAULT_OTHER - Start impossible. Other error.)
 
I would like to create an automatic process that checks, maybe every 5 mins, and send notification if  an object has ended with status "FAULT_OTHER ".

Thanks in advance.

 

[Pete Wirfs]

I don't have any objects with this state right now, so I can't confirm how to select them.  According to this documentation page, FAULT_OTHER is message code #1820;
https://docs.automic.com/documentation/webhelp/english/ALL/components/AE/11/All%20Guides/help.htm#ucaaiy.htm?Highlight=fault_other
I suspect this value may be stored in table AH as column AH_msgnr(?)

[Pete Wirfs]

Regarding automated methods for monitoring the system, we use a generic solution that checks the MELD(message) table every 30 minutes.  Our SQL query ignores all of the normal messages, so it returns only unexpected messages.  Those unexpected messages are then sent to staff via email.

This has been extremely beneficial for us.  We do not have staff watching the system around the clock.  This solution watches the system for us.

[Anon Anon]

here was a similar discussion:
https://community.automic.com/discussion/comment/24109#Comment_24109

as a start you can run a recurring script e.g.

:SET &FROMDATE# = SYS_DATE("YYYY-MM-DD")
:SET &TODATE# = SYS_DATE("YYYY-MM-DD")
:PRINT "FROM: &FROMDATE#   TO: &TODATE#"

:SET &HND#=PREP_PROCESS_VAR(VARA.SQLI.GET_1820)
:PROCESS &HND#
:   SET &NAME# = GET_PROCESS_LINE(&HND#,2)
:   SET &STATUS# = GET_PROCESS_LINE(&HND#,3)
:   SET &TIMESTAMP# = GET_PROCESS_LINE(&HND#,4)
:   PRINT "&NAME#  &STATUS#  &TIMESTAMP#"
:ENDPROCESS

with an SQLI VARA (T-SQL):

select ah_name, ah_status, ah_timestamp4 from ah
where ah_client = &$CLIENT#
and ah_status=1820
and ah_timestamp4 > '&FROMDATE# 00:00:00.000'
and ah_timestamp4 < '&TODATE# 23:59:59.000'

[Anon Anon]

Just as a further hint, I put the script into a CALL object like this just below the

:   PRINT "&NAME#  &STATUS#  &TIMESTAMP#"

:PUT_ATT_APPEND CALL_TEXT = "<tr><td>&NAME#</td><td>&STATUS#</td><td>&TIMESTAMP#</td></tr>"

and a bit HTML coding.

I start this on a daily (twice) basis so the admins get the info of faulty job starts.

[John Torres]

Wolfgang Brueckler said:

Just as a further hint, I put the script into a CALL object like this just below the

:   PRINT "&NAME#  &STATUS#  &TIMESTAMP#"

:PUT_ATT_APPEND CALL_TEXT = "<tr><td>&NAME#</td><td>&STATUS#</td><td>&TIMESTAMP#</td></tr>"

and a bit HTML coding.

I start this on a daily (twice) basis so the admins get the info of faulty job starts.

Wolfgang, I appreciate your help big time! I managed to run the select
and it gave me the results I need. I'm going to add the object type to
that list.

Thanks again!

[Anon Anon]

you are very welcome :-)

[John Torres]

Hi Wolfgang, this is a very helpful discussion and solution but I need your help.
I'm able to run the VARA.SQLI with hard coded values but I can't use variables. Would you know why? I can't figure this out.  Thanks ahead of time.

I can use :
select ah_name, ah_status, ah_timestamp4 from ah
where ah_client = 1090
and ah_status=1820
and ah_timestamp4 > '2017-05-01 00:00:00.000'
and ah_timestamp4 < '2017-06-25 23:59:59.000'

but I can't use:

select ah_name, ah_status, ah_timestamp4 from ah
where ah_client = &$CLIENT#
and ah_status=1820
and ah_timestamp4 > '&FROMDATE# 00:00:00.000'
and ah_timestamp4 < '&TODATE# 23:59:59.000'

 

[Pete Wirfs]

If you build your variable object as a "SEC_SQLI" instead of a "SQLI", then you will have the added option of passing BIND parameters.  Here is a really simple example I built when I was first learning how to use this type of variable object;
https://us.v-cdn.net/5019921/uploads/editor/c2/9c84jz9agab8.jpg" width="829">

[Anon Anon]

JohnTorres603474
What does "but I can't use variables." actually mean?

is your wife dishing out noogies if you use variables?
Or does appear an error message?
or does simply nothing appear?

Hmmm let me guess... I guess in your UC_SYSTEM_SETTINGS (Clt 0) the KEY VAR_SECURITY_LEVEL
hast to be adjusted...
...means != 0

https://docs.automic.com/documentation/WEBHELP/English/AWA/11.2/AE/11.2/All%20Guides/help.htm#ucabcf.htm#42

[John Torres]

I was able to get the SEC_SQLI working when I used one bind parameter (i.e. the &$CLIENT#) but as soon as I added two more (the dates), I got the following error. I even tried to use a system variable (e.g. &$PHYS_DATE_YYYY_MM_DD_d#) but got the same error. Am I coding it wrong?

https://us.v-cdn.net/5019921/uploads/editor/ge/u00i80lwxmzx.png" width="921">

[John Torres]

petwir Please read my prior post above. THANKS!

[Pete Wirfs]

When the questionmark(?) character is inside of quotes, it won't see it.  

That is why in my above example my quotes were in the "Bind parameters" data, instead of in the SQL statement.

[John Torres]

Pete Wirfs I now see it but I can't figure out how to substitute the date and time within one set of quotes (e.g. '2017-06-25 23:59:59.000'). I tried different combinations and nothing works. My snaphsot shows some of my tests and errors. Any ideas? and thanks again for taking out time to help me.

uploads/editor/sa/ha4vh08e6wyx.png" alt="" title="Image: https://us.v-cdn.net/5019921/uploads/editor/sa/ha4vh08e6wyx.png"/>  https://us.v-cdn.net/5019921/uploads/editor/lr/udl9mmuk8vt0.png" width="1010">

[John Torres]

petwir  my last at didn't insert your name correctly. please see my previous post above.

[Pete Wirfs]

I had problems with it seemingly forcing some sort of unexpected timestamp datatype conversion that just wasn't going to work.  I finally got it working by using the SQLServer function "convert()" to convert the timestamp into a varchar and then comparing text-to-text like so;
https://us.v-cdn.net/5019921/uploads/editor/uq/6092yticlaig.jpg" width="1007">

[Anon Anon]

I prefer using a SQLI VARA - here its possible to use script variables as well.

https://us.v-cdn.net/5019921/uploads/editor/zm/nzq5usjzdf0j.jpg" width="1363">

[Pete Wirfs]

John's post on June 28th said he couldn't get that method to work?  I wonder if he needs to change his variable inheritance setting or something like that?  I don't know what would be blocking it from working.  Seems I've used this method before too.

The reason I favor SEC with bind variables is out of habit.  In another life I used to do primarily SQL application work, and the DBAs always drilled it into me that using bind variables is more efficient in the database engine.  This is because if you run the same SQL statement 10 times with bind variables it will only need to be "compiled" by the database engine once, and it is stored in a cache for several hours for automatic re-use.  But if each of these 10 runs have different hard coded values, then it will have to compile each of the 10 SQL statements separately, and all 10 unique versions will wind up stored in cache and never result in any re-use.

In this day and age though, we are talking about saving milliseconds at run time.  So unless the SQL statement is going to be executed hundreds of times, I don't see it being anything to be concerned about.

(And now I'm wondering if UC4 SEC objects actually use database binding of the variables, or if UC4 is emulating this and passing a resolved unique string into the database, thus defeating the database performance gains?  Hummm...)

[John Torres]

FrankMuffkeIt's weird but the SQLI vara is now working. I didn't change anything, since Friday, but I did take a weekend break from Automic so something must have kicked in while I was away. 
Can you please show me a complete picture or copy/paste the entire code of your script. I'm interested in your HTML approach.
Thanks to you and petwir

[Anon Anon]

JohnTorres603474

Here you go...

just change recipient in the call object and if you do not use MS SQL Server change SQL Tab on the SQLI VARA.

have fun with it :-)

Attachment(s)

V112_chk_1820.zip   2 KB 1 version

[John Torres]

FrankMuffkeworks beautifully! I love this report. Thanks!

[Anon Anon]

You re welcome :-)

great that it works this easy cheesy :-)

[Laura Albrecht]

Hi.  So I'm reading through this thread and I'm just wondering if when you guys run this script - say every 30 minutes - do you just have a single team / person that you send the messages to?  And does it potentially report the SAME messages over and over?

For example, let's say there's a FAULT_OTHER error at 10:17am.  The script runs at 10:30am and obviously picks up the error and sends out an alert / email or whatever.  But what happens at 11, 11:30, 12, etc.?  Does it always pick up that same error?  It seemed like the query was for a 24 hour period, so I kind of feel like if there is a single FAULT_OTHER error - you're going to get an alert every 30 minutes for the rest of that day.  Can that be right?

The dilemma I have is that we do not have a single operations team who handles all the alerts.  Each team handles their own.  I think I can figure out how to identify which errors belong to which team by the object name, but again, I'm not sure how I would keep track of which errors I've already sent and new ones.  The teams are not going to want the same errors over and over.  I was thinking I could possibly use variables and run ID's to keep track, but this seems more complex than I was hoping for.

All suggestions welcome.  Thanks.

------------------------------
Enterprise Scheduling Lead
Takeda
------------------------------

[Pete Wirfs]

We send no duplicates.

Our 30 minute script maintains a state variable with the last MELD timestamp that triggered an alarm.  Then on following runs the query selects them again, but the script skips candidates that are older than the saved timestamp.   With this protection in place, we were able to ask the database query to return a large time-set of candidates, so if the script wasn't run in the last couple of hours, it would play catch-up.

We also have a system maintenance window process which can generate candidate errors (e.g. rebooting of servers with agents, as one example.)  So the workflow that we run at the end of this window also manipulates this MELD timestamp in our static variable to force the script to ignore messages that were generated during the maintenance window.

We do have a single operations team, so they get these alerts as phone text's. (we send text's via smtp email to the phone carrier.)

------------------------------
Pete (AE V11.2)
------------------------------

[Juergen Lechner]

Hi,

I think that this depends on the system criticallity. Normally the recipients are stored in a VARA. Should the application require special attention the subsequent emails might go to differnt (or more) recipients. This needs to be defined in a requirement.

In general it is also depending on the requirements how "older" errors are handled. I think the longer an error exists, the more it might hurt. So the best way of dealing with them is to compile a list of all errors and to mark the new ones. Errors need to be visible until they do not exist anymore.

Regards,
Juergen

------------------------------
Senior Consultant
setis GmbH
------------------------------