Is there a way to create a list of jobs which have not run in the past N months?
I could not find any constraint for that in the "Find" or in the "Export" items.
We are on 4.3.1.
Finding jobs not run in past 6 months
Accepted Answer
Staff
Accepted Answer
The only way to do this in the Console would be to select a group or queue, sort the jobs in descending order by Last Start, then scan through to get to the date you want. There wouldn't be a way to print that, though.
The simplest way to generate a list is to run a database query:
select j.oid,j.name,g.name as GroupName, executionStart from job j join jobstatus js on js.jobOID=j.oid join jobGroup g on j.jobGroupOID=g.oid
where executionStart<DATEADD(month,-6,getdate())
If you'd rather do it through the API I can put together a sample script for you.
Accepted Answer
Thank you for your quick reply, Bill.
The PHS DBA wants to know how to run this SQL statement on the adTempus instance. I think `sqlcmd.exe` is the CLI for SQL Server. However, I do not know the instance name, username, password, etc.
Can you suggest a command?
Accepted Answer
The knowledge base article I linked to in my earlier post has information on how to query the database. If you're on the adTempus server and using SQL Server Express the easiest way is to use the adTempus database utility. If you need to connect from a different computer, or you're using your own database server instead of SQL Server Express, install Management Studio on your computer.
The instance name is "adtempus" if you are using SQL Server Express. It will be using integrated security (automatic authentication--no explicit user ID and password). If you're not logged in under the same account that was used to install SQL Server Express, you may not have permission to connect to the database, in which things get a little more complicated and I'll have to dig up some instructions for you.
Accepted Answer
On second thought you can do this through the Console using a custom report. I've attached a report that does it.
On the computer where you're running the adTempus Console, go to "My Documents" in Windows Explorer and create "adTempus\Reports" if those folders aren't there. Save the report file to that folder, then restart the Console. You should now have a "Jobs Not Run in X Days" report in the Reports list.
I set this up to default to 180 days. It should prompt you for the number of days you want to use as the threshold, but there's a bug somewhere and the report viewer isn't showing the parameter prompt. While I look into that, here are workarounds:
- Select Report Designer from the Tools menu
- In the Report Designer, click the Open button on the toolbar
- Open the "Jobs Not Run in X Days" report
Now you have two options:
1. Click the Preview tab to run the report from the designer, and it will prompt you for the number of days.
2. You can change the default value and save the report so that it uses the value when you run the report from the main list. To do this:
- Go to the Report Explorer (top right corner)
- Click the Field List tab
- Scroll down to the bottom and expand the Parameters group
- Select Threshold
- In the Properties, change the default value to whatever number of days you want to use.
- Click Save to save the report.
Accepted Answer
Many thanks, Bill.
Accepted Answer
The report is working if I choose a small enough set of jobs. Anything in more than a few jobs across six (6) months appears to generate this message. What do I do to change the MaxReceivedMessageSize property?
Message Number: ADT005166E
Message: The server connection has failed or has been closed: The maximum message size quota for incoming messages (10240000) has been exceeded. To increase the quota, use the MaxReceivedMessageSize property on the appropriate binding element.: The maximum message size quota for incoming messages (10240000) has been exceeded. To increase the quota, use the MaxReceivedMessageSize property on the appropriate binding element.
Additional Information:
DebuggingInformation: The maximum message size quota for incoming messages (10240000) has been exceeded. To increase the quota, use the MaxReceivedMessageSize property on the appropriate binding element.: The maximum message size quota for incoming messages (10240000) has been exceeded. To increase the quota, use the MaxReceivedMessageSize property on the appropriate binding element.
****************************************
Or, this message.
Message Number: ADT005166E
Message: The server connection has failed or has been closed: The communication object, System.ServiceModel.Channels.ServiceChannel, cannot be used for communication because it is in the Faulted state.
Additional Information:
DebuggingInformation: The communication object, System.ServiceModel.Channels.ServiceChannel, cannot be used for communication because it is in the Faulted state.
****************************************
Accepted Answer
It won't matter what your time period is--it should be happening every time you try to run the report on the server, because it's related to the total number of jobs on the server.
The problem is the way the report is built, plus a flaw in the data provider that it's built on. The report uses a data provider that requests all job definitions from the server, then the report filters by date on the client side. If you have a lot of jobs, that will be more data than the communication channel is configured to allow. Increasing the limit probably isn't a great solution because you will run into performance problems. Let me think about another solution.
Accepted Answer
While you are at it, Bill, would it be possible to get the adTempus heirarchy path as a field in the output?
Jobs/OutBound_Extracts/Production/ACG,BEGIN_ACG,47,...
Accepted Answer
Sorry for the delay. I haven't had time to put together the solution that would be needed to make the report work for you--which is to write a custom data provider for the report. That's something that we should have documentation and examples for but don't.
As an alternative, here's a PowerShell script that uses the API to give you the information:
param (
[string]$server = ".",
[int]$days = 180
)
add-type -path "c:\program files\arcana development\adtempus\4.0\ArcanaDevelopment.adTempus.Client.dll"
$cutoffDate=[DateTime]::UtcNow.Subtract([TimeSpan]::FromDays($days))
$adtempus=[ArcanaDevelopment.adTempus.Client.Scheduler]::Connect($server,[ArcanaDevelopment.adTempus.Shared.LoginAuthenticationType]::Windows,"","")
$context=$adtempus.NewDataContext()
$restartPaging=$false
$context.GetJobs("*",[ArcanaDevelopment.adTempus.Shared.ObjectFetchOptions]::StubsOnly,0,[ref] $restartPaging) |
Where-Object{($_.JobStatus.ExecutionStart -eq $null) -or ($_.JobStatus.ExecutionStart -lt $cutoffDate)} |
Select-Object Name,@{n="GroupName";e={$_.Group.FullyQualifiedName}},@{n="LastStart";e={$_.JobStatus.ExecutionStart}},@{n="JobId";e={$_.OID.ObjectID}}
This uses a more efficient data mechanism to fetch the jobs from the server so it should work OK even with a large number of jobs.
Save it to a file called, for example, "listunrunjobs.ps1". By default it connects to the local adTempus server and shows jobs not run in the last 180 days. You can override that using the days parameter:
.\listunrunjobs.ps1 -days 30
If you want this information in CSV form (which seems like maybe what you're looking for) you can pipe it to export-csv:
.\listunrunjobs.ps1 -days 30 | export-csv -path joblist.csv
If you're still interested in having a report in the Console, I'll try to get back to that for you in the next few days.
Accepted Answer
This is looking great, Bill. But, is it possible that:
($_.JobStatus.ExecutionStart -lt $cutoffDate)
should be:
($_.JobStatus.ExecutionStart -ge $cutoffDate)
Accepted Answer
Does $_.JobStatus.ExecutionStart -eq $null mean that the job has -never- been run?
Accepted Answer
I seem to be having better luck with this. Am I missing something?
Where-Object{($_.JobStatus.ExecutionStart -ne $null) -and ($_.JobStatus.ExecutionStart -ge $cutoffDate)} |
Accepted Answer
Does $_.JobStatus.ExecutionStart -eq $null mean that the job has -never- been run?
Yes--this will include jobs that have never been run. If you don't want to include jobs that have never been run, change that line to
Where-Object{($_.JobStatus.ExecutionStart -ne $null) -and ($_.JobStatus.ExecutionStart -lt $cutoffDate)} |
This is looking great, Bill. But, is it possible that:
($_.JobStatus.ExecutionStart -lt $cutoffDate)
should be:
($_.JobStatus.ExecutionStart -ge $cutoffDate)
My script is correct: it should use "-lt". You wanted jobs that have not run within the last X days. Line 7 calculates the cutoff date by subtracting X days from the current date, so you want jobs that were last run before that date. If you use -ge, you are getting jobs that have run in the last X days.
Accepted Answer
Yes, you are right, Bill. It should be -lt. Back here at the ranch they are asking for jobs that have been run within the last 180 days. I forgot the original premise of the question. My mistake.
I am trying to use:
Where-Object{($_.JobStatus.ExecutionStart -ne $null) -and ($_.JobStatus.ExecutionStart -ge $cutoffDate)} |
Sort-Object -Property Group.FullyQualifiedName |
The sort does not appear to be happening. What am I missing?
Accepted Answer
You can't sort on a nested property like Group.FullyQualifiedName. The Select-Object is using calculated expressions to get the nested properties we care about and give them names. So you should have Sort-Object coming after that, and sort by GroupName (which was created from Group.FullyQualifiedName). So:
Where-Object{($_.JobStatus.ExecutionStart -ne $null) -and ($_.JobStatus.ExecutionStart -ge $cutoffDate)} |
Select-Object Name,@{n="GroupName";e={if($_.Group.IsRootGroup) {""} else{$_.Group.FullyQualifiedName}}},@{n="LastStart";e={$_.JobStatus.ExecutionStart}},@{n="JobId";e={$_.OID.ObjectID}} |
Sort-Object -Property GroupName,Name
Note that I made a change to Select-Object to leave the GroupName blank for jobs in the root group, rather than having it say "Root."
Accepted Answer
This is working well for us, Bill.
I put a REVERSE SOLIDUS (backslash) prefix for the group path. Also, there may not be many people who want to know the PowerShell type as the first line of the .csv file.
$context.GetJobs("*",[ArcanaDevelopment.adTempus.Shared.ObjectFetchOptions]::StubsOnly,0,[ref] $restartPaging) |
Where-Object{($_.JobStatus.ExecutionStart -ne $null) -and ($_.JobStatus.ExecutionStart -ge $cutoffDate)} |
Select-Object @{n="GroupName";e={if($_.Group.IsRootGroup) {"\"} else { '\' + $_.Group.FullyQualifiedName }}}, Name |
Sort-Object -Property GroupName,Name |
Export-Csv -Path "$Env:TEMP\joblist.csv" -NoTypeInformation
Replies are disabled for this topic.
