Database query to find a deleted computer and associated username

Usage and Config Platform
1

Hi all,

Can anyone tell me how to use SQL to query a computer that was deleted from the console but is in the databse, by username.

For example, I deleted an unknown computer belonging to jsmith and now need to retrieve that computer name.

Any and all help would be appreciated, I am struggling navigating the DB with my limited SQL skills.

2

There is an old post that has info on querying deleted computers…

https://www.ibm.com/developerworks/community/forums/html/topic?id=77777777-0000-0000-0000-000014734286

That should get you started with at least how to retrieve them. Sorry I can’t be of more help for obtaining the deleted computers by username

3

How was the computer tagged as owned by jsmith? Would that name be the value of the User Name property or did you have a custom property that tagged the owner of a computer? This can be done as long as we know some property value uniquely associated with this system.

4

It would just be the User Name value that is default in Bigfix.

5

That User Name property in bigfix is only set if there is a user logged in, otherwise it will be None. It should only be set if it happened to be set at time of deletion.

6

It was set at time of deletion. I am just looking for the query to get me there.

7

Assuming your Computer Name and User Name properties are the same IDs as mine, you can use these queries:

select ComputerID,ResultsText from QUESTIONRESULTS where SiteID in (select SiteID from SITENAMEMAP where Sitename = 'ActionSite') AND AnalysisID = 27 AND ResultsText like 'Steve%'

select ComputerID,ResultsText from QUESTIONRESULTS where SiteID in (select SiteID from SITENAMEMAP where Sitename = 'ActionSite') AND AnalysisID = 4 AND ComputerID = 6140499

Run the first one replacing Steve with the desired username. It will return the computer IDs and usernames that match string. Then use the ComputerID from the first query in the 2nd query to get the computer name.

2 Likes