Author Posts

April 9, 2016 at 5:54 pm

Hi,
Need a script which will give me the list of scheduled jobs/duplicate jobs in vRanger.

-Kalyan

April 10, 2016 at 6:03 am

Hi Venkata, you are actually asking people to do free work for you. We are here to help, not to provide you a complete solution. For that you will need to hire a consultant. What have you tried so far yourself? Didn't it work?

April 11, 2016 at 1:49 am

Hi Richard,
Below is the SQL script, which will give me all the information. The problem is I am not able to get only inventorynodename, name & start time columns.

SELECT * FROM
(SELECT r.*,
rtt.InventoryNodeName, t.TemplateId, tv.Name FROM Recurrence r
INNER JOIN TemplateVersion tv ON tv.TemplateVersionId = r.TemplateVersionId
INNER JOIN Template t ON t.TemplateId = tv.TemplateId
INNER JOIN ReplicateTemplateTask rtt ON tv.TemplateVersionId = rtt.TemplateVersionId
WHERE t.IsEnabled = 1 AND r.IsDisabled = 0 AND tv.IsCurrent = 1
) AS t1 — All enabled scheduled Replication templates and settings
LEFT OUTER JOIN
(SELECT * FROM
(SELECT t.TemplateId, j.JobId, j.StartTime, js.Name AS JobState, ks.Name AS TaskStatus, k.Error,
RANK() OVER (PARTITION BY t.TemplateId ORDER BY j.StartTime DESC) AS JobRank
FROM Job j
INNER JOIN Task k on j.JobId = k.JobId
INNER JOIN JobState js ON j.JobStateId = js.JobStateId
INNER JOIN TaskStatus ks ON k.TaskStatusId = ks.TaskStatusId
INNER JOIN ReplicateTask rk ON k.TaskId = rk.TaskId
INNER JOIN TemplateVersion tv ON j.TemplateVersionId = tv.TemplateVersionId
INNER JOIN Template t ON tv.TemplateId = t.TemplateId
) tmp_table
WHERE JobRank = 1
) AS t2 — Last run time, status and error if any. NULL for never executed templates
— also running and queued will be here with JobState Running and TaksStatus Unknown
–if either job or Task is Canceled, JobState is Complete and TaksStatus Canceled
ON t1.TemplateId = t2.TemplateId
LEFT OUTER JOIN
(SELECT * FROM
(SELECT t.TemplateId, j.JobId, j.StartTime, js.Name AS JobState, ks.Name AS TaskStatus, k.Error,
RANK() OVER (PARTITION BY t.TemplateId ORDER BY j.StartTime DESC) AS JobRank
FROM Job j
INNER JOIN Task k on j.JobId = k.JobId
INNER JOIN JobState js ON j.JobStateId = js.JobStateId
INNER JOIN TaskStatus ks ON k.TaskStatusId = ks.TaskStatusId
INNER JOIN ReplicateTask rk ON k.TaskId = rk.TaskId
INNER JOIN TemplateVersion tv ON j.TemplateVersionId = tv.TemplateVersionId
INNER JOIN Template t ON tv.TemplateId = t.TemplateId
) tmp_table
WHERE JobRank = 2
) AS t3
ON t1.TemplateId = t3.TemplateId
LEFT OUTER JOIN
(SELECT t.TemplateId, MAX(j.StartTime) AS LastSuccessful FROM Job j
INNER JOIN TemplateVersion tv ON j.TemplateVersionId = tv.TemplateVersionId
INNER JOIN Template t ON tv.TemplateId = t.TemplateId
INNER JOIN Task k ON j.JobId = k.JobId
WHERE k.TaskStateId = 3 AND k.TaskStatusId = 0
GROUP BY t.TemplateId
) AS t4 — Last successful. TaskStateId: 3 – Complete, TaskStatusId: 0 – Success
ON t1.TemplateId = t4.TemplateId
ORDER BY t1.InventoryNodeName

-Kalyan

April 11, 2016 at 3:07 am

I think this is not the right forum to ask your question, because it has nothing to do with PowerShell. I'd recommend you to ask this question at the SQL forum at Microsoft: https://social.msdn.microsoft.com/Forums/sqlserver/en-us/home?category=sqlserver