Query for finding who deleted tasks\fixlets

Usage and Config
1

We’re on version 9.2. Someone deleted 90% of our tasks\fixlets. We can recover these from a recent backup but I need to find out who did this and when. What would be the DB query to find this information?

Thank you!

2

You should be able to identify this from the database, yes. Do you have some method to filter or identify the content of interest? Perhaps by Fixlet name or site name?

1 Like
3

Yes, it was all within a custom site so we can narrow the results down by that at least.

We can call it “Applications” for now

1 Like
4

You might try a query such as the following:

select *
from LOCAL_OBJECT_DEFS P
inner join VERSIONS V on P.ID = V.ID AND P.Version = V.LatestVersion
where P.ParentID = 1 AND dbo.fn_ExtractField(‘CustomSiteName’, 0, P.Fields) = ‘Applications’

The ‘username’ column should correspond to the operator that deleted the content item in question, and the ‘CreationTime’ field should correspond to when the content was deleted.

1 Like
5

This pulled data but nothing relevant for the last 24 hours, which is when things were supposedly deleted. Maybe we’re looking at a simple case of DB corruption. Fortunately we have backups but its just bizarre.

1 Like
6

I recommend a nightly job the exports all content from the console and checks in the XML data into a GIT repo. This lets you track changes over time and makes it easy to restore specific items, even if it is back in time. You would be in much worse shape if someone modified all of the fixlets & tasks before deleting them.

I wonder if this operator was not malicious, but wanted to have the effect of “locally hiding” the items, but didn’t know how to do that, or didn’t know that deleting them for themselves would delete it for everyone.

7

Another odd thing is that most tasks that were created via “software distribution” were removed. The data is still there to recreate the tasks though.

1 Like
8

That’s what we’re thinking. If it is in fact deletion and not corruption.

1 Like
9

Here is a python script that will backup all items in the console:

It uses the besapi here: https://github.com/CLCMacTeam/besapi

All of the above was written by @hansen_m

This was mentioned in my talk at IBM Interconnect 2015: https://docs.google.com/presentation/d/1pME28wdjkzj9378py9QjFyMOyOHcamB6bk4k8z-c-r0/edit?usp=sharing

3 Likes
10

While we’re waiting for IBM to get back to us on this PMR, are there any other things I can be checking to see what happened? Nothing is hidden and the query isn’t pulling any results that indicate deletion of anything recently.

11

Ok, holy crap just noticed this. The entire custom site is missing… How do I query THAT type of deletion?? I thought it was just tasks\fixlets as there were other tasks by the similar names that were still around. But yea, the site is definitely gone.

1 Like
12

I just did a built in 100 row query on the Custom_Sites table and the Site is still listed. Last modification date was 2015-06-18 20:09:23.547. Flagged as 1 for IsDeleted. Can I simply unflag this and it will reappear? And, what would be the query to find the modification owner?

1 Like
13

I’m not sure if it is different for Sites, but I believe for fixlets & tasks, you can just change the “IsDeleted” flag and it will reappear.

14

So I managed to change the flag via the UDPATe query and it did bring back the site, but no fixlets. Which table(s) would I find all the fixlet data to do this? Can I run an SQL query for fixlets and what site they were created in?

15

So, figured out how to resolve this. Fortunately, the DBs keep all data, simply flagged as “isDeleted”.

Restore the custom site:

UPDATE dbo.CUSTOM_SITES
SET IsDeleted='0'
WHERE Sitename='YOURSITENAME'

Wait for the site to propagate to the console. Then to bring back your data:

UPDATE LOCAL_OBJECT_DEFS	
SET ParentID='0'
Where Sitename='YOURSITENAME'

This worked like a charm and we’re currently in the process of verifying all the data. Hope this may help anyone who has this problem in the future. Thanks to everyone for your help!

3 Likes
16

Glad to hear it worked out, but as a general note, please do backup the database prior to making any such changes within it!

4 Likes