Extracting Files from AppSense Environment Manager Database

We recently experienced some data loss issues with our Appsense Environment Manager installation. Long story short, a change we made to our application groups ended up deleting the user data associated with a particular application group.

What is Appsense Environment Manager, you ask? It's a desktop personalization platform, which consists of an agent running on the client and a database to go with it. The agent captures user specific data from registry and file system and stores it in an SQL database. The captured data can then be roamed to other clients where the agent is running. It's your roaming profile, on steroids.

Back to the story...

After working through the initial morning-after panic, my colleague reached out to Appsense for some assistance. They advised us to get a hold of the database backup from before the change was made and do a full restore. However, by this time, users had been logging on to their machines and generating data in the production database. A full restore was not an option. We needed to selectively restore files. However, restoring files one by one, for each user, from Environment Manager Console  was going to be a big hassle.

Our DBAs were kind enough to mount the backup database under a different name so that both the backup and production databases were available for us simultaneously. With that in place and nothing else to lose, we started investigating the database.

Locating the Data

First task was to figure out where the data was located within the database. Since we knew that the actual data is stored within the database, finding the right tables was only a matter of running the  Disk Usage by Top Tables report from SQL Server Management Studio.

This report is accessible by right clicking on the database, choosing Reports , then Standard Reports  and then Disk Usage by Top Tables.

The report spat out something like this:

We decided to start with dbo.ApplicationData table, instead of archive. From there, we traced some of the foreign key relationships to arrive at the conclusion that we needed the following tables and columns.

dbo.User

This table holds all user information. We just needed the data from the following columns for our purposes.

UserPK          -- primary key
LoginName       -- username

dbo.ApplicationData

This table contains the actual data. The columns of interest here are:

ApplicationProfileFK      -- FK to ApplicationProfile
Filename                  -- Name of the file
RelativePath              -- Path of the file.
Data                      -- Actual data, data type is Image
Size                      -- actual size of the file
ModifiedLow               -- Last Modified date  
ModifiedHigh              -- Last Modified data

dbo.ApplicationProfile

This table establishes relationships between users and their stored files. We only need the following columns from this table:

ApplicationProfilePK      -- Primary Key
UserFK                    -- Foreign Key to User table.</pre>

Final Query

Based on the table structure discovered above, we were able to construct the following query to extract file data and relevant details. This query expects the user's login name, relative path and name of the file you are looking for.

SELECT 
     AD.Data,
     AD.Size,
     DataLength(AD.Data) As StoredSize,
     AD.RelativePath,
     AD.ModifiedLow,
     AD.ModifiedHigh
FROM
     ApplicationData As AD
JOIN 
     ApplicationProfile As AP ON AD.ApplicationProfileFK = AP.ApplicationProfilePK
JOIN 
     [User] As Usr ON AP.UserFK = Usr.UserPK
WHERE 
     User.Loginname = '' AND AD.RelativePath like '%%' AD.[Filename] = ''

C# for the rest

Once we knew the data source, all that was left was to come up with a script or program to extract specific files for specific users.  We put together the following C# function to accomplish the rest.

