Solved

sql query

Posted on 2014-07-18
3
55 Views
Last Modified: 2016-06-09
Hi ,

Need your help! i'm not a sql guy and dont want to get the following info from a query limited to a following collection:

In sccm 2012, i goto reporting - queries -> create query

select Distinct SMS_R_System.NetbiosName from  SMS_R_System where SMS_R_System.ResourceId in (select SMS_R_System.ResourceID  from  SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "%adobe%") or SMS_R_System.ResourceId in (select SMS_R_System.ResourceID from  SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS_64 on SMS_G_System_ADD_REMOVE_PROGRAMS_64.ResourceID = SMS_R_System.ResourceId where  SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName like  "%adobe%") order by SMS_R_System.NetbiosName

the problem with this query is that it displays only computer names, i need the output in the below format:

Computer(s) Name                                           App Name(that sccm has searched for)
Computer1, Computer3                                  Adobe Acrobat
Computer2, Computer4, computer1            Adobe Flash

Thanks in advance!
Regards,
Ranganath
0
Comment
Question by:Ranganath2014
  • 2
3 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40206833
For a tabluar result like this:
DisplayName   NetbiosName 
Adobe Acrobat Computer1
Adobe Flash   Computer1
Adobe Acrobat Computer3
Adobe Flash   Computer3
Adobe Acrobat Computer2
Adobe Flash   Computer2

Open in new window

(untested) try this:
SELECT
        SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName
      , SMS_R_System.NetbiosName
FROM SMS_R_System
      INNER JOIN SMS_G_System_ADD_REMOVE_PROGRAMS
                  ON SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId
WHERE SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName LIKE "%adobe%"

UNION

SELECT
        SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName
      , SMS_R_System.NetbiosName
FROM SMS_R_System
      INNER JOIN SMS_G_System_ADD_REMOVE_PROGRAMS_64
                  ON SMS_G_System_ADD_REMOVE_PROGRAMS_64.ResourceID = SMS_R_System.ResourceId
WHERE SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName LIKE "%adobe%"

Open in new window

0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40206841
For this style of result:
|   DISPLAYNAME |                        COMPUTERS |
|---------------|----------------------------------|
| Adobe Acrobat |  Computer1, Computer2, Computer3 |
|   Adobe Flash |  Computer1, Computer2, Computer3 |

Open in new window

you need a combination of "for xml path" and stuff() to arrive at the comma separated string.
;WITH
      CTE
      AS (
                  SELECT
                        SMS_R_System.ResourceID
                  FROM SMS_R_System
                        INNER JOIN SMS_G_System_ADD_REMOVE_PROGRAMS
                                    ON SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId
                  WHERE SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName LIKE "%adobe%"
                  UNION

                        SELECT
                              SMS_R_System.ResourceID
                        FROM SMS_R_System
                              INNER JOIN SMS_G_System_ADD_REMOVE_PROGRAMS_64
                                          ON SMS_G_System_ADD_REMOVE_PROGRAMS_64.ResourceID = SMS_R_System.ResourceId
                        WHERE SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName LIKE "%adobe%"
            )
SELECT DISTINCT
      DisplayName
    , STUFF((
            SELECT
                  ', ' + CTE2.NetbiosName
            FROM CTE AS CTE2
            WHERE CTE2.DisplayName = CTE.DisplayName
            ORDER BY
                  CTE2.NetbiosName
            FOR xml PATH ('')
      )
      , 1, 1, '') AS Computers
FROM CTE

Open in new window

0

Featured Post

Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Issue: One Windows 2008 R2 64bit server on the network unable to connect to a buffalo Device (Linkstation) with firmware version 1.56. There are a total of four servers on the network this being one of them. Troubleshooting Steps: Connect via h…
Restoring deleted objects in Active Directory has been a standard feature in Active Directory for many years, yet some admins may not know what is available.
In this Micro Tutorial viewers will learn how they can get their files copied out from their unbootable system without need to use recovery services. As an example non-bootable Windows 2012R2 installation is used which has boot problems.
With the advent of Windows 10, Microsoft is pushing a Get Windows 10 icon into the notification area (system tray) of qualifying computers. There are many reasons for wanting to remove this icon. This two-part Experts Exchange video Micro Tutorial s…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question