1 2 Previous Next 21 Replies Latest reply on Mar 15, 2018 8:34 AM by Jan Persson

    Whitewashing XML list

    Jan Persson

      Hi,

      I'm collecting Requests from ITSM and then converting the XML list to JSON.

      For this to work I need to remove all forbidden characters and are doing so with a transform.

      The characters only appears in the fields where characters can be added manually,

      for instance in the Notes and Summary fields.

       

      If I have a big list it takes a long time for the transform to complete, 500 Requests would roughly take 3min..

       

      Does anyone know if there a way to run this clean up in a faster way, like splitting the list up and running the clean up in parallel processes or such?

       

      Regards

      /Jan

        • 1. Re: Whitewashing XML list
          Henrik Hauchwitz

          Hi Jan,

           

          do you have the xml data available as files, so to say stored on a filesystem and then proccessed, or is the data only available during workflow execution?
          If you do have it available as a file, I'd reccomend doing the transform on the host itself, not within the workflow. (using something like sed/awk to replace certain strings)

           

          BAO is known to strugle with larger xml structures. I seem to remember BMC recomending not going above 20kb of xml data.
          I've had similiar issues with workflows even getting stuck and at some point crashing the CDP due to insane workloads.

           

          Best regards,
          Henrik

          • 2. Re: Whitewashing XML list
            Aryan Anantwar

            Just to add what Henrik suggested, you can use Atrium Integrator and create spoon jobs to perform the transforms.

            you can also run the spoon jobs from AO workflows using command line adapter.

            In one of the integrations I have seen issues with big XML data processing in BAO workflow, so we did the big XML processing in spoon job and then used the transformed XMLs.

             

            Regards,

            Aryan Anantwar

            • 3. Re: Whitewashing XML list
              Ranganath Samudrala

              Can you provide sample data to see what exactly you are doing?

              • 4. Re: Whitewashing XML list
                Jan Persson

                Hi, please find a XML Sample below.

                In the "Description" and "Detailed Description" fields there are characters which are not allowed when converting XML to JSON.

                These fields needs to be cleaned from those characters. When I have a list of 100 or more the execution to clean these fields takes over 1min.

                I have to go through each and every Request using a while loop and then go through the Requet fields with a for loop to search for characters and remove them if necessary using a transform.

                 

                <Request-collection>

                  <Request>

                    <query-action-result>

                      <entries>

                        <metadata>

                          <entry-count>4</entry-count>

                        </metadata>

                        <entry id="WODE00000745302|WODE00000745302">

                          <field name="Request ID">WODE00000745302|WODE00000745302</field>

                          <field name="Work Order ID">WOND00001195918</field>

                          <field name="Region" />

                          <field name="Organization">PT</field>

                          <field name="Company">Test</field>

                          <field name="Department">Portugal - Security (T2T3)</field>

                          <field name="Location Company">Test</field>

                          <field name="Customer Last Name">TestUser3</field>

                          <field name="Customer First Name">User3</field>

                          <field name="Customer Organization">PT</field>

                          <field name="Customer Phone Number"></field>

                          <field name="Customer Department">Portugal - Security (T2T3)</field>

                          <field name="Customer Company">Test</field>

                          <field name="Company3">Test Support</field>

                          <field name="Support Organization">Global-ITSM-SS</field>

                          <field name="Support Group Name">ITSM-SS Projects</field>

                          <field name="Detailed Description">

                 

                 

                Project Code         : TEST

                 

                 

                Project Activity        : TEST

                 

                 

                Country             : Finland

                 

                 

                Request Summary         : TEST

                 

                 

                Request Details : TEST

                 

                 

                Process/Applications affected by this change  : Service Request Portal

                 

                 

                Client(s)         : TEST

                 

                 

                Request by date        : 2.2.2018 12:00 AM</field>

                          <field name="Description">OneITSM Add-hoc requests</field>

                          <field name="Status">7</field>

                          <field name="Status Reason" />

                          <field name="Priority">3</field>

                          <field name="First Name">User3</field>

                          <field name="Last Name">TestUser3</field>

                          <field name="Phone Number"></field>

                          <field name="Work Order Type">2000</field>

                          <field name="Product Name (2)">Nordics DR2 OneITSM Service</field>

                          <field name="Product Model/Version (2)" />

                          <field name="ASCHG" />

                          <field name="InstanceId">IDHAAFWIHFIECAPD8WN9AASOPQANB5</field>

                          <field name="TemplateID">IDHAAFWIHFIDSAOFDTIUA33VVUCSM6</field>

                          <field name="SRID">REQND0001275701</field>

                          <field name="Customer Person ID">PPLND0000625880</field>

                          <field name="Submit Date">1517575379</field>

                          <field name="Customer Internet E-mail">user3.testuser3@Test.com</field>

                        </entry>

                        <entry id="WODE00000746104|WODE00000746104">

                          <field name="Request ID">WODE00000746104|WODE00000746104</field>

                          <field name="Work Order ID">WOND00001198038</field>

                          <field name="Region" />

                          <field name="Organization">FR</field>

                          <field name="Company">Test</field>

                          <field name="Department">Energy Utilities Telcoms</field>

                          <field name="Location Company">Test</field>

                          <field name="Customer Last Name">TestUser4</field>

                          <field name="Customer First Name">User4</field>

                          <field name="Customer Organization">FR</field>

                          <field name="Customer Phone Number">0123456</field>

                          <field name="Customer Department">Energy Utilities Telcoms</field>

                          <field name="Customer Company">Test</field>

                          <field name="Company3">Test Support</field>

                          <field name="Support Organization">Global-ITSM-SS</field>

                          <field name="Support Group Name">ITSM-SS 3rd Line Support</field>

                          <field name="Detailed Description">What is the issue?

                 

                 

                I can not reach the inIT for external users via the link:

                 

                 

                 

                 

                 

                 

                https://arspext.han.telia.se/arsys/forms/arsprod/inIT

                 

                 

                 

                 

                 

                 

                I get login problem.

                 

                 

                 

                 

                 

                 

                look in the attached file for more info

                 

                 

                Enter the error message:

                 

                 

                 

                 

                 

                 

                Have you tried to solve the issue by yourself?

                 

                 

                 

                 

                 

                 

                If this ticket refers to e.g: equipment, orders,  accounts or servers,  please enter adequate information:

                 

                 

                 

                 

                 

                 

                Do you have access to your e-mail while your case is handled by Service Desk?

                 

                 

                 

                 

                 

                 

                Preferred way of contact:

                 

                 

                Brevduva</field>

                          <field name="Description">inIT for external user - login problem</field>

                          <field name="Status">0</field>

                          <field name="Status Reason" />

                          <field name="Priority">3</field>

                          <field name="First Name">User4</field>

                          <field name="Last Name">TestUser4</field>

                          <field name="Phone Number">0123456</field>

                          <field name="Work Order Type">2000</field>

                          <field name="Product Name (2)" />

                          <field name="Product Model/Version (2)" />

                          <field name="ASCHG" />

                          <field name="InstanceId">IDHAAFWIHFIDSAPEGO80C4BVNHJDMK</field>

                          <field name="TemplateID">IDHAAFWIHFIDSANX0NPXCZ74T471TE</field>

                          <field name="SRID">REQND0001277494</field>

                          <field name="Customer Person ID">PPLND0000615384</field>

                          <field name="Submit Date">1518684566</field>

                          <field name="Customer Internet E-mail">user4.testuser4@Test.com</field>

                        </entry>

                        <entry id="WODE00000746108|WODE00000746108">

                          <field name="Request ID">WODE00000746108|WODE00000746108</field>

                          <field name="Work Order ID">WOND00001198044</field>

                          <field name="Region" />

                          <field name="Organization">FR</field>

                          <field name="Company">Test</field>

                          <field name="Department">Energy Utilities Telcoms</field>

                          <field name="Location Company">Test</field>

                          <field name="Customer Last Name">TestUser4</field>

                          <field name="Customer First Name">User4</field>

                          <field name="Customer Organization">FR</field>

                          <field name="Customer Phone Number">0123456</field>

                          <field name="Customer Department">Energy Utilities Telcoms</field>

                          <field name="Customer Company">Test</field>

                          <field name="Company3">Test Support</field>

                          <field name="Support Organization">Global-ITSM-SS</field>

                          <field name="Support Group Name">ITSM-SS 3rd Line Support</field>

                          <field name="Detailed Description">What is the issue?

                 

                 

                IISreset-testIISreset-testIISreset-testIISreset-testIISreset-test

                 

                 

                Enter the error message:

                 

                 

                IISreset-test error msg

                 

                 

                Have you tried to solve the issue by yourself?

                 

                 

                IISreset-test

                 

                 

                If this ticket refers to e.g: equipment, orders,  accounts or servers,  please enter adequate information:

                 

                 

                IISreset-test

                 

                 

                Do you have access to your e-mail while your case is handled by Service Desk?

                 

                 

                IISreset-test

                 

                 

                Preferred way of contact:

                 

                 

                Brevduva</field>

                          <field name="Description">IISreset-test appli</field>

                          <field name="Status">0</field>

                          <field name="Status Reason" />

                          <field name="Priority">3</field>

                          <field name="First Name">User4</field>

                          <field name="Last Name">TestUser4</field>

                          <field name="Phone Number">0123456</field>

                          <field name="Work Order Type">2000</field>

                          <field name="Product Name (2)" />

                          <field name="Product Model/Version (2)" />

                          <field name="ASCHG" />

                          <field name="InstanceId">IDHAAFWIHFIDSAPEGPNYC4YAZQJHFH</field>

                          <field name="TemplateID">IDHAAFWIHFIDSANX0NPXCZ74T471TE</field>

                          <field name="SRID">REQND0001277500</field>

                          <field name="Customer Person ID">PPLND0000615384</field>

                          <field name="Submit Date">1518686065</field>

                          <field name="Customer Internet E-mail">user4.testuser4@Test.com</field>

                        </entry>

                        <entry id="WODE00000746110|WODE00000746110">

                          <field name="Request ID">WODE00000746110|WODE00000746110</field>

                          <field name="Work Order ID">WOND00001198047</field>

                          <field name="Region" />

                          <field name="Organization">FR</field>

                          <field name="Company">Test</field>

                          <field name="Department">Energy Utilities Telcoms</field>

                          <field name="Location Company">Test</field>

                          <field name="Customer Last Name">TestUser4</field>

                          <field name="Customer First Name">User4</field>

                          <field name="Customer Organization">FR</field>

                          <field name="Customer Phone Number">0123456</field>

                          <field name="Customer Department">Energy Utilities Telcoms</field>

                          <field name="Customer Company">Test</field>

                          <field name="Company3">Test Support</field>

                          <field name="Support Organization">Global-ITSM-SS</field>

                          <field name="Support Group Name">ITSM-SS 3rd Line Support</field>

                          <field name="Detailed Description">What is the issue?

                 

                 

                IISreset-testIISreset-testIISreset-test

                 

                 

                Enter the error message:

                 

                 

                IISreset-testIISreset-testIISreset-test

                 

                 

                Have you tried to solve the issue by yourself?

                 

                 

                IISreset-testIISreset-testIISreset-test

                 

                 

                If this ticket refers to e.g: equipment, orders,  accounts or servers,  please enter adequate information:

                 

                 

                IISreset-testIISreset-testIISreset-test

                 

                 

                Do you have access to your e-mail while your case is handled by Service Desk?

                 

                 

                IISreset-testIISreset-test

                 

                 

                Preferred way of contact:

                 

                 

                Brevduva</field>

                          <field name="Description">IISreset-test</field>

                          <field name="Status">0</field>

                          <field name="Status Reason" />

                          <field name="Priority">3</field>

                          <field name="First Name">User4</field>

                          <field name="Last Name">TestUser4</field>

                          <field name="Phone Number">0158719</field>

                          <field name="Work Order Type">2000</field>

                          <field name="Product Name (2)" />

                          <field name="Product Model/Version (2)" />

                          <field name="ASCHG" />

                          <field name="InstanceId">IDHAAFWIHFIDSAPEGPQIC4ZRP2JIDA</field>

                          <field name="TemplateID">IDHAAFWIHFIDSANX0NPXCZ74T471TE</field>

                          <field name="SRID">REQND0001277703</field>

                          <field name="Customer Person ID">PPLND0000615384</field>

                          <field name="Submit Date">1518686146</field>

                          <field name="Customer Internet E-mail">user4.testuser4@Test.com</field>

                        </entry>

                      </entries>

                    </query-action-result>

                  </Request>

                </Request-collection>

                • 5. Re: Whitewashing XML list
                  Jan Persson

                  The data is available during workflow execution..

                  • 6. Re: Whitewashing XML list
                    Ranganath Samudrala

                    Which character(s) would you like to replace?

                    • 7. Re: Whitewashing XML list
                      Jan Persson

                      Hi,

                      My regex expression to remove characters looks like this,

                      ('|"|\\|\t|<|>|&)

                       

                      /Jan

                      • 8. Re: Whitewashing XML list
                        Ranganath Samudrala

                        Seems like "Request-collection" does not contain those special characters.. was this data already processed through AO workflow before it was posted here?

                        • 9. Re: Whitewashing XML list
                          Ranganath Samudrala

                          Try the workflow in the module to pass your XML and replace special characters. Let me know how the performance is.

                          • 10. Re: Whitewashing XML list
                            Jan Persson

                            Hi Ranganath,

                            Using your workflow, I can see that the characters are being replaced but the original "Detailed Description" field are still i the XML aswell a the

                            same field without forbidden characters.

                             

                            /Jan

                            • 11. Re: Whitewashing XML list
                              Ranganath Samudrala

                              I am not sure how you were performing regex. I gave a way to do this - may be faster than how it was being done before. But, once unwanted characters are removed, you need to invoke 'Convert XML to JSON' workflow that exists in AutoPilot-OA-Common_Utilities module.

                               

                              Of course, you need to take out contents of the Description and Detailed Description fields apply regex and replace the existing text with the latest text.

                              • 12. Re: Whitewashing XML list
                                Jan Persson

                                Hi Ranganath,

                                Yeah I think what takes time is to replace the existing field data with the new cleaned data.

                                The way I have done it is to take out each and every field and recreate the entry with new fieldnames like below sample, (just added a few fields as an example).

                                The only two fields I'm cleaning is the "Detailed Description" and the "Description".

                                 

                                <Changes>

                                <Change>

                                   <request-id>WODE00000746104</request-id>

                                   .

                                   .

                                    <detailed-description>Cleaned data</detailed-description>

                                    <description>Cleaned data</description>

                                   .

                                   .

                                  </Change>

                                </Changes>

                                 

                                /Jan

                                • 13. Re: Whitewashing XML list
                                  Ranganath Samudrala

                                  Can you show XSLT transform you are using?

                                  • 14. Re: Whitewashing XML list
                                    Ranganath Samudrala

                                    Please try this version. It should create description and detailed description correctly. They will have an extra attribute, but I am guessing you can live with it.

                                    1 of 2 people found this helpful
                                    1 2 Previous Next