public static void ExportDataToFile(string username, string path, string filename, string destinationRoot)
{
    try
    {
        // the extracted file will be saved in [destinationRoot]\[username] folder
        string destination = Path.Combine(destinationRoot, username);
        string saveFilename = Path.Combine(destination, filename);

        // if the [destinationRoot]\[username] folder does not exist, create it.
        if (!Directory.Exists(destination)) { Directory.CreateDirectory(destination); }

        using (var sqlconnection = new SqlConnection("...Your Connection String..."))
        {
            // open SQL connection.
            sqlconnection.Open();

            var query = "SELECT AD.Data, AD.Size,DataLength(AD.Data) As StoredSize, "
                        + "AD.RelativePath, AD.ModifiedLow, AD.ModifiedHigh "
                        + "FROM ApplicationData As AD JOIN ApplicationProfile As AP  "
                        + "ON AD.ApplicationProfileFK = AP.ApplicationProfilePK "
                        + "JOIN [User] As Usr ON AP.UserFK = Usr.UserPK "
                        + "WHERE Usr.Loginname = @Loginname "
                        + "AND AD.RelativePath like @Path "
                        + "AND AD.[Filename] = @Filename ";

            //  construct SqlCommand using the query above.
            var selectCommand = new SqlCommand(query, sqlconnection);
            selectCommand.Parameters.AddWithValue("@Loginname", username);
            selectCommand.Parameters.AddWithValue("@Path", "%" + path + "%");
            selectCommand.Parameters.AddWithValue("@Filename", filename);

            // execute it.
            var reader = selectCommand.ExecuteReader();

            // did we get anything back?
            if (reader.Read())
            {
                byte[] data = (byte[])reader["Data"];

                // extract size and stored size
                int size, storedsize;
                int.TryParse(reader["Size"].ToString(), out size);
                int.TryParse(reader["StoredSize"].ToString(), out storedsize);

                // extract last modified date information.
                long low, high, total;
                long.TryParse(reader["ModifiedLow"].ToString(), out low);
                long.TryParse(reader["ModifiedHigh"].ToString(), out high);
                total = low + (high << 32);
                DateTime ft = DateTime.FromFileTime(total);

                // if the storedsize value is smaller than size value, the data is most likely zipped
                // handle accordingly.
                if (size > storedsize)
                {
                    using (var memstream = new MemoryStream(data))
                    {
                        using (var gzstream = new GZipStream(memstream, CompressionMode.Decompress))
                        {
                            using (var fs = new FileStream(saveFilename, FileMode.Create, FileAccess.Write))
                            {
                                var breader = new BinaryReader(gzstream);
                                var bwriter = new BinaryWriter(fs);
                                var numArray = new byte[2048];
                                for (var i = breader.Read(numArray, 0, (int)numArray.Length); i > 0; i = breader.Read(numArray, 0, (int)numArray.Length))
                                {
                                    bwriter.Write(numArray, 0, i);
                                }
                                bwriter.Close();
                                fs.Close();
                            }
                        }
                    }

                    // Set newly created file's last write time to what we pulled from the database.
                    File.SetLastWriteTime(saveFilename, ft);
                }
                // data is probably not compressed. write it out.
                else
                {
                    using (var fs = new FileStream(saveFilename, FileMode.Create, FileAccess.Write))
                    {
                        var bwriter = new BinaryWriter(fs);
                        bwriter.Write(data);
                        bwriter.Close();
                        fs.Close();
                    }

                    // set newly created file's last write time to what we pulled from the database.
                    File.SetLastWriteTime(saveFilename, ft);
                }
            }
        }
    }
    catch (Exception ex)
    {
        // basic, handle exceptions as you wish.
        Console.WriteLine(ex);
    }
}

For example, to extract a user's default signature file, this function can be called in the following fashion:

ExtractDataToFile("testuser", "Microsoft\\Signatures", "Default.htm", "C:\ExtractedData");

This will extract {CSIDL_APPDATA}\Microsoft\Signatures\Default.htm  for testuser  and write it to C:\ExtractedData\testuser\Default.htm

Last Modified Date

ApplicationData  table within Environment Manager's database contains two date columns, ModifiedLow and ModifiedHigh. After some research, we were able to figure out that these correspond to ftLastWriteTime  for WIN32_FILE_ATTRIBUTE_DATA Structure, which is a FILETIME structure.

The following C# code can take the ModifiedLow  and ModifiedHigh values and convert them into easily usable DateTime value.

// extract last modified date information.
long low, high, total;
long.TryParse(reader["ModifiedLow"].ToString(), out low);
long.TryParse(reader["ModifiedHigh"].ToString(), out high);
total = low + (high << 32);
DateTime ft = DateTime.FromFileTime(total);

// set a file's last write time to the newly calculated DateTime value.
File.SetLastWriteTime(saveFilename, ft);

And that's how we recovered data selectively from Environment Manager database. Leave a comment below if you end up using this information and don't forget to thank your DBAs. 🙂