8 Replies Latest reply on Jun 6, 2018 1:28 PM by marc.see

    Activity log counter for group transfers

    marc.see Apprentice

      This is a followup on a discussion from this post modifying Xtraction to provide Activity Log information

       

      I originally posed the question in order to find out how to get the number of group transfers.GreggSmith answers that there is a table already in place called "Group_Transfer_Count" for incidents.

       

      The scripting for the group transfer count goes like this:

       

           (SELECT call_req_id, COUNT(0) [transfer_count] FROM MDBADMIN.act_log WHERE type = 'TR' and action_desc LIKE '%transfer ''group''%' GROUP BY call_req_id)

       

      Running a report using this field results in an error. I first thought it was because the script uses square brackets for [transfer_count], since we use Oracle for our database, but it is still producing errors. Here's what is says on the log file:

       

      System.Data.OracleClient.OracleException (0x80131938): ORA-00904: "GROUP_TRANSFER_COUNT"."TRANSFER_COUNT": invalid identifier

       

         at System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle errorHandle, Int32 rc)

         at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, Boolean needRowid, OciRowidDescriptor& rowidDescriptor, ArrayList& resultParameterOrdinals)

         at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, ArrayList& resultParameterOrdinals)

         at System.Data.OracleClient.OracleCommand.ExecuteReader(CommandBehavior behavior)

         at System.Data.OracleClient.OracleCommand.ExecuteDbDataReader(CommandBehavior behavior)

         at 0.1E.0(String , String , String , String , Int32 )

         at 0.1E.0(String , String , String , String )

         at 0.1K.0(GroupComponentStandardSeries , Int32 , ComponentParameterCollection )

         at 0.1K.0(GroupComponentStandardSeries , Int32 , IndexedDataTable , ComponentParameterCollection )

         at 0.1K.0(GroupComponent )

         at 0.1K.GetDataXml(GroupComponent component)

         at SqlDataEngine.Services.Components.GroupComponentService.GetDataXml(GroupComponent component)

         at Xtraction.Web.Services.SqlDataEngineWebService.GetGroupComponentData(GroupComponent component)

       

      Has anyone encountered this issue?

        • 1. Re: Activity log counter for group transfers
          GreggSmith SupportEmployee

          What is the error?

          • 2. Re: Activity log counter for group transfers
            marc.see Apprentice

            I edited my original post.

            • 3. Re: Activity log counter for group transfers
              marc.see Apprentice

              I think I found a solution. I modified the original script to:

               

                   (SELECT call_req_id, COUNT(0) as transfer_count FROM MDBADMIN.act_log WHERE type = 'TR' and action_desc LIKE '%Transfer Group%' GROUP BY call_req_id)

               

              It currently works now without any errors. Can you confirm GreggSmith on your end?

              • 4. Re: Activity log counter for group transfers
                GreggSmith SupportEmployee

                You said "I first thought it was because the script uses square brackets for [transfer_count], since we use Oracle for our database, but it is still producing errors." This would seem to indicate that you thought it might be because of the brackets because you are using Oracle, [so you made a change] but it is still producing errors [after you made the change].  If this is correct, what change did you make?

                • 5. Re: Activity log counter for group transfers
                  GreggSmith SupportEmployee

                  There you go.  I was going to say to change it from [transfer_count] to "transfer_count" but as transfer_count works as well.

                  • 6. Re: Activity log counter for group transfers
                    marc.see Apprentice

                    Can you help me fine tweak this? When I was retesting, I found a ticket that had the following log transfer values:

                     

                        

                    If you notice, the ticket was transferred to a group 4 times, but Xtraction counted it as 3 times. I also didn't realize that Oracle was case sensitive. So changed the script:

                     

                         (SELECT call_req_id, COUNT(0) as transfer_count FROM MDBADMIN.act_log WHERE type = 'TR' and (action_desc LIKE '%Transfer group%' or action_desc LIKE '%Transfer Group%') GROUP BY call_req_id)

                     

                    With this change, now it counts it as 5 times group transfer.

                    • 7. Re: Activity log counter for group transfers
                      GreggSmith SupportEmployee

                      Looking at your screenshot, it was transferred 5 times, so that would be the correct result.

                       

                      Hint: The Description column in your screenshot is the user description field, which will show the user's description if they provided one and the system description if they did not. Personally, my thought is if the user didn't enter a description, the user description should be blank; but CA Service Desk has other ideas and fills it in with the contents of the system description field. Rows 2, 3, 4, and 6 are Group Transfers where they did not provide a description, so you see the system description.  Row 7 is (I assume) a Group Transfer where you are seeing the user description.  From the text of the user's description, it is a good indication that it is also a group transfer.

                      • 8. Re: Activity log counter for group transfers
                        marc.see Apprentice

                        You're right! The script conditions for the field "action_desc", which is not the same as the description field in activity logs. For Service Desk, any user comments made for the ticket (status changes, etc.) are placed in the description field. If there are no user comments, the contents of the "action_desc" is put in place. Here's the extract of the example ticket which includes the action_desc field:

                         

                        Thanks for your help!