(imported topic written by Shawn_Jefferson)
Is it possible to retrieve information (in this case serial number) of a computer that was removed from the bigfix database via the Bigfix Console Remove from Database function?
Thanks,
Shawn
(imported comment written by BenKus)
Hey Shawn,
If you right-click on a computer and go to “Remove”, then the information about the computer is removed from the BES Console view, but it is still in the database and you can query the information.
If you then run the “BES Deleted Computer Remover”: http://support.bigfix.com/bes/install/downloadutility.html#besdelcompremover (which is different from the “BES Expired Computer Remover”), then the data is deleted from the database and essentially gone forever (unless you can figure out how to get deleted data from the database).
This is a good reason not to run the “BES Deleted Computer Remover” unless you are having database size issues, which happen but are fairly rare.
Ben
(imported comment written by Shawn_Jefferson)
How do I query the information of a computer I removed from the database then? I didn’t run the deleted computer remover.
(imported comment written by BenKus)
You can do something like this:
- Open Query Analyzer, choose the “BFEnterprise” database
- Type in this query:
select * from BES_COLUMN_HEADINGS where value LIKE ‘%’
- This should return 1 row with a computerID as the first column. Then type:
select Name, Value from BES_COLUMN_HEADINGS where ComputerID =
- This should give you a list of all the properties of this computer (deleted or not).
Ben
(imported comment written by SystemAdmin)
Hey Ben… Are these instructions still valid? They don’t seem to be working for me on 7.2.5.
(imported comment written by BenKus)
Hey Kevin,
I don’t believe this will work with 7.x. You can do this though:
- Create a view that reports only the deleted computer properties:
USE BFEnterprise
GO
/****** Object: View dbo.BES_COLUMN_HEADINGS Script Date: 02/16/2010 18:23:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create view dbo.BES_DELETED_COLUMN_HEADINGS as
SELECT Q.ComputerID,
P.Name,
CASE WHEN Q.ResultsText IS NULL
THEN CAST( L.ResultsText AS VARCHAR(7936) )
ELSE Q.ResultsText
END AS ‘Value’,
Q.IsFailure
FROM QUESTIONRESULTS Q LEFT JOIN LONGQUESTIONRESULTS L
ON Q.SiteID = L.SiteID
AND Q.AnalysisID = L.AnalysisID
AND Q.PropertyID = L.PropertyID
AND Q.ComputerID = L.ComputerID,
COMPUTERS C,
SITENAMEMAP M,
LOCAL_OBJECT_DEFS P,
Versions V
WHERE Q.SiteID = M.SiteID
AND M.Sitename = 'ActionSite’
AND P.ID = Q.AnalysisID
AND Q.PropertyID = 1
AND P.ParentID = 0
AND P.ContentType = 5
AND P.ID = V.ID
AND M.Sitename = V.Sitename
AND P.Version = V.LatestVersion
AND C.ComputerID = Q.ComputerID
AND C.IsDeleted = 1
AND M.IsDeleted = 0
- Query this view and it will show the properties for all deleted computers…
Ben
(imported comment written by SystemAdmin)
I can see that computer ID now, but how do I see the actions that were taken against that machine. We had a relay/client removed from the environment, and I want to see the action history for this machine.
(imported comment written by BenKus)
Hey Kevin,
The view definition I gave you was only for property results… You would need to do something similar on the BES_ACTIONS view to see the action results (all I am really changing is getting the view definition and changing the view name and also changing C.IsDeleted = 0 --> C.IsDeleted = 1)
USE BFEnterprise
GO
/****** Object: View dbo.BES_ACTIONS Script Date: 02/19/2010 13:41:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
- Note: this table specification retrieves locking and unlocking actions as well as fixlet actions.
- Note on the note: subscription actions are filtered out because they have been moved to the sitenamemap table
create view
dbo
.
BES_ACTIONS_DELETEDCOMPS
as
select P.ID as ‘ActionID’, ActionResult.ComputerID, P.Name, P.Username,
P.CreationTime as ‘StartTime’,
CONVERT( int, dbo.fn_ExtractField( ‘SourceFixletId’, 0, P.Fields )) AS ‘FixletID’,
CONVERT( varchar(128), dbo.fn_ExtractField( ‘SourceSiteName’, 0, P.Fields )) AS ‘Sitename’,
ActionStateString.ActionStateString AS ‘ActionStatus’
from ACTION_DEFS P, VERSIONS V,
ACTIONRESULTS ActionResult, ACTIONSTATESTRINGS ActionStateString,
COMPUTERS C, ACTION_FLAGS A
where P.ContentType = 7 AND V.Sitename = ‘ActionSite’
AND P.ID = V.ID AND P.Version = V.LatestVersion
AND ActionResult.ActionID = P.ID AND ActionStateString.ActionState = ActionResult.State
AND C.ComputerID = ActionResult.ComputerID AND C.IsDeleted = 1
AND A.ActionID = P.ID AND A.IsDeleted = 0
AND dbo.fn_ExtractField( ‘SourceFixletId’, 0, P.Fields ) IS NOT NULL
AND dbo.fn_ExtractField( ‘SourceSiteName’, 0, P.Fields ) IS NOT NULL
Then use:
select * from BES_ACTIONS_DELETEDCOMPS where ComputerID=
Ben
(imported comment written by SystemAdmin)
Thanks Ben… that makes sense now… I actually redid this using BES_ACTIONS_ALL
(imported comment written by jnovak91)
Hi Ben,
How would this apply to Analysis results of deleted computers? Sorry, my sql isn’t all that great.
Jason
(imported comment written by BenKus)
You want to see results of properties of deleted computers? See post #6.
Ben
(imported comment written by jnovak91)
hey ben,
i did implement the BES_DELETED_COLUMN_HEADINGS view into sql and i do see the deleted computers through that view. my understanding is that this view will only show the column headings for the computers and after looking at the results that is what i see. what i am looking for are the actual results of a specific analysis that a deleted computer was evaluating against. is it possible to get analysis results from deleted computers?
Thanks,
Jason
(imported comment written by BenKus)
Hey Jason,
I see… You might consider these options:
-
Undelete the computer and look at in the console again. If you are just auditing something about one computer this should be good enough.
-
Duplicate all the database stored procedures to query for deleted computers instead of undeleted computers. Also, you will need to add the PropertyIDMapper and update the BES_COLUMN_HEADINGS view to return analysis property results to query analysis properties easily. http://forum.bigfix.com/viewtopic.php?id=1221
Ben
(imported comment written by jnovak91)
Option 1 is all I need and is actually what I was originally looking for when I came across this thread, now how would one go about doing that? Is it a flag in the database?
Jason
(imported comment written by BenKus)
Yea, you can look in the COMPUTERS table in the database. You can run:
UPDATE COMPUTERS set IsDeleted = 0 where ComputerID =
You probably need to clear the cache and restart the console to see the computers…
Ben
(imported comment written by jnovak91)
That got me what I needed. Thanks for all of your help Ben.
Jason
1 Like
(imported comment written by swap-techsa)
Hello guys,
As seen above we can create a database table exclusively for deleted computers.
In the database i am able to see dbo.COMPUTER_REGISTRATIONS. This table has various entries like IP , MAC Address , Computer ID…but cannot see computer name , RAM, OS.
Computer Name , Ram , OS is there in dbo.BES_COLUMN_HEADINGS but the format is not what i want it has Name as heading and entries are Computer name, OS, RAM and there respective values.
Example:
ID Name Value
12345 Computer Name Lab_Client1
12346
Computer Name Lab_Client2
12345 OS win7
12346 OS win8
I want to create the Table with following table entries : Headings : Computer ID, Computer Name, OS, RAM, IP
12345 Lab_client1 Win7 2048 10.0.0.2
12346 Lab_client2 Win8 2048 10.0.0.2
This table should be dynamic any change should be automatically updated.
Please any one help me to create this.
Regards
Swapnil