Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

sql query

Posted on 2014-07-18
3
Medium Priority
?
89 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
2 Comments
 
LVL 49

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 49

Accepted Solution

by:
PortletPaul earned 2000 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Resolve DNS query failed errors for Exchange
A procedure for exporting installed hotfix details of remote computers using powershell
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.
This tutorial will walk an individual through the steps necessary to join and promote the first Windows Server 2012 domain controller into an Active Directory environment running on Windows Server 2008. Determine the location of the FSMO roles by lo…

824 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