Solved

sql query

Posted on 2014-07-18
3
32 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
Comment Utility
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
Comment Utility
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

I don't know if many of you have made the great mistake of using the Cisco Thin Client model with the management software VXC. If you have then you are probably more then familiar with the incredibly clunky interface, the numerous work arounds, and …
When you upgrade from Windows 8 to 8.1 or to Windows 10 or if you are like me you are on the Insider Program you may find yourself with many 450MB recovery partitions.  With a traditional disk that may not be a problem but with relatively smaller SS…
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…
Windows 8 came with a dramatically different user interface known as Metro. Notably missing from that interface was a Start button and Start Menu. Microsoft responded to negative user feedback of the Metro interface, bringing back the Start button a…

762 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now