locked
SCCM SQL Query to Find Specific Software not Installed RRS feed

  • Question

  • Dears,

    We have SCCM current branch 1806 in our branch and we have Kaspersky installed in all client machines but some of the computers doesn't have Kaspersky installed, I'm looking for way or SCCM SQL query to find the computer which are "Kaspersky Anti-Virus - avp.exe - 6.0. (1033)" not installed. Is there anyway to find this query? 

    Thanks.

    Wednesday, February 13, 2019 7:16 AM

Answers

All replies

  • Hi,

    Here is a sample query on how to achieve that and samples of prompt for software name and more.

    https://blog.techygeekshome.info/2018/01/addremoveprograms/

    Regards,
    Jörgen


    -- My Enterprise Mobility blog ccmexec.com -- Twitter @ccmexec

    • Marked as answer by Dana.Kadi Wednesday, May 8, 2019 6:24 PM
    Wednesday, February 13, 2019 8:02 AM
  • Hello Dana.Kadi,
     
    Considering both x64 and x86 version of the software, check the following query (it's a WQL query).
     
    select SMS_R_System.Name, SMS_R_System.ResourceId, SMS_R_System.IPAddresses from  SMS_R_System where SMS_R_System.ResourceId not 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 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.DisplayName like "%Kaspersky Anti-Virus - avp.exe - 6.0. (1033)%" or SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName like "%Kaspersky Anti-Virus - avp.exe - 6.0. (1033)%")
     
    Hope my answer could help you and look forward to your feedback.
     
    Best Regards,
    Ray

    Please remembers to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Wednesday, February 13, 2019 8:31 AM
  • Wednesday, February 13, 2019 12:08 PM
  • Dear Ray, I have used your query in my environment it showed error "Invalid object name 'SMS_R_Systrem' it means I don't have this field in my environment.
    Thursday, February 14, 2019 6:05 AM
  • Dear Dana.Kadi,
     
    Where did you run the query? It's a WQL query which means you should run it in the SCCM.
     

     
    Hope my answer could help you and look forward to your feedback.
     
    Best Regards,
    Ray

    Please remembers to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Thursday, February 14, 2019 11:44 AM
  • Hello,
     
    Does the query work this time? Feel free to feedback.
     
    Best Regards,
    Ray

    Please remembers to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Friday, February 15, 2019 8:56 AM
  • Dear Ray, the query is working but this query is giving me the computers with Kaspersky installed I need give me the computers which are Kaspersky not installed.
    Friday, February 15, 2019 5:29 PM
  • Dear Ray, the query is working but this query is giving me the computers with Kaspersky installed I need give me the computers which are Kaspersky not installed.
    You need to use a subselect query as I have already pointed out 

    Garth Jones

    Blog: https://www.enhansoft.com/blog Old Blog: https://sccmug.ca/

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

    Friday, February 15, 2019 5:35 PM
  • Thanks Garth, but actually I'm not very good by using  subselect query, can you mention exact query and replace Microsoft Project with "Kaspersky Anti-Virus - avp.exe - 6.0. (1033)".
    Sunday, February 17, 2019 6:03 AM
  • Thanks Garth, but actually I'm not very good by using  subselect query, can you mention exact query and replace Microsoft Project with "Kaspersky Anti-Virus - avp.exe - 6.0. (1033)".
    in a nutshell that is all you have to do with that blog post. did you try to do that?

    Garth Jones

    Blog: https://www.enhansoft.com/blog Old Blog: https://sccmug.ca/

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

    Sunday, February 17, 2019 2:55 PM
  • Hello, 
     
    I actually use the subselect query in my reply above. And I think the issue is due to the keywords. Try using %Kaspersky Anti-Virus% instead of %Kaspersky Anti-Virus - avp.exe - 6.0. (1033)% in the query above and then check the results.
     
    Best Regards,
    Ray

    Please remembers to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Tuesday, February 19, 2019 12:55 PM
  • Ray. Unfortunately you query will only find x64 computers. it will not find x86 computer. 

    This blog will give you more details on why and how to fix the query. 

    https://www.enhansoft.com/does-your-os-architecture-x86-or-x64-matter-within-a-query/


    Garth Jones

    Blog: https://www.enhansoft.com/blog Old Blog: https://sccmug.ca/

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

    Tuesday, February 19, 2019 3:23 PM
  • Hello Garth Jones,
     
    Thanks for your explanation. I have read your blog which is really helpful.
     
    Dana.Kadi, try the following query.
     
    select SMS_R_System.Name, SMS_R_System.ResourceId, SMS_R_System.IPAddresses from  SMS_R_System where SMS_R_System.ResourceId not 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 "%Kaspersky Anti-Virus%") and SMS_R_System.ResourceId not 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 "%Kaspersky Anti-Virus%")
     
    Hope my answer could help you and look forward to your feedback.
     
    Best Regards,
    Ray

    Please remembers to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Wednesday, February 20, 2019 5:07 AM
  • I'm glad you like it, I post lots of helpful tips on my blog. if you have any questions, please feel free to touch base. 

    Garth Jones

    Blog: https://www.enhansoft.com/blog Old Blog: https://sccmug.ca/

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

    Wednesday, February 20, 2019 2:48 PM