Script for listing out jobs which are created but not used anymore

[Abhishek Nayak]

Hi Team,

I'm looking for a script that can extract job information from different clients. Specifically, I need details such as the client number, object name, type, creation date, and last usage date. For example, I want to filter jobs that were created approximately a year ago but haven't been used in the last 6 months. The output should include the job names along with the other specified details.

Is it possible to create and run such a script in Automic? for your understanding, I've set the time interval for job creation as 1 year from today's date and the last usage interval as 6 months from today.

If anyone knows of this, could you please provide me with the script?

Thank you,

Abhishek

[Peter Grundler]

Hi Abishek,

a few days ago there was a similar question in this forum: https://community.broadcom.com/discussion/script-to-get-the-job-list-which-is-older-and-not-active

Can you please check if the query provided would fit your needs.

regards,
Peter

[Siddharth V]

Hi Peter, 

We have checked the query but we're not getting the expected results. Could you please help with this?

Thanks,

Siddharth 

[Peter Grundler]

Hi Abhishek,

a few days ago there was a similar question: https://community.broadcom.com/discussion/script-to-get-the-job-list-which-is-older-and-not-active

Please check if the query would fit your needs.

regards,

Peter

[Jared Kessans]

This query might be a good starting point for what you are requesting.

select OH_Client as "Client", OH_NAME as "Name", OH_Otype as "Object Type", 
convert(datetime, OH_CRDATE AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time') as "Object Creation Date",
convert(datetime, OH_MODDATE AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time') as "Last Modification Date",
isnull(CONVERT(VARCHAR(19),max(convert(datetime, AH_1.AH_TIMESTAMP1 AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time'))),CAST('No statistics' as CHAR)) "Last Execution Date"
from OH
left outer join AH as AH_1 on OH.OH_IDNR = AH_1.AH_OH_IDNR
where OH_DELETEFLAG = 0
and OH_REFIDNR = 0
and OH_CLIENT = 10
--and (OH_Name like 'PROD%' or OH_Name like 'TEST%')
and OH_OTYPE in ('CALL','EVNT','JOBF','JOBG','JOBS','JSCH','SCRI','JOBP')
-- the GROUP BY for the output of the last activation in the resultset
group by OH_Client, OH_NAME, OH_Otype, OH_CRDATE, OH_MODDATE
-- sort by activation time
having max(AH_1.AH_TIMESTAMP1) < DATEADD(dd,-180,GETDATE()) 
or max(AH_1.AH_TIMESTAMP1) is null
order by oh_name, max(AH_1.AH_TIMESTAMP1), OH_CRDATE

[Aditya Simha S]

Hi Jared,

From the above query we are not able fetch data, we are getting error in the "convert" statement for the above query but once the convert statement is removed we are able to run the query. The error is "missing parenthesis" and also can you please modify the query to get owner details, host details(under which agent job is running) and region details.

Thanks

[roumaissa hamidi]

Yes, it is quite possible to create and run a script in Automic (also known as Automation Engine) to extract information about customer tasks, and filter them based on the date of creation and last use. Automic uses the scripting language called Automation Engine Script Language (AESL) which makes it possible to manipulate objects and execute commands.

[roumaissa hamidi]

Exemple de Script en Automic :

plaintext
Copy code



:SET &CLIENT_NR# = "12345"  ! Numéro de client
:SET &DATE_LIMIT_CREATION# = SYS_DATE_SUB("YYYY-MM-DD", "12")  ! Il y a un an
:SET &DATE_LIMIT_LAST_USED# = SYS_DATE_SUB("YYYY-MM-DD", "6")  ! Il y a six mois

:SET &HND# = PREP_PROCESS_VAR(VARA.SQLI.GET_TASKS_DETAILS, "&CLIENT_NR#", "&DATE_LIMIT_CREATION#", "&DATE_LIMIT_LAST_USED#")

:PROCESS &HND#
:   SET &CLIENT_ID# = GET_PROCESS_LINE(&HND#, 1)
:   SET &TASK_NAME# = GET_PROCESS_LINE(&HND#, 2)
:   SET &TASK_TYPE# = GET_PROCESS_LINE(&HND#, 3)
:   SET &CREATION_DATE# = GET_PROCESS_LINE(&HND#, 4)
:   SET &LAST_USED_DATE# = GET_PROCESS_LINE(&HND#, 5)

:   PRINT "Client ID: &CLIENT_ID#, Task Name: &TASK_NAME#, Type: &TASK_TYPE#, Created On: &CREATION_DATE#, Last Used On: &LAST_USED_DATE#"
:ENDPROCESS

:CLOSE_PROCESS &HND#

192.168.100.1 192.168.1.1

[Marilyn Singley]

I have a simple query I use that lists Jobs and workflows that have no statistics and have never run

select oh_client, oh_name, oh_otype

from oh

where not exists (select 1

                  from ah

where ah_oh_idnr = oh_idnr)

and oh_otype in ('JOBS', 'JOBP')

and oh_deleteflag = 0

and oh_client =