hey,
I’m have warning in DES Console Health about "Stopped And Expired Actions"
The problem is - On the one hand, I want to delete the actions bc its make the system slowly a little bit. But on the other hand, I need to document the actions.
I saw some topic about it, but I don’t fully understand.
I know it’s save in DB, but to check something it’s not comfortable.
There is solution to get all the actions to file and if I will need - I’ll just push the file into the console?
The Action history consists of a LOT of data. It’s not only the Action record itself, it’s all the details of each action from each computer that ran the action. This includes the status of each line of the Action run.
I have 50k clients and over 100 Operator, and I find that I need to delete the Stopped/Expired Actions that are older than 3 months. I tried to keep a years worth, but the Console just got too sluggish to be manageable.
We tried to “archive” old action details, but it was a nightmare trying to review the data later.
What we found was that in our environment, we didn’t need the data much after a week of the Action being run. Only once or twice have I been asked if I had data from a really old Action,
Deleting the actions from the console only turns their visabity flag to false, all data is still in the database. So its r really a matter of how much Live data your organisation’s need to view in the console.
hey, thank abut the response.
The organization need at lease 1 year.
But the console is fill out after several month.
We’re want the “Live data” bc it is more comfortable to see the history .
I’m not a DBA, and if I want to view the history. I need him, and also we need to spend time to understand all the table and write some SQL query to parser and filter it.
And I think it could be a nice feature to export all live data (and yes, I know its a lot) and option to import it and its can help to investigation problem.
There is a mail address or “feature requests” when I can ask for this?
The IDEAS portal is where you can submit feature requests: https://bigfix-ideas.hcltechsw.com/
Another approach.
If you delete an action in BigFix console, it is marked deleted in the database, as @zanesand mentioned.
If you have not run the Audit Trail Cleaner, then you can undelete actions. Here is a video you can share with your DBA on how to undelete an action: https://www.youtube.com/watch?v=Os5igZwM6jg
Have you taken a look at BigFix Insights for handling your historical data needs? This might be a good alternative to keeping the data live, but allowing it’s removal from the operational side.
I believe there was an Idea some time ago about having an auto-grooming process to siphon action records over to another database based on criteria. Some companies have homebrew solutions for that. Brolly, are you saying that Insights could be setup to do that? If so, it would be interesting to understand.
We’ve long been in the habit of using audit trail cleaner to trim off all but the last few weeks of action history, otherwise the console slows to a crawl. Before I run it, I typically save off the metadata to keep in an annual log that I need to keep for my internal audit folks.
Insights is a pretty interesting development in the BigFix space. Insights lives beside BigFix, so reams of action info in Insights will not slow down your BigFix consoles nor BigFix production performance.
https://help.hcltechsw.com/bigfix/10.0/insights/Insights/Getting_Started/c_Prerequisites_whatsnew.html
Please do note that we recommend the Insights ETL be run vs a copy of the BigFix database and not on the live root server. the ETL is pretty demanding and we want to protect the root server from that load.
Check out the following 3 tables in Insights
datasource_Actions + datasource_Fixlets + datasource_ActionResults
https://help.hcltechsw.com/bigfix/10.0/insights/Insights/Operators_Guide/c_schemanew.html
I think a join of those 3 will fulfill your long term recordkeeping needs and allow you to more aggressively delete completed actions on your root server to keep load low.
This is Almost Perfect!
thank you!.
I found this perfect query if somebody here need.- by @MattPeterson - thank man!
select A.name as "Action Name", A.ID, U.USERNAME, A.CreationTime, C.VALUE as "Computer Name", R.COMPUTERID, STATE, STARTTIME, ENDTIME, TRYCOUNT, RETRYCOUNT, LINENUMBER
from dbo.ACTION_DEFS A, dbo.ACTION_FLAGS F, dbo.ACTIONRESULTS R, dbo.BES_COLUMN_HEADINGS C, dbo.USERINFO U
where (A.ID = R.ACTIONID and A.ID = F.ACTIONID AND C.NAME = 'Computer Name' and C.VALUE LIKE 'ENTER_PCNAME_HERE%' and c.COMPUTERID = R.COMPUTERID and U.MASTHEADUSERNAME = A.USERNAME)
The next step is - how to return several action at once.
Tnx again!
I don’t know if its will help to someone.
But I solve the problem.
and here the explain what to do - all the code is SQL, and should run in the DB:
1.This code will take the the ID and Undeleted him:
exec bfenterprise.dbo.undelete_action <action_ID>
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
3.Thank to @MattPeterson this code will gives you all the actions who deleted by computer name:
select A.name as "Action Name", A.ID, U.USERNAME, A.CreationTime, C.VALUE as "Computer Name", R.COMPUTERID, STATE, STARTTIME, ENDTIME, TRYCOUNT, RETRYCOUNT, LINENUMBER
from dbo.ACTION_DEFS A, dbo.ACTION_FLAGS F, dbo.ACTIONRESULTS R, dbo.BES_COLUMN_HEADINGS C, dbo.USERINFO U
where (A.ID = R.ACTIONID and A.ID = F.ACTIONID AND C.NAME = 'Computer Name' and C.VALUE LIKE 'ENTER_PCNAME_HERE%' and c.COMPUTERID = R.COMPUTERID and U.MASTHEADUSERNAME = A.USERNAME)
DECLARE @MyCursor CURSOR;
DECLARE @ID INT;
BEGIN
SET @MyCursor = CURSOR FOR
SELECT A.ID
from dbo.ACTION_DEFS A, dbo.ACTION_FLAGS F, dbo.ACTIONRESULTS R, dbo.BES_COLUMN_HEADINGS C, dbo.USERINFO U
where (A.ID = R.ACTIONID and A.ID = F.ACTIONID AND C.NAME = 'Computer Name' and C.VALUE LIKE 'pcname%' and c.COMPUTERID = R.COMPUTERID and U.MASTHEADUSERNAME = A.USERNAME)
OPEN @MyCursor
FETCH NEXT FROM @MyCursor
INTO @ID
WHILE @@FETCH_STATUS = 0
BEGIN
exec bfenterprise.dbo.undelete_action @ID
FETCH NEXT FROM @MyCursor
INTO @ID
END;
CLOSE @MyCursor ;
DEALLOCATE @MyCursor;
END;
Hi @ori
Is there any way to use this same code, but instead of Computer Name, use Issuer ID or User?
Hey @Ian sorry about the delay.
Yes, you can use the code with Issuer ID or User.
Before I’ll show you, there is 2 kind of codes.
Now when I check it, it’s even easier then I thought.
For example when you want to check all the deleted by ‘issueID’ (A coworker who performed an action):
SELECT ID, CreatorID
FROM [BFEnterprise].[dbo].[ACTIONS]
WHERE IsDeleted = 1 and CreatorID = '<IssueID>'
P.S.
“CreatorID” in this table its equal to IssueID
----------
And if you want to restore by IssueID:
DECLARE @MyCursor CURSOR;
DECLARE @ID INT;
BEGIN
SET @MyCursor = CURSOR FOR
SELECT ID
FROM [BFEnterprise].[dbo].[ACTIONS]
WHERE IsDeleted = 1 and CreatorID = '<IssueID>'
OPEN @MyCursor
FETCH NEXT FROM @MyCursor
INTO @ID
WHILE @@FETCH_STATUS = 0
BEGIN
exec bfenterprise.dbo.undelete_action @ID
FETCH NEXT FROM @MyCursor
INTO @ID
END;
CLOSE @MyCursor ;
DEALLOCATE @MyCursor;
END;
And you ask me about filtering/restore by User, do you mean the user who preform this action? or the user on whom the action was performed?
have fun, Hope this helps you 
P.S. 2
Sorry about my English, I’m working on it. 
Thank you @ori!!
Yes, looking for the user who issued the action. I will test this with our DBA team.
Thanks again!!
Hello again @ori
We an the 1st query and got an error … the IssueID are characters (operator@company.com) where CreatorID is looking for an integer. Are we missing something here? Thank you again!
Hey, Sorry about that.
I missed details.
Each username has an ID number.
if you want to convent from ID to username - you can check in the table “USERINFO”.
For example:
SELECT [Username],[UserID]
FROM [BFEnterprise].[dbo].[USERINFO]
And it’s will give you a list you can use to convert.
hope this help.
Thanks again @ori
After running the query then the code to recover, it showed 11379 rows updated in the DB, but only half are now showing up again in Big Fix Console. That is progress, just curious as to why only half appear back in BF Console. Have you seen this behavior in the past? Would you know the difference between the isDeleted column in ACTON_FLAGS and ACTIONS table? Wondering if there is a change available that could make a difference there. Thanks again for all your help, we couldn’t have made this progress without you!!!
mmm…
first for all, I’m not related to BigFix support, and I’m not a DBA.
So don’t take everything said like its have to be true.
About that - I did a test.
I created a user, and do like 7 actions.
three of them - I deleted. and four of them I stay.
After that I restored the actions.
so now I have 7 actions in BF console and 0 deleted actions.
In ACTION_FLAGS table I executed this query:
SELECT [ActionID],[IssuerID],[IsDeleted]
FROM [BFEnterprise].[dbo].[ACTION_FLAGS]
WHERE IssuerID = 5
and the result is:
In ACTIONS table I executed this query:
SELECT [ID],[IsDeleted],[CreatorID]
FROM [BFEnterprise].[dbo].[ACTIONS]
WHERE CreatorID = 5
and the result was:
that mean - there is a different between the column in the tables.
So I have to correct myself -
The query should be:
(because in actions table if you deleted once, the flag remains constant, and in action_flags table is gives you the real result).
SELECT [ActionID],[IssuerID],[IsDeleted]
FROM [BFEnterprise].[dbo].[ACTION_FLAGS]
WHERE IssuerID = 5 and IsDeleted =1
Sorry for the confusion, and again sorry about my English.
It would be interesting to know some statements to catch similar results in DB2 database. Has anyone an idea?
Sorry, I’m not understand what you are asking.