cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
1846
Views
0
Helpful
5
Replies

Query To Find a Job with a search string

i know that this can be done by using query to find a job with a search string. i need to find a job that that has this 'PERFEVAL.txt' as the keyword on the query. basically the job can be a normal job or FTP or SFTP job. we had hundreds of jobs and i attempted to look for each one manually and could not find them maybe i might missed. i know how to construct a query however because we do not have access to the tidal database i can't see what are the tables and columns to look for. please help what is the query to (tables and column) to find a job that will look for a particular string. it may be in the command, command parameter, filename, new filename, path, remote path, or notes. i send this query to our database group to run it for me.

thank you,

warren

1 Accepted Solution

Accepted Solutions

Parameters shoudl return several FTP related components deopending on selections

but original name PERFEVAL.txt and new name PERFEVAL_NEW.TX woudl be in there\

example:

0,"","PERFEVAL.txt","PERFEVAL_NEW.TXT",N,N,N

Add jobmst_desc and jobmst_runbook for notes

SELECT

dbo.jobmst.jobmst_id, dbo.jobmst.jobmst_prntname, dbo.jobmst.jobmst_name, dbo.jobmst.jobmst_type, dbo.jobdtl.jobdtl_cmd AS LOCAL_DIR,

dbo

.jobdtl.jobdtl_envfile AS REMOTE_DIR, dbo.jobdtl.jobdtl_extinfo AS FTP_PROTOCOL, dbo.jobdtl.jobdtl_workdir AS QUOTE,

dbo

.jobdtl.jobdtl_params AS FTP_CMDS, dbo.jobmst.jobmst_runbook AS Runbook, dbo.jobmst.jobmst_desc AS Notes

FROM

dbo.jobmst INNER

JOIN

dbo

.jobdtl ON dbo.jobmst.jobdtl_id = dbo.jobdtl.jobdtl_id

WHERE

(dbo.jobmst.jobmst_type > 2) AND (dbo.jobdtl.jobdtl_params LIKE '%PERFEVAL.txt%'

)

View solution in original post

5 Replies 5

Marc Clasby
Level 1
Level 1

try this I added what the table columns are used as from a definition window in case you need to search for other criteria

Marc

remember job groups are type 1 and jos are type 2 but FTP = 6

SELECT

dbo.jobmst.jobmst_id, dbo.jobmst.jobmst_prntname, dbo.jobmst.jobmst_name, dbo.jobmst.jobmst_type, dbo.jobdtl.jobdtl_cmd AS LOCAL_DIR, dbo.jobdtl.jobdtl_envfile as REMOTE_DIR,

dbo

.jobdtl.jobdtl_extinfo AS FTP_PROTOCOL, dbo.jobdtl.jobdtl_workdir AS QUOTE, dbo.jobdtl.jobdtl_params AS FTP_CMDS

FROM

dbo.jobmst INNER

JOIN

dbo

.jobdtl ON dbo.jobmst.jobdtl_id = dbo.jobdtl.jobdtl_id

WHERE

(dbo.jobmst.jobmst_type > 2) AND jobdtl_params like

'%PERFEVAL.txt%'

thank you. does the column jobdtl_params covers the items filename and new filename in the FTP job; and the command and command parameters in normal job including the notes tab?

Parameters shoudl return several FTP related components deopending on selections

but original name PERFEVAL.txt and new name PERFEVAL_NEW.TX woudl be in there\

example:

0,"","PERFEVAL.txt","PERFEVAL_NEW.TXT",N,N,N

Add jobmst_desc and jobmst_runbook for notes

SELECT

dbo.jobmst.jobmst_id, dbo.jobmst.jobmst_prntname, dbo.jobmst.jobmst_name, dbo.jobmst.jobmst_type, dbo.jobdtl.jobdtl_cmd AS LOCAL_DIR,

dbo

.jobdtl.jobdtl_envfile AS REMOTE_DIR, dbo.jobdtl.jobdtl_extinfo AS FTP_PROTOCOL, dbo.jobdtl.jobdtl_workdir AS QUOTE,

dbo

.jobdtl.jobdtl_params AS FTP_CMDS, dbo.jobmst.jobmst_runbook AS Runbook, dbo.jobmst.jobmst_desc AS Notes

FROM

dbo.jobmst INNER

JOIN

dbo

.jobdtl ON dbo.jobmst.jobdtl_id = dbo.jobdtl.jobdtl_id

WHERE

(dbo.jobmst.jobmst_type > 2) AND (dbo.jobdtl.jobdtl_params LIKE '%PERFEVAL.txt%'

)

does the jobdtl_params column represents all of the items in the job definition window? thanks.

thank you that helps.