NEW Group by is not working right

[Gustavo]

Hi there,

I am making a report where I try to show the OU for every computer using MAX function, but for some reason that I still cannot see, the report is returning a register for every CN name. I made a similar report using MAX function and it worked, I got just one register per computer with the full OU name, ej: domain.com/OU1/OU2/OU3. Now I am getting this: register1 --> domain.com/OU1
register2--> domain.com/OU1/OU2
register3--> domain.com/OU1/OU2/OU3
..........
So I am getting 5 or 6 registers per computer.

This is my query, I am sure that I am missing something, but I have not been able to find it:

SELECT DISTINCT
v_R_System.Name0 as "Machine Name",
v_R_System.User_Name0 as "User Name",
v_R_System.User_Domain0 as Domain,
MAX(v_RA_System_SystemOUName.System_OU_Name0) as OU,
CASE WHEN v_GS_NETWORK_ADAPTER_CONFIGURATION.IPAddress0 like '%,%' THEN left(v_GS_NETWORK_ADAPTER_CONFIGURATION.IPAddress0,CHARINDEX(',',v_GS_NETWORK_ADAPTER_CONFIGURATION.IPAddress0)-1)
ELSE IPAddress0 END AS "IP Address",
v_GS_OPERATING_SYSTEM.Caption0 as "OS Name",
v_GS_OPERATING_SYSTEM.CSDVersion0 as "SP Name",
v_GS_OPERATING_SYSTEM.BuildNumber0 as "Build Number",
v_GS_WORKSTATION_STATUS.LastHWScan as "Last Contact",
v_GS_COMPUTER_SYSTEM.Manufacturer0 as Fabricante,
v_GS_COMPUTER_SYSTEM.Model0 as Modelo
FROM
dbo.v_R_System
left JOIN v_GS_OPERATING_SYSTEM ON v_R_System.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID
left join v_GS_NETWORK_ADAPTER_CONFIGURATION ON v_R_System.ResourceID=v_GS_NETWORK_ADAPTER_CONFIGURATION.ResourceID
LEFT Join v_GS_WORKSTATION_STATUS on v_R_System.ResourceID=v_GS_WORKSTATION_STATUS.ResourceID
left join v_GS_COMPUTER_SYSTEM on v_R_System.ResourceID=v_GS_COMPUTER_SYSTEM.ResourceID
left join v_RA_System_SystemOUName on v_R_System.ResourceID=v_RA_System_SystemOUName.ResourceID
WHERE
v_GS_NETWORK_ADAPTER_CONFIGURATION.IPAddress0 not like 'fe%'
and v_GS_NETWORK_ADAPTER_CONFIGURATION.IPAddress0 IS NOT NULL
--and v_GS_NETWORK_ADAPTER_CONFIGURATION.IPAddress0 not like '169.254.%'
and v_GS_NETWORK_ADAPTER_CONFIGURATION.DefaultIPGateway0 IS NOT NULL
and v_GS_NETWORK_ADAPTER_CONFIGURATION.IPEnabled0 = '1'
GROUP BY
v_R_System.Name0,v_R_System.User_Domain0,v_R_System.User_Name0,v_GS_NETWORK_ADAPTER_CONFIGURATION.IPAddress0,v_RA_System_SystemOUName.System_OU_Name0,v_GS_OPERATING_SYSTEM.Caption0,v_GS_OPERATING_SYSTEM.CSDVersion0,v_GS_OPERATING_SYSTEM.BuildNumber0,v_GS_WORKSTATION_STATUS.LastHWScan,v_GS_COMPUTER_SYSTEM.Manufacturer0,v_GS_COMPUTER_SYSTEM.Model0

This is the result of the report


Any suggestion or help would be appreciated
Thanks in advance
Best regards
Gustavo

 

[Garth]

That is because you have the OU listed within your group by, remove that it and everything will be fine.

 

[Gustavo]

That is because you have the OU listed within your group by, remove that it and everything will be fine.

Solved!! Thanks for your help and quick response. I write down another beer for you, Garth