none
Software/Inventory Query RRS feed

  • Question

  • I am trying to write a query that will pull back all machine in a collection with only one version of Java installed and exclude all machine that have more than on version installed. Can someone help me with this. This is what I currently have that does not seem to exclude any items.

    select
    distinct SMS_R_System.NetbiosName,
    SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName,
    SMS_R_System.MACAddresses
    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.ProdID = "{26A24AE4-039D-4CA4-87B4-2F83216031FF}"
    and
    (SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName not like "%Java 7%")
    and
    (SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName not like "%java 8%")

    Tuesday, March 15, 2016 7:23 PM

All replies

  • You will need to use subselect queries for this. These will help you

    http://www.enhansoft.com/?s=subselect

    But what you are asking for is not exactly simple.


    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

    Tuesday, March 15, 2016 7:37 PM
    Moderator
  • Dear Sir,

    Only one version? no matter what version? then below may help:

    select
    Name0,DisplayName0, Version0
    from
    (
    select
    COUNT(NAME0) AS number,
    RS.Name0
    ,DisplayName0,
    Version0
    from v_Add_Remove_Programs ARP
    inner join v_R_System RS on ARP.ResourceID = RS.ResourceID
    where DisplayName0 like '%Java 7%' or DisplayName0 like '%Java 8%' or DisplayName0 like '%Java 6%'
    group by Name0, DisplayName0, Version0
    ) as Tempt
    where Tempt.number = 1

    If the 'only one' is a specific version, then you may need to add more condition to filter in subquery.

    Best regards

    Frank


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Wednesday, March 16, 2016 2:42 AM
    Moderator
  • I am trying to write a query that will pull back all machine in a collection with only one version of Java installed and exclude all machine that have more than on version installed. Can someone help me with this.

    Try this Query (SQL but not used for Collection):

    First query (sub selected) is to get list of all computers with JAVA Versions and filter it by having count of PC only 1 which means ,it excludes all the machines that appeared more than once with Java version AND main query is to pull the JAVA information for only single instance (1 version of JAVA) installed.

    select sub.name0,arp1.DisplayName0,arp1.Version0

    from

    (

    select

    vrs.Name0,vrs.ResourceID,

    COUNT(*) Total

    from v_Add_Remove_Programs ARP

    inner join v_R_System vrs on ARP.ResourceID = VRS.ResourceID

    where arp.DisplayName0 like 'Java%'

    group by vrs.Name0,vrs.ResourceID

    having count(vrs.Name0)=1 )Sub

    inner join v_Add_Remove_Programs arp1 on arp1.resourceid=sub.ResourceID

    where arp1.DisplayName0 like 'Java%'

    order by sub.name0,arp1.DisplayName0,arp1.Version0


    Eswar Koneti | Configmgr Blog: www.eskonr.com | Linkedin: Eswar Koneti | Twitter: eskonr





    Wednesday, March 16, 2016 11:48 PM