none
Trying to do a report of SUP failures by OU

    Question

  • Have a list of SUP failure computers, and trying to do a report counting how many of the computers are in which OU (to see if Discovery or Boundary issue).

    Report is picking up the MAX OU correctly, but, not grouping the computer count (getting a single row per each computer).  Not sure what I'm doing wrong--anyone have a clue?

    Thanks.

    SELECT (select top 1 SOU2.System_OU_Name0 from dbo.v_RA_System_SystemOUName SOU2
         where SOU.ResourceID = SOU2.ResourceID and LEN(SOU2.System_OU_Name0)
          = MAX(LEN(SOU.System_OU_Name0))) AS 'OU'
    , COUNT(DISTINCT SOU.ResourceID) AS 'COUNT'
    
    FROM dbo.v_RA_System_SystemOUName SOU 
                JOIN dbo.v_R_System SYS ON SYS.ResourceID = SOU.ResourceID
    
    WHERE SYS.Netbios_Name0 IN ( 'PHO-PVR63186',
    'WCO-PVP11579',
    'PAL-PVI82336',
    'LOM-PVI46044',
    'LOM-PVI49102',
    'LOM-PVR46148',
    'PUG-PVI92643',
    'RED-PVI54875',
    'REN-PVR27553',
    'WLA-PVR125834')
    
    
    GROUP BY  SOU.ResourceID
    
    ORDER BY 'OU'

    Wednesday, March 19, 2014 3:58 PM

Answers

All replies

  • Give this a trying.

    http://tinyurl.com/k8l9ooz


    http://www.enhansoft.com/

    Wednesday, March 19, 2014 6:32 PM
    Moderator
  • Thanks, Garth...that one didn't give me the single row MAX OUs (at least when using the WHERE command).

    We did figure out the below, that seems to work just fine!

    Select COUNT(DISTINCT SYS.ResourceID) AS 'COUNT', SOU2.System_OU_Name0
    
    FROM dbo.v_R_System SYS  INNER JOIN 
    
    (select ResourceID,
    System_OU_Name0 ,
    ROW_NUMBER() OVER(PARTITION BY ResourceID ORDER BY System_OU_Name0 DESC) AS OUN
    from dbo.v_RA_System_SystemOUName) as  SOU2 ON SOU2.ResourceID  = SYS.ResourceID and OUN = 1
         
    WHERE SYS.Netbios_Name0 IN ( 'PHO-PVR63186',
    'WCO-PVP11579',
    'PAL-PVI82336',
    'LOM-PVI46044',
    'LOM-PVI49102',
    'LOM-PVR46148',
    'PUG-PVI92643',
    'RED-PVI54875',
    'REN-PVR27553',
    'WLA-PVR125834')
    
    
    GROUP BY  SOU2.System_OU_Name0
    
    ORDER BY SOU2.System_OU_Name0
    

    Wednesday, March 19, 2014 7:39 PM
  • opps slight glitch in that one. Try this one instead.

    http://tinyurl.com/m7hmsza


    http://www.enhansoft.com/

    Wednesday, March 19, 2014 8:16 PM
    Moderator
  • This works perfect.  Thanks, Garth!
    Wednesday, March 19, 2014 8:25 PM
  • I might have been a bit hasty, sorry...

    SELECT
        Max(SOU.System_OU_Name0),
        COUNT(*) AS 'COUNT'
    FROM 
        dbo.v_RA_System_SystemOUName SOU 
     
    WHERE SOU.System_OU_Name0 LIKE '%<domain root>/SPECIALIZED SYSTEMS%'
    
    Group by 
        SOU.System_OU_Name0
    ORDER BY
        SOU.System_OU_Name0

    I hid the actual Domain info...

    Where I'm seeing the issue is the query is NOT showing just the actual OU where the client is, but the one(s) above it, too:

    <domain root>/SPECIALIZED SYSTEMS/MEDICAL DEVICES 4
    <domain root>/SPECIALIZED SYSTEMS/MEDICAL DEVICES/PICIS 1
    <domain root>/SPECIALIZED SYSTEMS/MEDICAL DEVICES/PICIS/WORKSTATIONS 1
    <domain root>/SPECIALIZED SYSTEMS/MEDICAL DEVICES/VISTA IMAGING 3

    In the above example, returned from the query, what I need is that ONLY the last two (in italics) OU's be returned, not the first two, which are the superior OU's.

    I understand SCCM pulls in ALL the separate OU's above a resource, but, would there be a way around this?

    Thanks!

    Wednesday, April 02, 2014 3:10 PM
  • SELECT
        Max(SOU.System_OU_Name0),
        COUNT(*) AS 'COUNT'
    FROM 
        dbo.v_RA_System_SystemOUName SOU 
     
    WHERE 
    (
    SOU.System_OU_Name0 LIKE '%<domain root>/SPECIALIZED SYSTEMS/MEDICAL DEVICES/PICIS/Workstations%'
    OR
    SOU.System_OU_Name0 LIKE '%<domain root>/SPECIALIZED SYSTEMS/MEDICAL DEVICES/VISTA IMAGING%'
    )
    
    Group by 
        SOU.System_OU_Name0
    ORDER BY
        SOU.System_OU_Name0


    Standardize. Simplify. Automate.

    Wednesday, April 02, 2014 3:45 PM
    Moderator
  • use this simple one..

    SELECT
    	max(SOU.System_OU_Name0) as 'OU Name'
    FROM
    	v_RA_System_SystemOUName as SOU
    WHERE
    	SOU.System_OU_Name0 like '%SPECIALIZED SYSTEMS%'
    ORDER BY
    	'OU Name'

    check my reference thread here


    cheers!


    ---Pat

    Wednesday, April 02, 2014 6:35 PM