locked
Report - Computers without specific software registered in Add Remove Programs RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote
    Hi .

    I am looking to build a report that will show all systems in a specific collection that do not have a specific program registered under the add/remove progams .
    I have tried to build a quer myself but it dosent work pretty well for me since i gives me duplicatets and fulse positives .

    Does anyone have a similer report created that is willing to share the sql code ?

    Thanks
    Thursday, June 4, 2009 12:52 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    Display name should not be quaotes

    Try this :

    Select Distinct
    sys.Netbios_Name0,
    sys.User_Domain0,
    sys.User_Name0
    FROM
    v_R_System sys
    JOIN v_Add_Remove_Programs arp ON sys.ResourceID = arp.ResourceID
    WHERE
    sys.ResourceID not in (select sys.ResourceID
    from
    v_R_System sys
    JOIN v_Add_Remove_Programs arp ON sys.ResourceID = arp.ResourceID
    where
    DisplayName0 like '%Symantec EndPoint Protection%')

    without any additional prompt

    //Eswar Koneti @ www.eskonr.com

    Monday, August 1, 2011 12:42 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote
    Hi,

    This query will return all computers without .Ne Framework 3.5 SP1 installed

    select distinct SMS_R_System.Name from  SMS_R_System where SMS_R_System.Name not in (select distinct SMS_R_System.Name 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 = "Microsoft .NET Framework 3.5 SP1")

    If you want to create a query you can use this step by step guide - http://agerlund.spaces.live.com/blog/cns!3A51A2B50B5C1F51!357.entry
    Kent Agerlund | http://agerlund.spaces.live.com/blog/
    Thursday, June 4, 2009 1:14 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    hi,

    I am getting error in SCCM 2007?

    possible to help?

    Wednesday, August 26, 2009 12:08 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    What is the error?

    Standardize. Simplify. Automate.
    Wednesday, August 26, 2009 12:24 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    anyway i was looking to find a quarry to return all station without the configuration manager  client agents. and i thought if i replace' Microsoft .NET Framework 3.5 SP1' As shown in my friends query above i would succeed. but then then it brings the below error.

     if there is any one with the quarry to return all station without the configuration manager  client agents. you can help.

     

    Msg 208, Level 16, State 1, Line 1

    Invalid object name 'SMS_R_System'.

    JOhn Mukundo IT Sytem Admin Telecom
    Wednesday, June 15, 2011 11:29 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    I dont think SCCM client makes its entry in Add/Remove as SMS Does not do that.

    I think there is easy way to do it .

    Goto collection All System (Containing all the machines) in the collection sort the machines with Client type machines not showing clients does not have client or they may have some other issue (WMI corrupt) or some other issues.

     

    Wednesday, June 15, 2011 11:38 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    <form style="MARGIN-TOP: 0px; MARGIN-BOTTOM: 0px" action="report.asp" enctype="application/x-www-form-urlencoded" method="post">
    Hi Kent,
    I am getting the follwing error:
    An error occurred when the report was run. The details are as follows:
    Invalid object name 'SMS_R_System'.
    Error Number: -2147217865
    Source: Microsoft OLE DB Provider for SQL Server
    Native Error:

    208
    </form>

    This is the same query which you have provided replaced with "Symantec Endpoint Protection".

     

    select distinct SMS_R_System.Name from  SMS_R_System where SMS_R_System.Name not in (select distinct SMS_R_System.Name 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 = "Symantec Endpoint Protection")

     

    Can you please tell me what I am missing...?

     

    Many thanks,

    Ameen.

     

    Mohammad Ameenuddin
    Monday, August 1, 2011 10:32 AM
  • Question
    Sign in to vote
    1
    Sign in to vote

    select distinct SMS_R_System.Name from  SMS_R_System where SMS_R_System.Name not in (select distinct SMS_R_System.Name 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 = "Symantec Endpoint Protection")

     

    Can you please tell me what I am missing...?

     


    Yes. The quoted query is WQL and is used for creating collections.
    You have to use SQL in webreports.
    Torsten Meringer | http://www.mssccmfaq.de
    Monday, August 1, 2011 10:34 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Thank you very much Tor,

    All I need is pull report from SCCM for  computers on which Symantec Endpoint Protection is not installed.

    I have created report in SCCM with the above query but no luck.

     

    May I know how can I acheive this...?

     

    Many thanks,

    Ameen

    Mohammad Ameenuddin
    Monday, August 1, 2011 10:54 AM
  • Question
    Sign in to vote
    1
    Sign in to vote

    post ur query ?

     

    take a look at thsi blog post to list computers whcih do not have software installled onto them ?

    http://eskonr.com/2010/01/sccm-report-for-applications-installled-on-computers-with-without/ and select the application from list of avilable .

     

    Select Distinct
    sys.Netbios_Name0,
    sys.User_Domain0,
    sys.User_Name0
    FROM
    v_R_System sys
    JOIN v_Add_Remove_Programs arp ON sys.ResourceID = arp.ResourceID
    WHERE
    sys.ResourceID not in (select sys.ResourceID
    from
    v_R_System sys
    JOIN v_Add_Remove_Programs arp ON sys.ResourceID = arp.ResourceID
    where
    DisplayName0 = @displayname)

    prompt for Display name :

    select DisplayName0 from v_Add_Remove_Programs

    //Eswar Koneti @ www.eskonr.com
    Monday, August 1, 2011 11:12 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Thanks Eswar,

    I have removed earlier query and I have tried by pasting your query in report properties.I get an error saying must declare the scalar variable "@display name"

     

    Select Distinct
    sys.Netbios_Name0,
    sys.User_Domain0,
    sys.User_Name0
    FROM
    v_R_System sys
    JOIN v_Add_Remove_Programs arp ON sys.ResourceID = arp.ResourceID
    WHERE
    sys.ResourceID not in (select sys.ResourceID
    from
    v_R_System sys
    JOIN v_Add_Remove_Programs arp ON sys.ResourceID = arp.ResourceID
    where
    DisplayName0 = @displayname)

     

    Mohammad Ameenuddin
    Monday, August 1, 2011 11:45 AM
  • Question
    Sign in to vote
    1
    Sign in to vote

    Replace "DisplayName0 = @displayname" with "DisplayName0 like '%Symantec Endpoint Protection%'"

    Torsten Meringer | http://www.mssccmfaq.de
    Monday, August 1, 2011 11:49 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

     

     

    I am pasting this whole query but sql doesnt accept.Am I doing the right thing...?

    ********************************************************

    Select Distinct
    sys.Netbios_Name0,
    sys.User_Domain0,
    sys.User_Name0
    FROM
    v_R_System sys
    JOIN v_Add_Remove_Programs arp ON sys.ResourceID = arp.ResourceID
    WHERE
    sys.ResourceID not in (select sys.ResourceID
    from
    v_R_System sys
    JOIN v_Add_Remove_Programs arp ON sys.ResourceID = arp.ResourceID
    where
    "DisplayName0 like '%Symantec EndPoint Protection%'"

    prompt for Display name :

    select DisplayName0 from v_Add_Remove_Programs

     

    *****************************************

     

    Many thanks,

    Ameen

    Mohammad Ameenuddin
    Monday, August 1, 2011 12:24 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Display name should not be quaotes

    Try this :

    Select Distinct
    sys.Netbios_Name0,
    sys.User_Domain0,
    sys.User_Name0
    FROM
    v_R_System sys
    JOIN v_Add_Remove_Programs arp ON sys.ResourceID = arp.ResourceID
    WHERE
    sys.ResourceID not in (select sys.ResourceID
    from
    v_R_System sys
    JOIN v_Add_Remove_Programs arp ON sys.ResourceID = arp.ResourceID
    where
    DisplayName0 like '%Symantec EndPoint Protection%')

    without any additional prompt

    //Eswar Koneti @ www.eskonr.com

    Monday, August 1, 2011 12:42 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Thank you very much Eswar,

    This works.

     

    Have a nice week.

    Mohammad Ameenuddin
    Monday, August 1, 2011 1:08 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi all,

    How would I limit this to a prompted specific collection?  Prompt for software Name, instead of useing Symantec Endpoint?

    thanks,

    Mark

    Wednesday, November 14, 2012 7:10 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    This one works for SCCM version 1006, finds machines missing 'Microsoft .NET Framework 4.5.2'

    Note there is another view, v_GS_ADD_REMOVE_PROGRAMS for non x64 that you might have to search also

    **********************************

    select

    v_R_SYSTEM.ResourceID,v_R_SYSTEM.ResourceType,v_R_SYSTEM.Name0,v_R_SYSTEM.SMS_Unique_Identifier0,v_R_SYSTEM.Resource_Domain_OR_Workgr0,v_R_SYSTEM.Client0

    from

    v_R_SYSTEM inner join v_GS_COMPUTER_SYSTEM

    on v_GS_COMPUTER_SYSTEM.ResourceID = v_R_SYSTEM.ResourceId

    where

    v_GS_COMPUTER_SYSTEM.Name0 not in

    (select distinct v_GS_COMPUTER_SYSTEM.Name0

    from  v_R_SYSTEM

    inner join v_GS_COMPUTER_SYSTEM

    on v_GS_COMPUTER_SYSTEM.ResourceID = v_R_SYSTEM.ResourceId

    inner join v_GS_ADD_REMOVE_PROGRAMS_64

    on v_GS_ADD_REMOVE_PROGRAMS_64.ResourceID = v_R_SYSTEM.ResourceId

    where v_GS_ADD_REMOVE_PROGRAMS_64.DisplayName0 like 'Microsoft .NET Framework 4.5.2'

    )

    order by Name0

    Friday, September 30, 2016 2:01 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    This one works for SCCM version 1006, finds machines missing 'Microsoft .NET Framework 4.5.2'

    Note there is another view, v_GS_ADD_REMOVE_PROGRAMS for non x64 that you might have to search also

    **********************************

    select

    v_R_SYSTEM.ResourceID,v_R_SYSTEM.ResourceType,v_R_SYSTEM.Name0,v_R_SYSTEM.SMS_Unique_Identifier0,v_R_SYSTEM.Resource_Domain_OR_Workgr0,v_R_SYSTEM.Client0

    from

    v_R_SYSTEM inner join v_GS_COMPUTER_SYSTEM

    on v_GS_COMPUTER_SYSTEM.ResourceID = v_R_SYSTEM.ResourceId

    where

    v_GS_COMPUTER_SYSTEM.Name0 not in

    (select distinct v_GS_COMPUTER_SYSTEM.Name0

    from  v_R_SYSTEM

    inner join v_GS_COMPUTER_SYSTEM

    on v_GS_COMPUTER_SYSTEM.ResourceID = v_R_SYSTEM.ResourceId

    inner join v_GS_ADD_REMOVE_PROGRAMS_64

    on v_GS_ADD_REMOVE_PROGRAMS_64.ResourceID = v_R_SYSTEM.ResourceId

    where v_GS_ADD_REMOVE_PROGRAMS_64.DisplayName0 like 'Microsoft .NET Framework 4.5.2'

    )

    order by Name0

    Hi Adam, just an fyi, It is best to use resourceid instead of name in your subselect query , additionally you should use v_add_remove_programs SQL view as it will get both x86 and x64 software titles.

    Garth Jones

    Blog: http://www.enhansoft.com/blog Old Blog: http://smsug.ca/blogs/garth_jones/default.aspx

    Twitter: @GarthMJ Book: System Center Configuration Manager Reporting Unleased

    Friday, September 30, 2016 2:52 PM