excel and access problems

Discussion in 'OT Technology' started by MattR2, Oct 2, 2006.

  1. MattR2

    MattR2 New Member

    Joined:
    Sep 6, 2004
    Messages:
    408
    Likes Received:
    0
    so im recording performance monitor statistics off of some of our servers but i cant figure out how to graph it properly.

    Each column is a different counter and each row is data captured every 10 minutes.

    But i want to find a way to make some graphs that avg each counter out based on the values for each hour and then also for each day. But i have no idea how to do this in excel.

    I tried importing the csv into access and was able to make a query that showed the data i wanted. But i still couldnt figure out how to then get it to avg that data and graph it!

    someone please help. Heres an example csv file
    http://download.yousendit.com/0D8B866A2D1DAFDE
     
  2. Peyomp

    Peyomp New Member

    Joined:
    Jan 11, 2002
    Messages:
    14,017
    Likes Received:
    0
    In SQL you would accomplish this by executing something like:

    SELECT HOUR(datetime), counter1, counter2, counter3 FROM countertable WHERE timerange between foo and bar GROUP BY HOUR(datetime);

    That would give you hourly values. Lookup group by. If Access can't do that... kill yourself.
     
  3. MattR2

    MattR2 New Member

    Joined:
    Sep 6, 2004
    Messages:
    408
    Likes Received:
    0
    sorry my sql isnt that great but i dont understand the last part

    WHERE timerange between foo and bar GROUP BY HOUR(datetime)

    so im saying 'where' between a time range i want say 11:00 - 12:00 but then grouping by the hour as well? That doesnt seem right to me.
     
  4. MattR2

    MattR2 New Member

    Joined:
    Sep 6, 2004
    Messages:
    408
    Likes Received:
    0
    sorry i just read up on the WHERE and GROUP BY clauses. il give it a shot and let you know how it works.

    Thanks!
     
  5. MattR2

    MattR2 New Member

    Joined:
    Sep 6, 2004
    Messages:
    408
    Likes Received:
    0
    SELECT [SUN-SV-EXCH Counters_2006100210].Field1, [SUN-SV-EXCH Counters_2006100210].Field2, [SUN-SV-EXCH Counters_2006100210].Field3, [SUN-SV-EXCH Counters_2006100210].Field4, [SUN-SV-EXCH Counters_2006100210].Field5, [SUN-SV-EXCH Counters_2006100210].Field6, [SUN-SV-EXCH Counters_2006100210].Field7, [SUN-SV-EXCH Counters_2006100210].Field8, [SUN-SV-EXCH Counters_2006100210].Field9, [SUN-SV-EXCH Counters_2006100210].Field10, [SUN-SV-EXCH Counters_2006100210].Field11, [SUN-SV-EXCH Counters_2006100210].Field12, [SUN-SV-EXCH Counters_2006100210].Field13, [SUN-SV-EXCH Counters_2006100210].Field14
    FROM [SUN-SV-EXCH Counters_2006100210]
    WHERE [SUN-SV-EXCH Counters_2006100210].Field1>("10/02/2006 10:00:00.000") And [SUN-SV-EXCH Counters_2006100210].Field1<("10/02/2006 11:00:00.000")
    GROUP BY HOUR([SUN-SV-EXCH Counters_2006100210].Field1);

    -----------------
    you tried to execute a query that does not include the specified expression 'field1' as part of an aggregate function.

    wtf
     
  6. MattR2

    MattR2 New Member

    Joined:
    Sep 6, 2004
    Messages:
    408
    Likes Received:
    0
    seems like the problem is the group by command.

    i wanna group by each column. hmm
     
  7. Peyomp

    Peyomp New Member

    Joined:
    Jan 11, 2002
    Messages:
    14,017
    Likes Received:
    0
    Uhhhh why are you using... field1 as your value and time field?

    Use GROUP BY if you want to look at more than one hour period. Like say you wanted to look at every hour period for an entire day, then you select between the edges of that day and group by the hour. Otherwise you can just select sum(field.1) and leave the group by out if you are just doing a single hour period.

    And actually I apologize as my code above is incorrect. When grouping by, you must also sum(counter1), sum(counter2), etc.
     
  8. MattR2

    MattR2 New Member

    Joined:
    Sep 6, 2004
    Messages:
    408
    Likes Received:
    0
    i think im closer now

    Code:
     
    SELECT HOUR(datetime), [SUN-SV-EXCH Counters_2006100210].Field2, [SUN-SV-EXCH Counters_2006100210].Field3, [SUN-SV-EXCH Counters_2006100210].Field4, [SUN-SV-EXCH Counters_2006100210].Field5, [SUN-SV-EXCH Counters_2006100210].Field6, [SUN-SV-EXCH Counters_2006100210].Field7, [SUN-SV-EXCH Counters_2006100210].Field8, [SUN-SV-EXCH Counters_2006100210].Field9, [SUN-SV-EXCH Counters_2006100210].Field10, [SUN-SV-EXCH Counters_2006100210].Field11, [SUN-SV-EXCH Counters_2006100210].Field12, [SUN-SV-EXCH Counters_2006100210].Field13, [SUN-SV-EXCH Counters_2006100210].Field14
    FROM countertable 
    WHERE timerange between foo and bar 
    GROUP BY HOUR(datetime), sum[SUN-SV-EXCH Counters_2006100210].Field2, sum[SUN-SV-EXCH Counters_2006100210].Field3, sum[SUN-SV-EXCH Counters_2006100210].Field4, sum[SUN-SV-EXCH Counters_2006100210].Field5, sum[SUN-SV-EXCH Counters_2006100210].Field6, sum[SUN-SV-EXCH Counters_2006100210].Field7, sum[SUN-SV-EXCH Counters_2006100210].Field8, sum[SUN-SV-EXCH Counters_2006100210].Field9, sum[SUN-SV-EXCH Counters_2006100210].Field10, sum[SUN-SV-EXCH Counters_2006100210].Field11, sum[SUN-SV-EXCH Counters_2006100210].Field12, sum[SUN-SV-EXCH Counters_2006100210].Field13, sum[SUN-SV-EXCH Counters_2006100210].Field14;
    
    i feel really stupid asking this but if its already grouping by the hour based on those commands then what do i enter for foo and bar? or is that an hour range and i need to make query's for each hour im interested in?
     
  9. Peyomp

    Peyomp New Member

    Joined:
    Jan 11, 2002
    Messages:
    14,017
    Likes Received:
    0
    1) Your datetime column is called (PDH-CSV 4.0) (Central European Daylight Time)(-120), right? So use that in the HOUR()function. Or whatever that field is called. Is it actually called datetime?
    2) I don't know that Access actually has an HOUR() function, but it will have something similar. Look up the date time functions for Access. For queries that span more than one day you will need to group by day() and month() as well.
    3) Re-reading what you want, it would look more like this:

    PHP:
    SELECT HOUR(datetime), avg([SUN-SV-EXCH Counters_2006100210].Field2), avg([SUN-SV-EXCH Counters_2006100210].Field3), avg([SUN-SV-EXCH Counters_2006100210].Field4), avg([SUN-SV-EXCH Counters_2006100210].Field5), avg([SUN-SV-EXCH Counters_2006100210].Field6), avg([SUN-SV-EXCH Counters_2006100210].Field7), avg([SUN-SV-EXCH Counters_2006100210].Field8), avg([SUN-SV-EXCH Counters_2006100210].Field9), avg([SUN-SV-EXCH Counters_2006100210].Field10), avg([SUN-SV-EXCH Counters_2006100210].Field11), avg([SUN-SV-EXCH Counters_2006100210].Field12), avg([SUN-SV-EXCH Counters_2006100210].Field13), avg([SUN-SV-EXCH Counters_2006100210].Field14)
    FROM countertable 
    WHERE datetime between foo 
    and bar 
    GROUP BY HOUR
    (datetime);
    You need to lookup the Access date time functions to format foo and bar.

    This query can be run for any period. You could run it for a day. You could run it for a month. You will get one row of returns for each hour in that period. foo and bar are datetime strings that define that period. How to format these will depend on Access's date and time functions, which you must lookup.
     
  10. MattR2

    MattR2 New Member

    Joined:
    Sep 6, 2004
    Messages:
    408
    Likes Received:
    0
    i just imported these in from a csv so the fields arent labelled yet so my datetime column is still called "[SUN-SV-EXCH Counters_2006100210].Field1"

    Code:
     
    SELECT HOUR([SUN-SV-EXCH Counters_2006100210].Field1), avg([SUN-SV-EXCH Counters_2006100210].Field2), avg([SUN-SV-EXCH Counters_2006100210].Field3), avg([SUN-SV-EXCH Counters_2006100210].Field4), avg([SUN-SV-EXCH Counters_2006100210].Field5), avg([SUN-SV-EXCH Counters_2006100210].Field6), avg([SUN-SV-EXCH Counters_2006100210].Field7), avg([SUN-SV-EXCH Counters_2006100210].Field8), avg([SUN-SV-EXCH Counters_2006100210].Field9), avg([SUN-SV-EXCH Counters_2006100210].Field10), avg([SUN-SV-EXCH Counters_2006100210].Field11), avg([SUN-SV-EXCH Counters_2006100210].Field12), avg([SUN-SV-EXCH Counters_2006100210].Field13), avg([SUN-SV-EXCH Counters_2006100210].Field14)
    FROM [SUN-SV-EXCH Counters_2006100210]
    WHERE [SUN-SV-EXCH Counters_2006100210].Field1 between "0" And "23"
    GROUP BY HOUR([SUN-SV-EXCH Counters_2006100210].Field1);
    
    
    data type mismatch in criteria expression

    i just dont fucking get the last two lines. WHERE and GROUP BY
     
    Last edited: Oct 2, 2006
  11. MattR2

    MattR2 New Member

    Joined:
    Sep 6, 2004
    Messages:
    408
    Likes Received:
    0
    little help?
     
  12. MattR2

    MattR2 New Member

    Joined:
    Sep 6, 2004
    Messages:
    408
    Likes Received:
    0
    ok i made a lot more progress and the query works as long as theirs no data in the date/time field. But when there is i get data type mismatch in criteria expression errors. Ive triple checked the formatting but im stumped.

    Code:
     
    SELECT datepart('m',[SUN-SV-EXCH Counters_2006100210].Field1)+"/"+datepart('d',[SUN-SV-EXCH Counters_2006100210].Field1)+"/"+datepart('yyyy',[SUN-SV-EXCH Counters_2006100210].Field1)+" "+datepart('h',[SUN-SV-EXCH Counters_2006100210].Field1)+":00:00", avg([SUN-SV-EXCH Counters_2006100210].Field2), avg([SUN-SV-EXCH Counters_2006100210].Field3), avg([SUN-SV-EXCH Counters_2006100210].Field4), avg([SUN-SV-EXCH Counters_2006100210].Field5), avg([SUN-SV-EXCH Counters_2006100210].Field6), avg([SUN-SV-EXCH Counters_2006100210].Field7), avg([SUN-SV-EXCH Counters_2006100210].Field8), avg([SUN-SV-EXCH Counters_2006100210].Field9), avg([SUN-SV-EXCH Counters_2006100210].Field10), avg([SUN-SV-EXCH Counters_2006100210].Field11), avg([SUN-SV-EXCH Counters_2006100210].Field12), avg([SUN-SV-EXCH Counters_2006100210].Field13), avg([SUN-SV-EXCH Counters_2006100210].Field14)
    FROM [SUN-SV-EXCH Counters_2006100210]
    GROUP BY datepart('m',[SUN-SV-EXCH Counters_2006100210].Field1)+"/"+datepart('d',[SUN-SV-EXCH Counters_2006100210].Field1)+"/"+datepart('yyyy',[SUN-SV-EXCH Counters_2006100210].Field1)+" "+datepart('h',[SUN-SV-EXCH Counters_2006100210].Field1)+":00:00";
     
    
     
  13. Peyomp

    Peyomp New Member

    Joined:
    Jan 11, 2002
    Messages:
    14,017
    Likes Received:
    0
    I dunno, man. I use MySQL and SQLite.
     

Share This Page