Anonymous_User Absent Member.
Absent Member.
819 views

Migrating AutoAgeEvents Stored Procedure to AppManager 8


Hi,

Our AppManager 7 installation is running a stored procedure called
AutoAgeEvents, that sets open events to acknowledged, and acknowledged
events to closed, after X period of time. Our company's former
AppManager administrator implemented this stored procedure, so I'm not
familiar with the background of it. I'm hoping that someone reading this
is.

I tried to create this stored procedure in our newly installed
AppManager 8 environment, but it calls 2 Extended Stored Procedures
called "master..nq_now" and "master..nqp_sp_try". My "master" database
on my AppManager 8 instance has NO extended stored procedures installed.
AppManager 7 has a BUNCH!

Do I need to somehow install these Extended Stored Procedures or do I
need the AutoAgeEvents rewritten for AppManager 8?


--
marty45714
------------------------------------------------------------------------
marty45714's Profile: https://forums.netiq.com/member.php?userid=4004
View this thread: https://forums.netiq.com/showthread.php?t=46904

0 Likes
4 Replies
Anonymous_User Absent Member.
Absent Member.

Re: Migrating AutoAgeEvents Stored Procedure to AppManager 8


NetIQ AppManager 8 eliminated all the extended stored procedures we used
in previous versions. Unfortunately, your stored procedure will need to
be modified to eliminate references to the extended stored procedures
that are no longer available.

Thanks,
Kevin


--
haggardk
------------------------------------------------------------------------
haggardk's Profile: https://forums.netiq.com/member.php?userid=4343
View this thread: https://forums.netiq.com/showthread.php?t=46904

0 Likes
Anonymous_User Absent Member.
Absent Member.

Re: Migrating AutoAgeEvents Stored Procedure to AppManager 8


I was able to talk to the former AppManager admin here, Brett. He told
me he thought that Andy wrote this stored procedure. So I may need Andy
or Alain's help in getting this re-written for NetIQ 8.

haggardk;225920 Wrote:
> NetIQ AppManager 8 eliminated all the extended stored procedures we used
> in previous versions. Unfortunately, your stored procedure will need to
> be modified to eliminate references to the extended stored procedures
> that are no longer available.
>
> Thanks,
> Kevin



--
marty45714
------------------------------------------------------------------------
marty45714's Profile: https://forums.netiq.com/member.php?userid=4004
View this thread: https://forums.netiq.com/showthread.php?t=46904

0 Likes
Anonymous_User Absent Member.
Absent Member.

Re: Migrating AutoAgeEvents Stored Procedure to AppManager 8


Here's the current code:

USE [QDB]
GO

/****** Object: StoredProcedure [dbo].[AutoAgeEvents] Script Date:
02/21/2013 16:36:37 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[AutoAgeEvents] @Activity VARCHAR(255),
@NumberOfHours INT

AS

SET NOCOUNT ON

DECLARE @UTCTargetTime INT
DECLARE @UTCTimeNow INT
DECLARE @ParentEventID INT
DECLARE @EventID INT
DECLARE @StartTime INT
DECLARE @ProcessID INT

DECLARE @ActualStatus INT

DECLARE @EventStatus_Open INT
DECLARE @EventStatus_Ack INT
DECLARE @EventStatus_Closed INT
DECLARE @EventStatus_Deleted INT

SELECT @EventStatus_Open = 0x00000000
SELECT @EventStatus_Ack = 0x00000001
SELECT @EventStatus_Closed = 0x00000002
SELECT @EventStatus_Deleted = 0x80000000


EXECUTE master..nq_now @UTCTimeNow OUT
EXECUTE master..nqp_sp_try 'AutoAgeEvents', @ProcessID OUT, @StartTime
OUT


SELECT @UTCTargetTime = @UTCTimeNow - (@NumberOfHours * 60 * 60)

CREATE TABLE #ChildEvents (EventID INT)

IF (@Activity = 'OpenToAck') GOTO OpenToAck
IF (@Activity = 'AckToClosed') GOTO AckToClosed
IF (@Activity = 'ClosedToDeleted') GOTO ClosedToDeleted

GOTO InvalidActivity

/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
*/
OpenToAck:
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
*/

INSERT #ChildEvents
SELECT EventID
FROM dbo.Event (NOLOCK)
WHERE ParentEventID IS NOT NULL
AND Status = @EventStatus_Open
AND LastOccurTime < @UTCTargetTime

WHILE EXISTS (SELECT * FROM #ChildEvents)
BEGIN

SET ROWCOUNT 1

SELECT @EventID = EventID
FROM #ChildEvents

DELETE #ChildEvents

SET ROWCOUNT 0

EXEC dbo.UpdateEventStatusEx @eventid = @EventID,
@status = @EventStatus_Ack,
@origstatus = @EventStatus_Open,
@actualstatus = @ActualStatus OUT,
@batch = 1

END

GOTO EndOfScript


/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
*/
AckToClosed:
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
*/

INSERT #ChildEvents
SELECT EventID
FROM dbo.Event (NOLOCK)
WHERE ParentEventID IS NOT NULL
AND Status = @EventStatus_Ack
AND LastOccurTime < @UTCTargetTime

WHILE EXISTS (SELECT * FROM #ChildEvents)
BEGIN

SET ROWCOUNT 1

SELECT @EventID = EventID
FROM #ChildEvents

DELETE #ChildEvents

SET ROWCOUNT 0

EXEC dbo.UpdateEventStatusEx @eventid = @EventID,
@status = @EventStatus_Closed,
@origstatus = @EventStatus_Ack,
@actualstatus = @ActualStatus OUT,
@batch = 1

END

GOTO EndOfScript


/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
*/
ClosedToDeleted:
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
*/

INSERT #ChildEvents
SELECT EventID
FROM dbo.Event (NOLOCK)
WHERE ParentEventID IS NOT NULL
AND Status = @EventStatus_Closed
AND LastOccurTime < @UTCTargetTime

WHILE EXISTS (SELECT * FROM #ChildEvents)
BEGIN

SET ROWCOUNT 1

SELECT @EventID = EventID
FROM #ChildEvents

DELETE #ChildEvents

SET ROWCOUNT 0

EXEC dbo.DeleteEventEx @eventid = @EventID,
@batch = 1

END

GOTO EndOfScript

/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
*/
InvalidActivity:
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
*/
PRINT 'Invalid Activity Name'
GOTO EndOfScript

/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
*/
EndOfScript:
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
*/
exec master..nqp_sp_done @ProcessID, @StartTime



GO


--
marty45714
------------------------------------------------------------------------
marty45714's Profile: https://forums.netiq.com/member.php?userid=4004
View this thread: https://forums.netiq.com/showthread.php?t=46904

0 Likes
Anonymous_User Absent Member.
Absent Member.

Re: Migrating AutoAgeEvents Stored Procedure to AppManager 8


Wasn't me who wrote it... although I know who did... anyway - as Kevin
says, the extended procs are gone in AM8 and so this code needs to be
changed. Simple change... instead of this:-

exec master..nq_now @UTCTimeNow OUT

use this line:-

select @UTCTimeNow = datediff(s, '19700101', getutcdate())

and DELETE the two lines

exec master..nq_sp_try 'AAutoAgeEvents', @ProcessID OUT, @StartTime OUT
exec master..nqp_sp_done @ProcessID, @StartTime

and then the procedure should work.


--
Andy Doran
Software Engineer Consultant (NetIQ)
------------------------------------------------------------------------
andy_doran's Profile: https://forums.netiq.com/member.php?userid=3937
View this thread: https://forums.netiq.com/showthread.php?t=46904

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.