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

• AS2
• GIS
• SI