Here's what I started with that would give me like 20 or 30 of the same computer name before continuing on and producing the rest of the report...
I.E.
Computer01 X 30
Computer02 X 30
Computer03 X 30

Code:
SELECT SC.CONNECT_TIME,MACHINE.NAME AS SYSTEM_NAME,IP,MAC,MACHINE_DAILY_UPTIME.HOURS,MACHINE_DAILY_UPTIME.DAY,SC.CLIENT_CONNECTED FROM MACHINE LEFT JOIN KBSYS.SMMP_CONNECTION SC ON MACHINE.KUID = SC.KUID LEFT JOIN MACHINE_DAILY_UPTIME ON (MACHINE_DAILY_UPTIME.MACHINE_ID = MACHINE.ID) ORDER BY DAY,CONNECT_TIME,SYSTEM_NAME
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Community Chosen Answer

1
Basically you need to add in a SELECT DISTINCT statement. Example SELECT DISTINCT 'MACHINE.NAME' WHERE etc etc...
Answered 03/28/2012 by: scriptingninja
Fifth Degree Black Belt

  • 1st Attempt (Produces Error):
    SELECT DISTINCT (MACHINE.NAME), SC.CONNECT_TIME AS SYSTEM_NAME,IP,MAC,MACHINE_DAILY_UPTIME.HOURS,MACHINE_DAILY_UPTIME.DAY,SC.CLIENT_CONNECTED FROM MACHINE LEFT JOIN KBSYS.SMMP_CONNECTION SC ON MACHINE.KUID = SC.KUID LEFT JOIN MACHINE_DAILY_UPTIME ON (MACHINE_DAILY_UPTIME.MACHINE_ID = MACHINE.ID) ORDER BY DAY,CONNECT_TIME,SYSTEM_NAME

    2nd Attempt (Produces Error):
    SELECT SC.CONNECT_TIME,MACHINE.NAME AS DISTINCT (SYSTEM_NAME),IP,MAC,MACHINE_DAILY_UPTIME.HOURS,MACHINE_DAILY_UPTIME.DAY,SC.CLIENT_CONNECTED FROM MACHINE LEFT JOIN KBSYS.SMMP_CONNECTION SC ON MACHINE.KUID = SC.KUID LEFT JOIN MACHINE_DAILY_UPTIME ON (MACHINE_DAILY_UPTIME.MACHINE_ID = MACHINE.ID) ORDER BY DAY,CONNECT_TIME,SYSTEM_NAME
  • DISTINCT only grabs unique values from the column. You cannot use one column to be distinct and others non distinct.

    Suppost you have following table:

    COMPUTER VALUE
    A 10
    B 100
    A 20
    B 30

    SELECT DISTINCT(COMPUTER), VALUE
    FROM TABLE
    cannot return anything, sql gets confused

    You can only type:
    SELECT DISTINCT(COMPUTER)
    FROM TABLE

    Result:
    A
    B
Please log in to comment

Answers

0
What are you trying to find?

It looks like your getting duplicates because the up time is being reported for each day
Answered 03/28/2012 by: dchristian
Red Belt

  • I'm running this at 3:06AM after a WOL has been sent at 3:00AM to see how many computers are on & at what time they woke up. Currently the DNS is set with a 8 hour hard release/renew, but we are trying to change this, so the IP isn't changing so frequently and preventing WOL for patches & updates. Currently the DNS has been set to 48 hours to facilitate testing, but I need this data to make the change permanent. Thank You, RTBradley @ ECSU.edu
Please log in to comment
0
Any additional information out there? or a different way to achieve this information. BTW, wol will be sebt over multiple VLAN's 10.1.1.1 style...
Answered 04/02/2012 by: rtbradley
Senior Yellow Belt

Please log in to comment
Answer this question or Comment on this question for clarity

Share