How to Export active (currently running) jobs from UC4 to a google sheet or a CSV file?

[Legacy User]

Help me out if it is possible to Export active (currently running) jobs from from UC4 to an google sheet or an CSV file by scripting.

[Legacy User]

Hi GaneshJ628943

Many thanks for your request.
This would be possible by means of a SQLI Variable (VARA.SQLI --> Object) and a corresponding  SQL Statement which needs to be created.
The database table which shows this information is the EH Table. 

Further information regarding DB Tables are found here.

Should you have any further questions or queries, feel free to contact me anytime.

Best Regards
Daniel Hausdorf

[Legacy User]

Hi @Daniel Hausdorf

Thanks for Sharing your idea on this.Can you explain still more on this about how to pull that extract.

[Legacy User]

Hi GaneshJ628943

Thanks for the reply and question.
What exactly would you like to know ?
As stated, to achieve this, you would need a SQLI Variable (in AE = VARA.SQLI (which is an Object))

https://us.v-cdn.net/5019921/uploads/editor/kv/x69a1awsvuu0.png" width="553">

Once you have added this, you need to create the corresponding SQL statement to fetch the 'running jobs'. After that you can export them.

Hope this helps :)

Best Regards
Daniel Hausdorf

[Judith Gitahi]

I am trying to do the same thing. Daniel_Hausdorf_6982 what is the "corresponding SQL statement to fetch the running jobs?"

[Legacy User]

Hi,

A very basic SQL statement to get a list of active objects:

select eh_name,eh_otype,eh_client,eh_status from eh

Cheers,
Alexander

[Judith Gitahi]

Hi Alexander, I tried running that SQL Statement, but the job name (eh_name) show up multiple times for most of the JOBS/JOBPs.

[Legacy User]

Hi JGi604607 ,

The same object name can show up multiple times with different states or even the same status.
If you only want to see each entry one use select distinct.

Cheers,
Alexander 

[Judith Gitahi]

Something doesn't look right. It only gives me 63 lines and we have a few hundreds, if not thousands, of jobs running in prod.

[Luu_Le_185]

Hi Judith,

As you mention that there is only 63 lines, is that when you are running the SQL with the distinct? Or with just the SQL example shown by Alexander?

select eh_name,eh_otype,eh_client,eh_status from eh

[Judith Gitahi]

Hi Luu, the 63 lines is when I am running the SQL with the distinct.

[Luu_Le_185]

That sound about right then.  If you had run the distinct like this:

select distinct eh_name,eh_otype,eh_client,eh_status from eh

Then it, would filter on the eh_name, making sure that it only return a unique (one entry of the eh_name).

So the original sql example Alex, shown would of given you all the object (jobs/jobp, script etc...) that is still out in the Activity window...

select eh_name,eh_otype,eh_client,eh_status from eh

So... this may return jobs that are ended_ok already (and waiting the parent to finished) or you might have jobp that complete but have not been deactivated..

Depending on how/what you want for the "currently running" then you can further specific back only jobs that are not in a finished status if you like...
--------------------
On my own system, if I run the sql provided by Alexander it return over 400 jobs/jobp etc... with various status... which in my case a lot of these are running, or waiting for its parent to finish or just have finished and not been deactivated...  And when I change it to distinct, it would only return 60 lines, which is correct since what the distinct function does in sql.

See example of Distinct

[Judith Gitahi]

Ohhh. Looks like I misunderstood the whole thing. I wanted a report that shows all the jobs that are active in Prod (scheduled or via event), not jobs that are CURRENTLY running/active.

[Legacy User]

Hi Judith!
I don't believe there is an easy way (besides using the Autoforecast tool within the UserInterface) to pull up jobs which are scheduled to run in the future via schedule or event. The EH table will contain everything you see within the Activity window which is all active jobs (and past active jobs which have not been de-activated). If you get stuck let your account manager know and we can get our professional services team to help if needed.