FTP Jobs

[Michael Kennedy]

Hi,

I'm trying to create a report that contains all of our FTP jobs. So far I've pulled data from OH,AH, and JFA. I think I have most of the jobs. However I am still missing a vast majority of the major information I need. I'm trying to create something that contains the source location, destination location, file name, where the file came from, and where the file is going. When the job was created and when it was last ran would also be beneficial. For example the most complete information I've been able to pull around AH table includes.

AH_IDNR, AH_OH_IDNR, AH_HOSTDST, AH_FILENAMEDST, AH_HOSTSRC, AH_FILENAMESRC, AH_NAME

I believe the vast majority of my missing information will be stored in the field related to process information. It's basically a script of commands used to complete the file transfer within UNIX. 

Based on what I found in this documentation, it looked like it should be table JPOP under JPOP_Value but this table is empty. We are currently on, Automic Web Interface 21.0.7. 

Thanks,
Michael

[Stephan Schratzberger]

Hi Michael,

if you are looking for the content of the (pre/post)process tab you can find it in the table OT (https://docs.automic.com/documentation/webhelp/english/AA/21.0/AE/21.0/DB%20Schema/db/_structure/HTML/OT.html).

Best Regards

Stephan

[Jared Kessans]

If you are looking for file transfer objects (JOBF), then you might use something like the following query which can be modified as needed.

-- With Client
with host_ip (name, ip, port)
as
(
select AH_Name name , host_tcpipaddr ip, host_tcpipport port
from AH inner join host on host_oh_idnr = AH_OH_Idnr
)

select distinct AH_client as 'Client', ah_name as 'Name', 
AH_HostSrc as 'Host Source', hip1.ip 'Source Ip', hip1.port 'Source Port', AH_FileNameSrc,
AH_HostDst as 'Host Dest', hip2.ip 'Dest IP', hip2.port 'Dest Port', AH_FileNameDst
from ah, JFA, host_ip hip1, host_ip hip2
where (AH_OH_Idnr = JFA_OH_IDNR) 
and AH_HostSrc = hip1.name
and AH_HostDst = hip2.name
--and ah_client NOT IN ('0001','0002','0003','0004')
and AH_DeleteFlag = '0'
--and (AH_HostSrc in ('host1') or AH_HostDst in ('host2'))
--and AH_TimeStamp4 > '2017-03-01 00:00:00.000'
order by AH_HostSrc, AH_HostDst