Having trouble with a Dell KACE report, multiple duplicate computer names - Modifying SQL for a UniqueIdentifier or Distinct variable to eliminate duplicates
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
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
Answers (3)
Please log in to answer
Posted by:
scriptingninja
12 years ago
Basically you need to add in a SELECT DISTINCT statement. Example SELECT DISTINCT 'MACHINE.NAME' WHERE etc etc...
Comments:
-
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 - rtbradley 12 years ago -
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 - haseebiqbal 10 years ago
Posted by:
dchristian
12 years ago
What are you trying to find?
It looks like your getting duplicates because the up time is being reported for each day
It looks like your getting duplicates because the up time is being reported for each day
Comments:
-
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 - rtbradley 12 years ago
Posted by:
rtbradley
12 years ago
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...