CAWA DE - looking for a SQL to identify all FTP job definitions

[Kenneth Chrapczynski]

Hello, Does anyone have an SQL, or any other method to capture all FTP job definitions?

When I query the [ESP_AMS_DF] table, it includes the template, which is in every Application. Is there a different table I should be searching? There is no meaningful information returned in the [ESP_GENERIC_JOB] table. 

------------------------------
DARDEN Corporation
Orlando, FL
------------------------------

[Ravi Kiran Kunduri]

can you please try FindJob in Desktop Client with 'Job Type' as FTP 

[Ravi Kiran Kunduri]

You can use jobmap cli command to get that job definition - 

-> jobmap events("IT.JOB_TYPES") jobs("FTP*")

Event Name:IT.JOB_TYPES
-----------------------------------------------------------
 Application Name:JOB_TYPES
===========================================================
  Agent Name:ESP_G36R0T3_37500
  Execution Time(min):0.0
  Anticipated End Time:10:50:13 Mon 19 Aug 2024
  
***********************************************************
    Job Name:FTP
    Job Type:FTP
    Predecessors:MISCELLANEOUS
    Transfer Direction:DOWNLOAD
    Run Schedule:DAILY
    Auto-Resubmit failed job:RetryCount(0) RetryInterval(0) 
    User Name:x
    Life Time(min):0.0
    Server Address:x
    Anticipated End Time:10:50:13 Mon 19 Aug 2024
    Transfer Type:B
    Agent Name:AGENT
    Notification Alert:Notify1(Monitorstates("SUBERROR,FAILED") Alert(COMPLETE_SELF))
    Execution Time(min):0.0
    Remote File Name:x
    Local File Name:x
    Anticipated Start Time:10:50:13 Mon 19 Aug 2024
    Last Run:Tue Jul 16 14:10:27 IST 2024

***********************************************************

[Kenneth Chrapczynski]

Hi,

I tried this command, but none of the jobs have FTP in the jobname, so I wasn’t getting any results.


Using command: jobmap events("eventprefix.eventname") jobs("*") on a specific EVENT I was able to see all the jobs in this Application.



Here is the information returned for one of my FTP jobs. Is there a way to key on the “job type” or “qualifier”?

[cid:image001.png@01DAF207.87DC1010]



Also, I was able to eventually use SQL by keying on the “serverport” value since that field isn’t part of the defaults in the template.



<ftp_job name="DEFAULT">

<dependencies></dependencies>

<transferdirection>DOWNLOAD</transferdirection>

<serveraddress>Insert_value_here</serveraddress>

<remotename>Insert_value_here</remotename>

<localname>Insert_value_here</localname>

<userid>Insert_value_here</userid>

<transfertype>Insert_value_here</transfertype>

</ftp_job>



Here is the command I use to find all the Applications that have FTP Jobs defined in them:
SELECT [ARTIFACTTYPE]
,[IDENTIFIER]
,[VERSION]
,[DEFINITION]
,[MODIFIEDTIME]
,[MODIFIEDUSER]
,[LOCKTIME]
,[LOCKUSER]
,[LOCKID]
,[VERSION_COMMENTS]
FROM [database_name].[xxx].[ESP_AMS_DF]

where [ARTIFACTTYPE] like 'APPLICATION' AND [database_name].[xxx].[ESP_AMS_DF].definition like '%<serverport>1234</serverport>%';

[Ravi Kiran Kunduri]

Yes, jobmap support wild card characters and based on qualifier. 

For example , i want to search all jobs that have qualifier starting with 'NO'

 

-> jobmap events("*") jobs("*.NO*")

 

Event Name:IT.MAIN

-----------------------------------------------------------

 Application Name:MAIN

===========================================================

  Agent Name:ESP_G36R0T3_37500

  Execution Time(min):0.65396667

  Anticipated End Time:15:50:08 Tue 20 Aug 2024

  

***********************************************************

    Job Name:TIME_DEPENDENCIES

    Job Type:LINK

    Predecessors:ROOT.JOBS

    Run Schedule:daily

    Life Time(min):0.0

    Anticipated End Time:15:50:08 Tue 20 Aug 2024

    Agent Name:ESP_G36R0T3_37500

    Execution Time(min):0.0

    Qualifier:NOTIFICATIONS

    Anticipated Start Time:15:50:08 Tue 20 Aug 2024

    

 

***********************************************************

 

Event Name:IT.TIME_DEPENDENCIES

-----------------------------------------------------------

 Application Name:TIME_DEPENDENCIES

===========================================================

  Agent Name:ESP_G36R0T3_37500

  Execution Time(min):0.0

  Anticipated End Time:15:49:30 Tue 20 Aug 2024

  

***********************************************************

    Job Name:READY

    Job Type:TASK

    Predecessors:NOTIFICATION.CHECKS

    Run Schedule:daily

    Life Time(min):0.0

    Anticipated End Time:15:49:30 Tue 20 Aug 2024

    Agent Name:ESP_G36R0T3_37500

    Notification Alert:Notify1(Monitorstates(READY) Alert(COMPLETE_SELF))

    Execution Time(min):0.0

    Qualifier:NOTIFICATION

    Anticipated Start Time:15:49:30 Tue 20 Aug 2024

    

 

***********************************************************

[Kenneth Chrapczynski]

Hi Ravi,

This works. Thanks!

------------------------------
DARDEN Corporation
Orlando, FL
------------------------------