Is this possible with an SQL query?

Question

Sorry for the generic title of the question, but I didn't know how else to put it.. So here goes:

I have a single table that holds the following information:

computerName | userName |           date          | logOn | startUp
             |          |                         |       |
ID_000000001 |  NULL    | 2012-08-14 08:00:00.000 |  NULL |   1
ID_000000001 |  NULL    | 2012-08-15 09:00:00.000 |  NULL |   0
ID_000000003 | user02   | 2012-08-15 19:00:00.000 |   1   |  NULL
ID_000000004 | user02   | 2012-08-16 20:00:00.000 |   0   |  NULL

computername and username are self-explanatory I suppose

logOn is 1 when the user logged on at the machine and 0 when he logged off.

startUp is 1 when the machine was turned on and 0 when it got turned off.

the other entry is alway NULL respectively since we can't login and startup at the exact same time.

Now my task is: Find out which computers have been turned on the least amount of time over the last month (or any given amount of time, but for now let's say one month) Is this even possible with SQL? <-- Careful: I don't need to know how many times a PC was turned on, but how many hours/minutes each computer was turned on over the given timespace

There's two little problems as well:

We cannot say that the first entry of each computer is a 1 in the startUp column since the script that logs those events was installed recently and thus maybe a computer was already running when it started logging.

We cannot assume that if we order by date and only show the startUpcolumn that the entries will all be alternating 1's and 0's because if the computer is forced shut down by pulling the plug for example there won't be a log for the shutdown and there could be two 1's in a row.

EDIT: userName is of course NULL when startUp has a value, since turning on/shutting down doesn't show which user did that.

Answer 1

In a stored procedure, with cursors and fetch loops. And you use a temptable to store by computername the uptime. I give you the main plan, I'll let you see for the details in the TSQL guide.

Another link: a good example with TSQL Cursor.

 DECLARE @total_hour_by_computername    
declare @computer_name varchar(255)
declare @RowNum int

 --Now in you ComputerNameList cursor, you have all different computernames:
declare ComputerNameList cursor for
select DISTINCT computername from myTable

-- We open the cursor
OPEN ComputerNameList

     --You begin your foreach computername loop :
    FETCH NEXT FROM ComputerNameList 
    INTO @computer_name
    set @RowNum = 0 
    WHILE @@FETCH_STATUS = 0
    BEGIN

     SET @total_hour_by_computername=0;

     --This query selects all startup 1 dates by computername order by date.
     select @current_date=date from myTable where startup = 1 and computername = @computername order by date

         --You use a 2nd loop on the dates that were sent you back: 

         --This query gives you the previous date 
         select TOP(1) @previousDate=date from myTable 
         where computername = @computername and date < @current_date and startup is not null
         order by date DESC


         --If it comes null, you can decide not to take it into account.
         --Else
         SET @total_hour_by_computername=@total_hour_by_computername+datediff(hour, @previousDate, @current_date);

    --Once all dates have been parsed, you insert into your temptable the results for this computername

    INSERT INTO TEMPTABLE(Computername,uptime) VALUES (@computername,@total_hour_by_computername)

--End of the @computer_name loop
  FETCH NEXT FROM ComputerNameList 
    INTO @computer_name
END
CLOSE ComputerNameList
DEALLOCATE ComputerNameList

You only need a select into your temptable to determine which one of the computers has been up the most time.

Answer 2

As RoadWarrior pointed out, an accurate reports is not possible when shutdown messages are dropped. But here is an attempt to generate something useful. I'm going to assume the table name is computers:

SELECT c1.computerName,
       timediff(MIN(c2.date), c1.date) as upTime
FROM computers as c1, computers as c2
WHERE c1.computerName=c2.computerName
  AND c1.startUp=1 AND c2.startUp=0
  AND c2.date >= c1.date
GROUP BY c1.date
ORDER BY c1.date;

This will generate a list of all the periods a computer was on. To generate your requested report you can use the above query as a subquery:

SELECT
  c3.computerName,
  SEC_TO_TIME(SUM(TIME_TO_SEC(c3.upTime))) AS totalUpTime
FROM
  (SELECT c1.computerName,
          timediff(MIN(c2.date), c1.date) AS upTime
   FROM computers AS c1, computers AS c2
   WHERE c1.computerName=c2.computerName
     AND c1.startUp=1 AND c2.startUp=0
     AND c2.date >= c1.date
   GROUP BY c1.date
   ORDER BY c1.date
  ) AS c3
GROUP BY c3.computerName
ORDER BY c3.totalUpTime;

Answer 3

If (per your last paragraph) you aren't recording all shutdown events. then you don't have the information available to generate a report showing the amount of time each computer has been switched on. Because you aren't recording all instances of computer shutdown, it doesn't matter what SQL query you use.

FWIW, this schema isn't 3NF. A more common approach would be to have a single column recording each event, for example:

ComputerId:UserId:EventId:EventDate

The first three columns are each a foreign key into another table where the details are stored. Although even with this schema, the UserID would be null for startup/shutdown events.

Answer 4

Try this query (replace table_name with the name of your table):

SELECT SUM(startUp) AS startupTimes 
FROM table_name
GROUP BY computerName
ORDER BY startupTimes

This will output the number of times each computer has been started. To get just the first row (the computer that has the least amount of startups) you can append LIMIT 1 to the query.

Answer 5

You could group by computer, and use where to filter for startups in a particular month:

select  computerName
,       count(*)
from    YourTable
where   '2012-08-01' <= [date] and [date] < '2012-09-01' 
        and startup = 1
group by
        computerName
order by
        count(*) desc