Automation for billing : API : adTempus : Arcana Development Forums

slick50

Joined: October 29, 2008
Posts: 38

Posted April 10, 2009 1:47:30 PM

Prior to adTempus, our company had some automation scripts which programmatically calcluated the number of times a job had executed within the past month, and generated a usage charge based on that number.

In adTempus, it seems there are quite a number of new data points we can use to generate charge info. However to start off with, I'd like to copy that model. Does anyone have a sample .NET code snippet which will loop through each job, and calculate the number of times it has executed in the past month?

Bill

Arcana Development Staff

Joined: September 21, 2007
Posts: 79

Posted April 10, 2009 5:30:58 PM

Depending on what you are doing, there may be better ways to get this data than through the API.

First, there's a Job Accounting report built in to the Console, which gives you instance counts and processing times.

If you need to integrate this into your own system, it's probably going to be more efficient to query the adTempus database directly rather than going through the API. This query will get you the total instances for each job in a given date range:

    
            select g.fullname as GroupName,j.name as JobName,count(*) as TotalInstances 
        
            from job j join executionhistoryitem ehi on j.oid=ehi.joboid 
        
            join jobgroup g on j.jobgroup=g.oid 
        
            where ehi.executionstart>='2009-03-01' and ehi.executionfinish<'2009-04-01' 
        
            group by g.fullname,j.name

This just gives you the total instances, but you could also easily sum up the execution times if you want that information.

You can accomplish what you want using the API, but the API isn't set up for aggregating data, so it takes some work and involves retrieving a lot of data. The following VB code shows how you could do this. It retrieves all jobs, then iterates through them.

For each job it fetches the history for the date range you're interested in. Here's where things get inefficient. If you only care about the total number of instances, you can get a record count without retrieving them all (Option A in the code). However, if you want to get anything else (like, say, summing up the total execution time), you would have to retrieve each instance and look at it, which can be slow if there's a lot of history to look at. To use this approach, comment out Option A and uncomment Option B below.

The code assumes you already have a connection. See this example if you need the code for that.

    
                'this method retrieves all jobs from the adTempus server, 
        
                'then retrieves the history for the last 30 days for each job. 
        
                Private Sub ProcessJobRuns(ByVal scheduler As IScheduler) 
        
                    Dim startDateTime As DateTime 
        
                    Dim endDateTime As DateTime 
        
                    Dim jobs As IADTObjects 
        
                    Dim job As IJob 
        
                    endDateTime = DateTime.Now 
        
                    startDateTime = endDateTime.AddDays(-30) 
        
                    'get all jobs 
        
                    jobs = scheduler.GetObjectsForClass(ClassIDEnum.CID_Job) 
        
                    'process each job 
        
                    For Each job In jobs 
        
                        ProcessJobHistory(scheduler, job, startDateTime, endDateTime) 
        
                    Next 
        
                End Sub 
        
                Private Sub ProcessJobHistory(ByVal scheduler As IScheduler, ByVal job As IJob, ByVal startDate As DateTime, ByVal endDate As DateTime) 
        
                    Dim filter As New JobHistoryFilter 
        
                    Dim instances As IADTObjects 
        
                    Dim recordCount As Integer 
        
                    filter.AddJobOID(job.OID) 
        
                    filter.EndDate = endDate 
        
                    filter.StartDate = startDate 
        
                    'Option A: if you only want the count of instances, use this: 
        
                    scheduler.GetObjectsWhere(CType(filter, IObjectRequestFilter), 1, True, recordCount) 
        
                    'this will only fetch one instance from the database, but the recordCount will indicate  
        
                    'the total number of instances in the date range. 
        
                    'Option B: if you want to process each instance (e.g., to get execution times), use the following line instead. 
        
                    'it will get all instances for the time period. 
        
                    'instances = scheduler.GetObjectsWhere(CType(filter, IObjectRequestFilter), 0, True, recordCount) 
        
                    'you can then iterate through the instances. 
        
                End Sub

slick50

Joined: October 29, 2008
Posts: 38

Posted April 13, 2009 1:48:14 PM

It didn't occur to me to query the database directly, but that makes perfect sense. Thanks for the sample code.

slick50

Joined: October 29, 2008
Posts: 38

Posted April 19, 2009 5:07:25 PM

Do you have any sample code on how to make the connection? I seem to be having a difficult time figuring out that detail.

Bill

Arcana Development Staff

Joined: September 21, 2007
Posts: 79

Posted April 20, 2009 7:24:35 PM

What scripting environment/language are you using? The database code you need will be dependent on this.

Generally, you want to create an ADO or OLEDB connection. See this article for information on the database connection string that adTempus uses. Your script will use the same or a similar one.

slick50

Joined: October 29, 2008
Posts: 38

Posted April 29, 2009 10:04:21 AM

I hesitated responding to this because I am using Perl, and I was a bit afraid of the reaction I would get. However, I was able to successfully make a connection to the database two different ways with Perl and successfully execute the query you provided. I will submit a post in the General section to explain how I made the connection - hopefully will help others.

Now I need to read up on the database schema :)

Thanks very much for your help.

Community

Automation for billing