Undelete_action Stored Procedure

Usage and Config
1

(imported topic written by chipjnr)

Hi,

I previously removed action history to help the performance of BigFix.

I need to undelete the action history & I know I can do this by running the undelete_action stored procedure.

Once the procedure has been run I have to clear the BES console cache.

To undelete an action ID I know I run the following command;

exec undelete_action

As I don’t know what action ID’s are relating back to 05 & 06, how do I restore all of action history?

Regards,

Neil

2

(imported comment written by jessewk)

Hi Neil,

You probably don’t want to undelete all the actions.

You can use this query to get all the deleted actions and from there find the IDs of the specific actions you’d actually like to undelete:

select A.name as “Action Name”, A.ID, A.CreationTime
from ACTION_DEFS A, ACTION_FLAGS F
where A.ID = F.ActionID AND F.IsDeleted=1

p.s. I deleted your duplicate post

3

(imported comment written by chipjnr)

Hi,

I discovered the action ID using the query you provided, thanks.

When I run exec undelete_action 4477 or exec undelete_action ‘4477’ I get the following error;

Server: Msg 208, Level 16, State 1, Procedure undelete_action, Line 5

Invalid object name 'ACTIONS

Thanks,

Neil

4

(imported comment written by jessewk)

I just tried on a recent 7.0 build and it worked fine. The syntax is:

exec undelete_action 4477

I looked at the definition of the stored procedure and I did not see ACTIONS anywhere in there. Can you post the definition of your stored procedure and also the exact version of 7.0 you are running?

Did you upgrade from 6.0 to 7.0? I’m thinking your stored procedure didn’t get updated because the 6.0 version of the stored procedure would include the ACTIONS object on line 5, and it would fail if run against a 7.0 database. If this is the case, I suggest you give support a call and they can help make sure your database is updated correctly.

5

(imported comment written by chipjnr)

Hi,

Here is the definition that is listed in SQL.

'CREATE procedure undelete_action

@actionId integer

AS

BEGIN

UPDATE ACTIONS

SET IsDeleted = 0

WHERE ACTIONID = @actionId

UPDATE ACTIONRESULTS

SET ActionID = ActionID

WHERE ActionID = @actionId

END

GO’

I’m running 7.0.1.376 & I upgraded from version 6 to 7.

I’ll contact support regarding ensuring the database is updated correctly.

Thanks,

Neil

6

(imported comment written by jessewk)

Hi Neil,

I did a little more investigation and it looks like the undelete_action stored procedure was broken in 7.0.1. If upgrading to 7.0.7 is possible for you, I’d recommend just doing that.

If you can’t upgrade you should be able to run the 7.0.7 stored procedure against the 7.0.1 database:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER procedure dbo.undelete_action
@ActionID integer
as
begin
update ACTION_FLAGS
set IsDeleted = 0,
ManyVersion = dbo.fn_IncrementManyVersion( ManyVersion ),
OriginServerID = NULL,
OriginSequence = NULL
where ActionID = @ActionID
end

7

(imported comment written by chipjnr)

Hi,

I’ve upgraded the Bigfix server to version 7.0.7 & the stored procedure now works successfully.

Thanks for your help.

Neil

8

(imported comment written by SystemAdmin)

Forgive my ignorance, but where would I run these commands? I need to verify that I can get my past actions, even after i delete them from the console. I am pretty green in SQL, and looking for what tool to use for the above procedures.

9

(imported comment written by NoahSalzman)

For a GUI, the most common choice would be

Microsoft also provides a command line (SQLCMD) tool that allows you to run SQL commands.

10

(imported comment written by SystemAdmin)

Got it, thank you Noah.

Can you put a range of Action ID’s in here to undelete, or do I need to do these one by one?

11

(imported comment written by NoahSalzman)

Yep, you would add at WHERE clause to the SQL that was something like … WHERE ID > 123 and ID < 500

12

(imported comment written by cstoneba)

what would you add to this querey to pull back the operator that deployed the action?

select A.name as “Action Name”, A.ID, A.CreationTime

from ACTION_DEFS A, ACTION_FLAGS F

where A.ID = F.ActionID AND F.IsDeleted=1

13

(imported comment written by Aram_Eblighatian)

You can leverage something like:

select A.Name as 
'Action Name', A.ID, A.CreationTime, dbo.fn_ExtractField(
'Issuer', 0, A.Fields) as 
'Action Issuer' from ACTION_DEFS A, ACTION_FLAGS F where A.ID = F.ActionID AND F.IsDeleted=1