AS2 problems report (GIS)

Problem

Recently we had some problems with the outbound AS2 connection to one of our clients. I decided to check and analyze this situation and needed to gather information about all AS2 sessions between our servers. Our AS2 BP does 3 attempts to connect and if all of them failed, it sends an email to the support team.

Solution

I decided to use the GIS’s DB as the source for my report. I used the following tables:

  • WF_INST_S – info about BP instances
  • WORKFLOW_CONTEXT – detailed information about all the BP steps
  • WFD – info about BP’s name/version
  • WFD_VERSIONS – info about default version of BP
  • CORRELATION_SET – related information about used envelopes, AS2 connection settings and so on (depends on the BP)

First of all, I needed to find all the instances of AS2 sessions during some period of time. To do it, I needed to know the ID and version of BP, so I ran the following SQL query (I’ve replaced real BP/URL and other values with dummy ones):

SELECT * FROM WFD w, WFD_VERSIONS wv
WHERE 
  w.NAME = 'BP_Send_AS2'
  AND w.WFD_ID = wv.WFD_ID
  AND w.WFD_VERSION = wv.DEFAULT_VERSION

This query returned WF_ID = 111 and version = 3.

Next, I ran SQL query to get all the AS2 sessions for one day

select
  wis.workflow_id as WFID,
  TO_CHAR(wis.start_time, 'HH24:MI:SS') as WFTIME,
  count(wc.workflow_id)
from
  WF_INST_S wis
  left outer join WORKFLOW_CONTEXT wc on wis.workflow_id = wc.workflow_id and wc.BASIC_STATUS = 1
WHERE
 wis.start_time > TO_DATE('2013-08-08 00:00:01', 'yyyy-mm-dd HH24:MI:SS')
 and wis.start_time < TO_DATE('2013-08-08 23:59:59', 'yyyy-mm-dd HH24:MI:SS')
 and wis.WFD_ID = 111 and wis.WFD_VERSION = 3 -- AS2
group by wis.workflow_id,  wis.start_time
order by wis.start_time

And finally I changed it a little to filter particular customer out (its URL was http://customername.com/as2):

select
  wis.workflow_id as WFID,
  TO_CHAR(wis.start_time, 'HH24:MI:SS') as WFTIME,
  count(wc.workflow_id)
from
  WF_INST_S wis
  left outer join WORKFLOW_CONTEXT wc on wis.workflow_id = wc.workflow_id and wc.BASIC_STATUS = 1
WHERE
 wis.start_time > TO_DATE('2013-08-08 00:00:01', 'yyyy-mm-dd HH24:MI:SS')
 and wis.start_time < TO_DATE('2013-08-08 23:59:59', 'yyyy-mm-dd HH24:MI:SS')
 and wis.WFD_ID = 111 and wis.WFD_VERSION = 3 -- AS2
 and wis.workflow_id in (select WF_ID from correlation_set
		where
		  NAME = 'URL'
		  and VALUE = 'http://customername.com/as2'
		) 
group by wis.workflow_id,  wis.start_time
order by wis.start_time

I put all of this information into a MS Excel sheet, added a couple of graphs and got the clear picture of AS2 problems for that day:

Gennady Kim

Advertisements

8 thoughts on “AS2 problems report (GIS)

  1. I would like to extract the report data from the tables, but I don’t know which tables to look in. All I can find is the InstanceID on the report. I can use the WORKFLOW_LINKAGE table to cross reference the InstanceID to get the ProcessID, but that still doesn’t show me any of the report data. Any ideas?

    • unfortunately I don’t have access to GIS/SI now and cannot help. When I was working with SI, I used documentation + was making “educated guesses” to find information.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s