locked
Query Tracking information Pipelines and Orchestrations (other then MessageBody) RRS feed

  • Question

  • I'm trying to query the tracking information of the Pipelines and the Orchestrations.
    In particular I am interested in the Tracking Events from the Pipelines and the Shape Tracking of the Orchestrations.

    All I can find is the tracking of the Messagebody in the table BizTalkMgmtDb.StaticTrackingInfo.

    For Orchestrations you can change the following tracking settings:

    • Before Orchestration
    • After Orchestration
    • Incoming
    • Outgoing
    • Shape
    • Message
    • Orchestration

    Only "Message" and "Orchestration" influences the value of StaticTrackingInfo.ismsgBodyTrackingEnabled
    So how can I check for which Orchestrations the shape tracking is on?


    For Pipelines you can change the following tracking settings:

    • Start and End Events
    • Send and Receive Events
    • Before Pipeline
    • After Pipeline

    Only "Before Pipeline" and "After Pipeline" influences the value of StaticTrackingInfo.ismsgBodyTrackingEnabled
    So how can I can I check for which Pipelines the tracking Events are on (or off)?

    Anyone has a suggestion how to query this information?


    • Edited by FrankHaarlem Wednesday, November 23, 2016 12:07 PM
    Wednesday, November 23, 2016 12:04 PM

Answers

  • Hi Frank,

    U can try looking at  ExplorerOM, Microsoft.BizTalk.Operations dll or WMI classes that can help you get these information programatically.

    Check the below tool. 

    https://code.msdn.microsoft.com/windowsdesktop/BizTalk-Tracking-Manager-33bce27c


    Pi_xel_xar

    Blog: My Blog

    BizTalkApplicationDeploymentTool: BizTalk Application Deployment Tool/


    Wednesday, November 23, 2016 12:39 PM
    Answerer
  • Hi Frank

    As you have found out, all of the Tracking configuration(how much to track) is stored in the StaticTrackingInfo table. The strServiceName column shows the name of the orchestration/pipeline involved.

    Now, how much to track (which of the tracking checkboxes are enabled) is stored in the imgData column. I am guessing some bitwise calculation is performed on the fields and the value for it is calculated.

    You can use the ExplorerOM API to find out which options are enabled. Code sample is available in below thread-

    https://social.technet.microsoft.com/Forums/windows/en-US/05114b01-1528-4ed0-8a10-80be1c2cd9c8/orchestration-shape-start-and-end-tracking?forum=biztalkgeneral


    Thanks Arindam


    Wednesday, November 23, 2016 12:56 PM
    Moderator
  • The community recommended way is to query using C#, not TSQL directly.

    There are 3 known ways. Here they are ordered by preference:

    Use the Microsoft.BizTalk.Operations.GetTrackedMessage class:

            //Retrieves the message using the operations DLL - Add Microsofr.BizTalk.Operations.dll to references
            public string GetMessageWithOperations(GuidMessageInstanceId)
            {
                try
                {
                    TrackingDatabasedta = new TrackingDatabase(txtTrackingDBServer.Text, txtTrackingDBName.Text);
                    BizTalkOperations operations = new BizTalkOperations();
                    IBaseMessagemessage = operations.GetTrackedMessage(MessageInstanceId, dta);
                    string body = string.Empty;
                    using (StreamReaderstreamReader = new StreamReader(message.BodyPart.Data))
                    {
                        body = streamReader.ReadToEnd();
                    }
                    return body;
                }
                catch (ExceptionexOp)
                {
                    return "Failed to get message with id "+ MessageInstanceId.ToString() + " from tracking database: "+ exOp.Message;
                }
            }

    Using WMI:

            //Uses WMI to save the tracked message out to a file folder using MSBTS_TrackedMessageInstance class
            public stringGetMessageWithWMI(GuidMessageInstanceId)
            {
                try
                {
                    // Construct full WMI path to the MSBTS_TrackedMessageInstance using the message guid (NOTE: MessageInstanceID string value must be enclosed in {} curly brackets)
                    string strInstanceFullPath = "\\\\.\\root\\MicrosoftBizTalkServer:MSBTS_TrackedMessageInstance.MessageInstanceID='{"+ MessageInstanceId.ToString() + "}'";
                    // Load the MSBTS_TrackedMessageInstance
                    ManagementObject objTrackedSvcInst = new ManagementObject(strInstanceFullPath);
                    // Invoke "SaveToFile" method to save the message out into the specified folder
                    objTrackedSvcInst.InvokeMethod("SaveToFile", new object[] {Application.StartupPath});
                    //Get all files in the directory starting with this messageid
                    string[] files = Directory.GetFiles(Application.StartupPath, "{"+ MessageInstanceId.ToString() + "*.*");
                    string message = "";
                    foreach (string file in files)
                    {
                        if(file.EndsWith(".out"))
                        {
                            using (StreamReadersr = new StreamReader(file))
                            {
                                message = sr.ReadToEnd();
                            }
                        }
                    }
                    foreach (string file in files)
                    {
                        System.IO.File.Delete(file);
                    }
                    if (files.Length == 0)
                    {
                        throw new Exception("No files found on folder that match the GUID");
                    }
                    return message;
                }
                catch (ExceptionexWMI)
                {
                    return "Failed to save tracked message with id "+ MessageInstanceId.ToString() + " into folder " + Application.StartupPath + ": "+ exWMI.Message;
                }
            }

    Using reflection. A bit of a hack but works:

            //Calls BizTalk stored procedure to retrieve compressed message and decompresses it
            public stringGetMessageWithSQL(GuidMessageInstanceId)
            {
                try
                {
                    //Connection to DTA database on localhost
                    SqlConnection con = new SqlConnection("Data Source=" + txtTrackingDBServer.Text + ";Initial Catalog=" + txtTrackingDBName.Text + ";Integrated Security=True");
                    string message = "";
                    try
                    {
                        SqlCommandcmd = new SqlCommand();
                        SqlDataReader reader;
                        //Build execution of stored procedure bts_GetTrackedMessageParts
                        cmd.CommandText = "bts_GetTrackedMessageParts";
                        cmd.CommandType = CommandType.StoredProcedure;
                        SqlParameterguidParameter = new SqlParameter("@uidMsgID", SqlDbType.UniqueIdentifier);
                        guidParameter.Value = MessageInstanceId;
                        cmd.Parameters.Add(guidParameter);
                        cmd.Connection = con;
                        con.Open();
                        reader = cmd.ExecuteReader();
                        //Get the reader to retrieve the data
                        while (reader.Read())
                        {
                            //Use memory stream and reflection to get the data
                            SqlBinarybinData = new SqlBinary((byte[])reader["imgPart"]);
                            MemoryStream stream = new MemoryStream(binData.Value);
                            AssemblypipelineAssembly = Assembly.LoadFrom(string.Concat(@"C:\Program Files\Microsoft BizTalk Server 2006", @"\Microsoft.BizTalk.Pipeline.dll"));
                            TypecompressionStreamsType = pipelineAssembly.GetType("Microsoft.BizTalk.Message.Interop.CompressionStreams", true);
                            StreamReader st = new StreamReader((Stream)compressionStreamsType.InvokeMember("Decompress", BindingFlags.Public | BindingFlags.InvokeMethod | BindingFlags.Static, null, null, new object[] { (object)stream }));
                            message = st.ReadToEnd();
                        }
                    }
                    finally
                    {
                        con.Close();
                    }
                    return message;
                }
                catch (ExceptionexSQL)
                {
                    return "Failed to get message with id "+ MessageInstanceId.ToString() + " from tracking database: "+ exSQL.Message;
                }
            }

    Source: Thiago Almeida's Blog


    My Technet Articles

    If you like this or another reply, vote it up!
    If you think this or another reply answers the original question, mark it or propose it as an answer.


    Mauricio Feijo
    www.mauriciofeijo.com


    • Edited by Mauricio Feijo Wednesday, November 23, 2016 1:07 PM
    • Marked as answer by FrankHaarlem Thursday, November 24, 2016 9:58 AM
    Wednesday, November 23, 2016 1:04 PM

