Skip to Content

If you want to overview details of your SAP XI systems, so it would help you in some cases – http://hixay.googlecode.com/files/XID_CC_REPORT_2009-05-28.xls

This is simple excel workbook with macros which can ask SAP XI systems through standard web-services. You can write down your login info and list of systems to the first sheet, select the second one and press the “Ask the XI” button. After some pause you’ll get the brief info about communication channels and full-featured info at the third worksheet.

h2. Limitations and prerequisites

On the client side, this excel macro works as VBA macro only in Excel/Windows and uses Microsoft’s library MS XML v6.0, which can be downloaded from http://www.microsoft.com/downloads/details.aspx?FamilyID=993c0bcf-3bcf-4009-be21-27e85e1857b1&displaylang=en .

For the server side, XI/PI system with Integration Directory API is only required. These are XI3.0 SPS>=21, PI7.0 SPS>

=13 or PI7.1, according to Directory API Development – Part 1 of 3 . Currently this macro was tested on 70SPS16, 70SPS18 and 71SPS8 without any errors.

h3. What will you get as result?

It is a table, something like this:
!http://hixay.googlecode.com/svn/trunk/doc/excel/excel_pic08.png|alt=See result|src=http://hixay.googlecode.com/svn/trunk/doc/excel/excel_pic08.png!

For me and my colleguaes, there were need in report built on three PI systems, where we could search or filter all communication channels and their detail: who and when changed last time, what is the current state, most important address or URL, login name, etc.

h3. How to use, step-by-step

Download and open the macro with appropriate security level

 

Because of viruses, many computers ban spreadsheets with active content. You should allow macroses, either choosing appropriate button at start: !http://hixay.googlecode.com/svn/trunk/doc/excel/excel_pic00.png|alt=Excel security alert window|src=http://hixay.googlecode.com/svn/trunk/doc/excel/excel_pic00.png!

or with touching by menu (Service -> Macros -> Security levels): !http://hixay.googlecode.com/svn/trunk/doc/excel/excel_pic01.png|alt=Excel security settings|src=http://hixay.googlecode.com/svn/trunk/doc/excel/excel_pic01.png!

Of course you have to turn MSXML library on. For that, download and install it if required and swith to excel. Press Alt+F11, then Tools -> References, then select this library: !http://hixay.googlecode.com/svn/trunk/doc/excel/excel_pic02.png|alt=MSXML reference|src=http://hixay.googlecode.com/svn/trunk/doc/excel/excel_pic02.png!

h3. Configuring source systems

Initially after loading, you get this default sheet with list of source systems and some references to help information about adding required rights for username, as it was said in API documentation: First sheet
You should write required attributes of systems down, for example: Add your system(s)
There are several columns which could be used for various needs:

    • Disabled – mark here ‘X’ if you want to eliminate reporting from this system for while;
    • Use cache – mark here ‘X’ if you want to use cached answers from XI system. All the requests are cached but results used only if this mark is switched on for given system;
    • hostname, sysnr, user – it’s clear, yeah?
    • password – you can write here password but it’s unsecure. So if the field is blank, excel will ask for the password in pop-up window;
    • base port – you can use either 8xxx or 5xxxx-based requests;
    • url – this is target automatically-built url which is really used for asking through web-services

h3. Request the data

Switch to second sheet and press the button down: Ask XI
Then, popup will appear if password wasn’t set in first sheet:!http://hixay.googlecode.com/svn/trunk/doc/excel/excel_pic07.png|alt=Input password if it|src=http://hixay.googlecode.com/svn/trunk/doc/excel/excel_pic07.png!
After some pause (and if no errors in authorisation occurs) you’ll get the result as it was said before .
But additionally at the third sheet you can configure or just look at source (non-formatted raw) information about channels: You can get additional information      contains all answers for this system. For example: Cache (temporary files)
One file is just ordinary XML which you can use your own way: Content of one cached file with details about communication channel
If you set ‘Use proxy’ field, then these files will be used as source data.

h2. How it works? How can you customize this?

Initially I tried to use MS Soap Client but it failed with SAP wsdl’s. So after some thinking I started make requests by hand and analyse answers with simple XPath. The macro sends ordinary HTTP request to XI using this code (see SendRequest function):

Private Function SendRequest(src As String, xis As TXISystem, Optional ByRef statusCode As Integer, Optional ByRef statusText As String) As String
Dim port As New MSXML2.XMLHTTP60
On Error GoTo 1
Call port.Open(“POST”, xis.url, False, xis.uname, xis.password)
Call port.setRequestHeader(“User-Agent”, “Excel”)
Call port.setRequestHeader(“SOAPAction”, “”””)
Call port.setRequestHeader(“Content-Type”, “text/xml;charset=UTF-8”)
Call port.send(src)
SendRequest = port.responseText
statusCode = port.Status
statusText = port.statusText
Exit Function
1:
SendRequest = “”
statusCode = -1
statusText = “Unknown”
End Function

