Showing no data for vara object

[Abhishek Nayak]

Hi Team,

I am using a vara query to output all jobs in the Waiting for queue slot (max. parallel tasks exceeded), Waiting for host status but it's not showing any output for me.

I am using the below query:

SELECT AH_Idnr AS "Run ID", 
       AH_Name AS "Job Name", 
       ah_otype AS "TYPE", 
       AH_Title AS "Title", 
       AH_Queue AS "QUEUE", 
       AH_Status AS "Status", 
       AH_HostDst AS "HOST", 
       AH_TimeStamp1 AS "Activation Time", 
       AH_TimeStamp2 AS "Start Time", 
       AH_TimeStamp4 AS "End Time", 
       AH_Client AS "CLIENT", 
       COUNT(*) AS "Count"
FROM AH
WHERE AH_Status IN (1684,1700,1696)
      AND AH_Client IN (1, 100, 110, 200, 700, 800, 1000)
      AND AH_Queue = 'QUEUE.E1_PERU'
      AND ah_otype IN ('JOBS', 'JOBP')
GROUP BY AH_Name, AH_Idnr, AH_Title, AH_Queue, AH_Status, AH_HostDst, AH_TimeStamp1, AH_TimeStamp2, AH_TimeStamp4, AH_Client, ah_otype
ORDER BY 1, 2

if i check in AWI some jobs are still in waiting for queue slot and waiting for host status
Please let me know if I am making some mistakes in the above query.

Thanks,

Abhishek

[Abhishek Nayak]

I have modified the query and provided AH_Status = 0
      AND AH_Idnr = 137230777 , now i am getting the correct output, but my doubt is for waiting for queue  status it should be 1684 not 0.

Please let me know your thoughts.

Thanks,

[Joel Wiesmann]

You query the wrong table. AH is archive, you're interested in activities (EH).

------------------------------
☎️ Swisscom Automation Engineer & 🧙 PEM Creator

Online Automic Training 24/7:
https://membership.philippelmer.com/ (deutsch)
https://pemautomic.com (english)
------------------------------

[Abhishek Nayak]

Hi Joel,

Still, I am not getting the desired output for jobs in waiting status but getting output for jobs that have been ended not ok and ended normally, i have modified the query :

SELECT DISTINCT 
    (SELECT AH_Idnr FROM Ah WHERE AH_Idnr = EH_AH_Idnr) AS "Run ID", 
    EH_Name AS "Job Name", 
    
    eh_otype AS "TYPE", 
    EH_Title AS "Title", 
    EH_Queue AS "QUEUE", 
    EH_Status AS "Status", 
    EH_HostDst AS "HOST", 
    EH_ActivationTime AS "Activation Time", 
    EH_StartTime AS "Start Time", 
    EH_EndTime AS "End Time", 
    EH_Client AS "CLIENT", 
    COUNT(*) AS "Count" 
FROM EH 
WHERE EH_Status BETWEEN 1653 AND 1909
  AND EH_Client IN (1, 100, 110, 200, 700, 800, 1000) 
 
  AND EH_StartTime BETWEEN TO_DATE('2023-09-06', 'YYYY-MM-DD')
                  AND TO_DATE('2024-10-16', 'YYYY-MM-DD')
  AND eh_otype IN ('JOBS', 'JOBP') 
GROUP BY 
    EH_Name, 
     EH_AH_Idnr,
    EH_Title, 
    EH_Queue, 
    EH_Status, 
    EH_HostDst, 
    EH_ActivationTime, 
    EH_StartTime, 
    EH_EndTime, 
    EH_Client, 
    eh_otype 
ORDER BY 1, 2

if possible can you please let me know the correct query if you feel i am using the incorrect query.

Thanks,

Abhishek

[Joel Wiesmann]

Hi Abhishek

Just break down your SQL on the very simple query and then start building it up to contain groups and filters.

I doubt you want to do things like:

    (SELECT AH_Idnr FROM Ah WHERE AH_Idnr = EH_AH_Idnr) AS "Run ID",

Also your new query has nothing to with your initial request, where you wanted to filter for queues and waiting status. Now you filter for status that even contain ENDED_NOT_OK. So even if I would write you the statement you need, I don't know what you want :-p.

Good luck!

------------------------------
☎️ Swisscom Automation Engineer & 🧙 PEM Creator

Online Automic Training 24/7:
https://membership.philippelmer.com/ (deutsch)
https://pemautomic.com (english)
------------------------------

[Abhishek Nayak]

Hi Joel,

Sorry for the confusion, actually I was not getting the results for the job which all are in waiting status, so I just increased the status upto ended not ok also, my initial request is to find all the jobs that are in waiting status from last 24 hours, I will be glad if you provide me the query.

Thanks,

Abhishek