Solved

sql query

Posted on 2014-07-18
3
51 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Citrix XenApp, Internet Explorer 11 set to Enterprise Mode and using central hosted sites.xml file.
Resolve DNS query failed errors for Exchange
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 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…

791 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