1#!/usr/bin/python
2
3# Copyright (c) 2011 The Chromium OS Authors. All rights reserved.
4# Use of this source code is governed by a BSD-style license that can be
5# found in the LICENSE file.
6
7"""
8Tool to check DUT usage by querying the Autotest DB.
9
10Sample usage:
11
12utils/site_check_dut_usage.py 11/1/2011 11/5/2011 netbook_LABEL
13"""
14
15import datetime
16import optparse
17import sys
18
19import common
20from autotest_lib.database import database_connection
21
22_DATE_FORMAT = '%m/%d/%Y'
23
24
25class CheckDutUsageRunner(object):
26    """Checks DUT usage for given label or hostname during a time period."""
27
28    def __init__(self, start_time, end_time, label, hostname, list_hostnames):
29        """
30        Instantiates a CheckDUTUsageRunner.
31
32        @start_time: start date of time period we are interested in.
33        @end_time: end date of time period we are interested in.  Note the
34            time period is (start_date, end_date].
35        @label: If not None, the platform label of the hostnames we are
36            interested in.
37        @hostname: If not None, the hostname we are intersted in.
38        @list_hostnames: If set, print out the list of hostnames found that ran
39            jobs during the given time period.
40        """
41        self._start_time = start_time
42        self._end_time = end_time
43        self._list_hostnames = list_hostnames
44        self._label = label
45        self._hostname = hostname
46        self._database_connection = None
47
48
49    def find_all_durations(self):
50        """
51        Returns all list of tuples containing durations.
52
53        A duration is a 4-tuple containing |queued_time|, |started_time|,
54        |finished_time|, |hostname|.
55        """
56        query = ('select queued_time, started_time, finished_time, '
57                 '  hostname '
58                 'from tko_jobs left join tko_machines on '
59                 '  tko_jobs.machine_idx=tko_machines.machine_idx '
60                 'where tko_jobs.started_time>=DATE(%s) and '
61                 '  tko_jobs.finished_time<DATE(%s)')
62        if self._label:
63            query += ' and tko_machines.machine_group=%s'
64            filter_value = self._label
65        else:
66            query += ' and tko_machines.hostname=%s'
67            filter_value = self._hostname
68
69        results = self._database_connection.execute(
70                query, [self._start_time, self._end_time, filter_value])
71        return results
72
73
74    @staticmethod
75    def _total_seconds(time_delta):
76        """
77        Returns a float that has the total seconds in a datetime.timedelta.
78        """
79        return float(time_delta.days * 86400 + time_delta.seconds)
80
81
82    def calculate_usage(self, durations):
83        """
84        Calculates and prints out usage information given list of durations.
85        """
86        total_run_time = datetime.timedelta()
87        total_queued_time = datetime.timedelta()
88        machines = set()
89        for q_time, s_time, f_time, machine in durations:
90            total_run_time += f_time - s_time
91            total_queued_time += s_time - q_time
92            machines.add(machine)
93
94        num_machines = len(machines)
95        avg_run_time = total_run_time / num_machines
96        avg_job_run_time = self._total_seconds(total_run_time) / len(durations)
97        avg_job_queued_time = (self._total_seconds(total_queued_time) /
98                               len(durations))
99        duration = self._end_time - self._start_time
100        usage = self._total_seconds(avg_run_time) / self._total_seconds(
101                duration)
102
103        # Print the list of hostnames if the user requested.
104        if self._list_hostnames:
105            print '=================================================='
106            print 'Machines with label:'
107            for machine in machines:
108                print machine
109            print '=================================================='
110
111        # Print the usage summary.
112        print '=================================================='
113        print 'Total running time', total_run_time
114        print 'Total queued time', total_queued_time
115        print 'Total number of machines', num_machines
116        print 'Average time spent running tests per machine ', avg_run_time
117        print 'Average Job Time ', datetime.timedelta(seconds=int(
118                avg_job_run_time))
119        print 'Average Time Job Queued ', datetime.timedelta(seconds=int(
120                avg_job_queued_time))
121        print 'Total duration ', duration
122        print 'Usage ', usage
123        print '=================================================='
124
125
126    def run(self):
127        """Connects to SQL DB and calculates DUT usage given args."""
128        # Force the database connection to use the read the readonly options.
129        database_connection._GLOBAL_CONFIG_NAMES.update(
130                {'username': 'readonly_user',
131                 'password': 'readonly_password',
132                })
133        self._database_connection = database_connection.DatabaseConnection(
134                global_config_section='AUTOTEST_WEB')
135        self._database_connection.connect()
136
137        durations = self.find_all_durations()
138        if not durations:
139            print 'Query returned no results.'
140        else:
141            self.calculate_usage(durations)
142
143        self._database_connection.disconnect()
144
145
146def parse_args(options, args, parser):
147    """Returns a tuple containing start time, end time, and label, hostname."""
148    label, hostname = None, None
149
150    if len(args) != 4:
151        parser.error('Should have exactly 3 arguments.')
152
153    if options.hostname:
154        hostname = args[-1]
155    else:
156        label = args[-1]
157
158    start_time, end_time = args[1:3]
159    return (datetime.datetime.strptime(start_time, _DATE_FORMAT).date(),
160            datetime.datetime.strptime(end_time, _DATE_FORMAT).date(),
161            label, hostname)
162
163
164def main(argv):
165    """Main method.  Parses options and runs main program."""
166    usage = ('usage: %prog [options] start_date end_date platform_Label|'
167             'hostname')
168    parser = optparse.OptionParser(usage=usage)
169    parser.add_option('--hostname', action='store_true', default=False,
170                      help='If set, interpret argument as hostname.')
171    parser.add_option('--list', action='store_true', default=False,
172                      help='If set, print out list of hostnames with '
173                      'the given label that ran jobs during this time.')
174    options, args = parser.parse_args(argv)
175
176    start_time, end_time, label, hostname = parse_args(options, args, parser)
177    runner = CheckDutUsageRunner(start_time, end_time, label, hostname,
178                                 options.list)
179    runner.run()
180
181
182if __name__ == '__main__':
183    main(sys.argv)
184