So, it’s really easy just parse XMLed feedback in code — some handly-written WSDL proxy.

Here all the functions with some remarks:

    1. init_wbk – setup templates of requests and parse list of systems
    2. refresh_clist_manual – make human-readable report on second sheet from raw data on the third sheet
    3. refresh_xi – loop at all systems, fill the third sheet down, call refresh_clist_manual
    4. SendRequest – just make HTTP request
    5. retrieve – retrieve information from XI or from file, applying user-defined XPath’s to file and put results to AChannelDetails array
    6. publish_on_sheet – write AChannelDetails array to third sheet
    7. ClaimError – to pop-up the error message

h3. XPath is very powerful tech

Well, why I have used XPath and told about it so much? Because it’s really good in XML addresses here. The third sheet (“raw”) contains just XPath expressions, for example: //pns0:AdapterSpecificAttribute[pns0:Name=’adapterStatus’ or pns0:Name=’file.adapterStatus’]/pns0:Value for getting the adapter’s state either non-file and file. Human-readable report is built from references to these fields and you can customize it with your own way.

h2. What the next?

SAP said that we can use these services so we really do that. Excel reporting and communication channels aren’t the only goal, but there are people who want to automate routine jobs and to make easier our work in XI.

[Hixay | http://code.google.com/p/hixay/] is project aimed for this ideas. Our first goal is to make many reports (as much as possible) and to combine data from various source (SLD, ID, RWB, user-defined sources) into smart and handy visualisation. Excel macro was just “proof-of-concept” and demonstration of possibilities, not our current work. Hixay has been written from scratch, it’s standalone program (and you use it under Windows, Linux and other mainstream OSes — everywhere Haskell GHC could compile it), and it will produce some interesting reports and even could make changes in XI through API.

Today we can get some graphs, for example see one scenario and big picture . They are built fully automatically from 7.1 and 7.0 systems correspondingly. There are proof-of-concept too, and part of chains aren’t handled correctly yet. Some of errors are here (one communication channel for two systems for example). Also, plain-text script is produced (configuration scenario-based view), but this needs a lot of work before getting stable and good-looking reports of course.

I hope, somebody could find this approach for automating XI and producing documentation interesting a bit.

To report this post you need to login first.

10 Comments

You must be Logged on to comment or reply to a post.

  1. George Ardeleanu
    Hi,

    This is a great resource explaining several concepts. For one, I wondered in the past how to get the comm channel via code. This answers my question perfectly, including additional details and a very useful example.

    Great work Iliya!

    Regards,

    George

    (0) 
  2. Daniel Graversen
    Hi,
    I really like the images you have created in hixay. They could be used to create a good documentation of what is going on in the system.

    I can see a potential in using the tool to, make a difference of what has changed since ie. yesteday or make deployement planing.

    /regards
    Daniel

    (0) 
  3. Amol Joshi
    hi,

    which are the authorizations required for making this work?

    i got a 500 internal server error and when tracked it using wsnavigator and performing a test there, i realized that my user dint have authorization to execute the operations of this ws which are read/ create etc.
    i got the below error

    User XXX does not have access to method read.

    (0) 
    1. Iliya Kuznetsov Post author
      Currently we focused on development of 0.1 version where xml and png/graphviz reporting is planned (RWB/SLD are the next goals) and 0.1 will be published as downloadable binary …. hmmm don’t know when and I am humble to plan it strictly.

      Just now you can get all the sources via http://code.google.com/p/hixay/source/checkout and compile it using GHC (see haskell.org), then run hix_c and MakeGraph.

      Currently making of graphs has some errors/lacks and we are thinking how to fix it because not all the information can be get from ID when sender agreement isn’t obligatory (for IDoc, HTTP, XI, SOAP and Mail sender channels Sender agreement is not obligatory) .

      (0) 
  4. Glen Anthony

    Hi Lliya,

    i realise this post is from 2009 so not sure if this is still applicable but wanted to ask; I am struggling to logon via this. I get message logon failed.

    Any help greatly appreciated. Thank you.

    (0) 
    1. Iliya Kuznetsov Post author

      Hello Glen,

      Have you provided such authorization roles for your login? Nowadays default SAP_XI_CONFIGURATOR_J2EE is not enough.

      Regards, ILIYA KUZNETSOV

      (0) 
      1. Glen Anthony

        Hi Iliya,

        Thank you for your reply. I have managed to get past the logon error message by pasting the url into the spreadsheet i.e. column J as opposed to hostname and port but spreadsheet not getting populated?

        The below is the UME which i believe is for my user:

        ume.PNG

        (0) 

Leave a Reply