All replies

  • Hi Frank,

    U can try looking at  ExplorerOM, Microsoft.BizTalk.Operations dll or WMI classes that can help you get these information programatically.

    Check the below tool. 

    https://code.msdn.microsoft.com/windowsdesktop/BizTalk-Tracking-Manager-33bce27c


    Pi_xel_xar

    Blog: My Blog

    BizTalkApplicationDeploymentTool: BizTalk Application Deployment Tool/


    Wednesday, November 23, 2016 12:39 PM
    Answerer
  • Hi Frank

    As you have found out, all of the Tracking configuration(how much to track) is stored in the StaticTrackingInfo table. The strServiceName column shows the name of the orchestration/pipeline involved.

    Now, how much to track (which of the tracking checkboxes are enabled) is stored in the imgData column. I am guessing some bitwise calculation is performed on the fields and the value for it is calculated.

    You can use the ExplorerOM API to find out which options are enabled. Code sample is available in below thread-

    https://social.technet.microsoft.com/Forums/windows/en-US/05114b01-1528-4ed0-8a10-80be1c2cd9c8/orchestration-shape-start-and-end-tracking?forum=biztalkgeneral


    Thanks Arindam


    Wednesday, November 23, 2016 12:56 PM
    Moderator
  • The community recommended way is to query using C#, not TSQL directly.

    There are 3 known ways. Here they are ordered by preference:

    Use the Microsoft.BizTalk.Operations.GetTrackedMessage class:

            //Retrieves the message using the operations DLL - Add Microsofr.BizTalk.Operations.dll to references
            public string GetMessageWithOperations(GuidMessageInstanceId)
            {
                try
                {
                    TrackingDatabasedta = new TrackingDatabase(txtTrackingDBServer.Text, txtTrackingDBName.Text);
                    BizTalkOperations operations = new BizTalkOperations();
                    IBaseMessagemessage = operations.GetTrackedMessage(MessageInstanceId, dta);
                    string body = string.Empty;
                    using (StreamReaderstreamReader = new StreamReader(message.BodyPart.Data))
                    {
                        body = streamReader.ReadToEnd();
                    }
                    return body;
                }
                catch (ExceptionexOp)
                {
                    return "Failed to get message with id "+ MessageInstanceId.ToString() + " from tracking database: "+ exOp.Message;
                }
            }

    Using WMI:

            //Uses WMI to save the tracked message out to a file folder using MSBTS_TrackedMessageInstance class
            public stringGetMessageWithWMI(GuidMessageInstanceId)
            {
                try
                {
                    // Construct full WMI path to the MSBTS_TrackedMessageInstance using the message guid (NOTE: MessageInstanceID string value must be enclosed in {} curly brackets)
                    string strInstanceFullPath = "\\\\.\\root\\MicrosoftBizTalkServer:MSBTS_TrackedMessageInstance.MessageInstanceID='{"+ MessageInstanceId.ToString() + "}'";
                    // Load the MSBTS_TrackedMessageInstance
                    ManagementObject objTrackedSvcInst = new ManagementObject(strInstanceFullPath);
                    // Invoke "SaveToFile" method to save the message out into the specified folder
                    objTrackedSvcInst.InvokeMethod("SaveToFile", new object[] {Application.StartupPath});
                    //Get all files in the directory starting with this messageid
                    string[] files = Directory.GetFiles(Application.StartupPath, "{"+ MessageInstanceId.ToString() + "*.*");
                    string message = "";
                    foreach (string file in files)
                    {
                        if(file.EndsWith(".out"))
                        {
                            using (StreamReadersr = new StreamReader(file))
                            {
                                message = sr.ReadToEnd();
                            }
                        }
                    }
                    foreach (string file in files)
                    {
                        System.IO.File.Delete(file);
                    }
                    if (files.Length == 0)
                    {
                        throw new Exception("No files found on folder that match the GUID");
                    }
                    return message;
                }
                catch (ExceptionexWMI)
                {
                    return "Failed to save tracked message with id "+ MessageInstanceId.ToString() + " into folder " + Application.StartupPath + ": "+ exWMI.Message;
                }
            }

    Using reflection. A bit of a hack but works:

            //Calls BizTalk stored procedure to retrieve compressed message and decompresses it
            public stringGetMessageWithSQL(GuidMessageInstanceId)
            {
                try
                {
                    //Connection to DTA database on localhost
                    SqlConnection con = new SqlConnection("Data Source=" + txtTrackingDBServer.Text + ";Initial Catalog=" + txtTrackingDBName.Text + ";Integrated Security=True");
                    string message = "";
                    try
                    {
                        SqlCommandcmd = new SqlCommand();
                        SqlDataReader reader;
                        //Build execution of stored procedure bts_GetTrackedMessageParts
                        cmd.CommandText = "bts_GetTrackedMessageParts";
                        cmd.CommandType = CommandType.StoredProcedure;
                        SqlParameterguidParameter = new SqlParameter("@uidMsgID", SqlDbType.UniqueIdentifier);
                        guidParameter.Value = MessageInstanceId;
                        cmd.Parameters.Add(guidParameter);
                        cmd.Connection = con;
                        con.Open();
                        reader = cmd.ExecuteReader();
                        //Get the reader to retrieve the data
                        while (reader.Read())
                        {
                            //Use memory stream and reflection to get the data
                            SqlBinarybinData = new SqlBinary((byte[])reader["imgPart"]);
                            MemoryStream stream = new MemoryStream(binData.Value);
                            AssemblypipelineAssembly = Assembly.LoadFrom(string.Concat(@"C:\Program Files\Microsoft BizTalk Server 2006", @"\Microsoft.BizTalk.Pipeline.dll"));
                            TypecompressionStreamsType = pipelineAssembly.GetType("Microsoft.BizTalk.Message.Interop.CompressionStreams", true);
                            StreamReader st = new StreamReader((Stream)compressionStreamsType.InvokeMember("Decompress", BindingFlags.Public | BindingFlags.InvokeMethod | BindingFlags.Static, null, null, new object[] { (object)stream }));
                            message = st.ReadToEnd();
                        }
                    }
                    finally
                    {
                        con.Close();
                    }
                    return message;
                }
                catch (ExceptionexSQL)
                {
                    return "Failed to get message with id "+ MessageInstanceId.ToString() + " from tracking database: "+ exSQL.Message;
                }
            }

    Source: Thiago Almeida's Blog


    My Technet Articles

    If you like this or another reply, vote it up!
    If you think this or another reply answers the original question, mark it or propose it as an answer.


    Mauricio Feijo
    www.mauriciofeijo.com


    • Edited by Mauricio Feijo Wednesday, November 23, 2016 1:07 PM
    • Marked as answer by FrankHaarlem Thursday, November 24, 2016 9:58 AM
    Wednesday, November 23, 2016 1:04 PM