Solved

sql query

Posted on 2014-07-18
3
75 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 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 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Ever notice how you can't use a new drive in Windows without having Windows assigning a Disk Signature?  Ever have a signature collision problem (especially with Virtual Machines?)  This article is intended to help you understand what's going on and…
Citrix XenApp, Internet Explorer 11 set to Enterprise Mode and using central hosted sites.xml file.
In this Micro Tutorial viewers will learn how to use Windows Server Backup to create full image of their system. Tutorial shows how to install Windows Server Backup Feature on Windows 2012R2 and how to configure scheduled Bare Metal Recovery backup.…
This tutorial will walk an individual through the process of installing of Data Protection Manager on a server running Windows Server 2012 R2, including the prerequisites. Microsoft .Net 3.5 is required. To install this feature, go to Server Manager…

624 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