1 Reply Latest reply on Apr 9, 2015 11:14 PM by AbdulA

    Exporting someone's archived Session Data for offline analysis

    gregf SupportEmployee
      In the unlikely event anyone ever needs to do this:

                  
      $SQLServer = "YOUR-SQL-SERVER"
      $SQLDBName = "PersonalizationServer"
      $SqlQuery = @'
      SELECT * FROM ApplicationDataArchive ada
      INNER JOIN ApplicationProfile ap ON 
      ada.ApplicationProfileFK = ap.ApplicationProfilePK
      INNER JOIN [User] u ON 
      u.UserPK = ap.UserFK
      AND u.Name = 'YOURDOMAIN\YOURUSER'
      INNER JOIN (
             SELECT Application.ApplicationPK,Application.Name FROM Application
             UNION ALL
             SELECT ApplicationGroup.ApplicationGroupPK,ApplicationGroup.Name FROM ApplicationGroup
             UNION ALL
             SELECT ApplicationReserved.ApplicationReservedPK,ApplicationReserved.Name FROM ApplicationReserved
             ) AS Apps ON Apps.ApplicationPK = AP.ApplicationFK
      AND Apps.Name = 'Session Data'
      AND ada.[Filename] = 'settings.fbr'
      
      '@
       
      $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
      $SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True"
      $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
      $SqlCmd.CommandText = $SqlQuery
      $SqlCmd.Connection = $SqlConnection
      $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
      $SqlAdapter.SelectCommand = $SqlCmd
      $DataSet = New-Object System.Data.DataSet
      $SqlAdapter.Fill($DataSet)
      $SqlConnection.Close()
      $DataSet.Tables[0] 
      
      foreach ($r in $DataSet.Tables[0].Rows) {
        $filepath = ([System.IO.Path]::GetTempPath() + $r.InsertionTime.ToString("yyyyMMdd-hhmmss") + ".fbr")
        
        $fileStream = new-object System.IO.FileStream($filepath,[System.IO.FileMode]::Create)
        $fileStream.Write($r.Data, 0, $r.Data.Length)
        $fileStream.Close()
      }
      
      


      Run it, look for .fbr files in your %temp% folder. They're named based on InsertionDate in the ApplicationDataArchive table, which hopefully corresponds to the date the archive was taken.

      Open the .fbr files in FBRExplorer to peruse them.