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
  • Hi Eswar,

    Thank you for your help.

    Need same as SQL Server 2008 and 2012 list, could you please help i have tried to modify the script but its not work out. 


    subu

    Tuesday, September 17, 2019 12:08 PM
  • For SQL version details try this

    select  xyz.ProductName, count(*) as Number_of_Machines from

    (Select distinct v_R_System_Valid.Netbios_Name0 AS "Computer Name",

     v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedName AS "ProductName",

     "Publisher" = CASE  

      when (v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedPublisher is NULL or   v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedPublisher = '-1') then 'Unknown'

     Else v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedPublisher

     End,

     "Version" = CASE when (v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedVersion is NULL or   v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedVersion  = '-1') then 'Unknown'

     Else v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedVersion

     End,

     "Install Date" = CASE when (v_GS_INSTALLED_SOFTWARE_CATEGORIZED.InstallDate0 is NULL ) then 'Unknown'

     Else CAST(v_GS_INSTALLED_SOFTWARE_CATEGORIZED.InstallDate0 as varchar)

     End,

     "Registered User" = CASE when (v_GS_INSTALLED_SOFTWARE_CATEGORIZED.RegisteredUser0 is NULL or   v_GS_INSTALLED_SOFTWARE_CATEGORIZED.RegisteredUser0 = '-1') then 'Unknown'

     Else v_GS_INSTALLED_SOFTWARE_CATEGORIZED.RegisteredUser0

     End

    FROM v_GS_INSTALLED_SOFTWARE_CATEGORIZED

    INNER JOIN v_R_System_Valid on v_R_System_Valid.ResourceID = v_GS_INSTALLED_SOFTWARE_CATEGORIZED.ResourceID

    JOIN v_GS_OPERATING_SYSTEM ON v_GS_INSTALLED_SOFTWARE_CATEGORIZED.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID

    Where v_GS_OPERATING_SYSTEM.Caption0 like '%server%' and

     v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedName like '%SQL%' and

     v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedName not like '%arcserve%' and

     v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedName not like '%hotfix%' and

     v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedName not like '%books%' and

     v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedName not like '%setup support%'

    --order by v_R_System_Valid.Netbios_Name0,v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedName, Publisher, Version

    )xyz

    group by xyz.ProductName

    Tuesday, September 17, 2019 12:20 PM
  • Hi Kalyan,

    I have used the above Query but i did not get any result. please help me on this.


    subu

    Tuesday, September 17, 2019 12:45 PM
  • Hi Kalyan,

    I have used the above Query but i did not get any result. please help me on this.


    subu

    Exactly what error are you getting? 

    Garth Jones

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

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

    Tuesday, September 17, 2019 1:21 PM
    Moderator
  • Hi Garth,

    When i run the above query getting output without any information.

    Product Name      No.Of Machines 

    no data on the below 


    subu

    Tuesday, September 17, 2019 1:49 PM
  • Hi Garth,

    When i run the above query getting output without any information.

    Product Name      No.Of Machines 

    no data on the below 


    subu

    So there is nothing wrong with the query. It means you don't match what you are querying for. 

    Garth Jones

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

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

    Tuesday, September 17, 2019 2:09 PM
    Moderator
  • no we have installed SQL server 2008,2012,2014 more than 100 systems but i need exact list of all Servers 

    Sql server installed Servers list and not installed SQL Server list. 


    subu

    Tuesday, September 17, 2019 2:23 PM

  • Sql server installed Servers list and not installed SQL Server list. 


    Have you tried this method ? https://home.configmgrftw.com/sql-server-inventory-using-configmgr/



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

    Tuesday, September 17, 2019 3:31 PM
  • Hi All 


    I have write like this but  arp. ResourceID is not available in Add_Remove_Programs_Data  Kindly advice correct table name need list of SQL Server installed servers list.

    select vr.Name0, arp.displayname00 from v_R_System vr inner join Add_Remove_Programs_DATA arp on vr.ResourceID = arp.ResourceID where arp.DisplyaName00 like '%SQLServer%'


    subu

    Wednesday, September 18, 2019 12:19 PM
  • Hi SMEs,

    Kindly help on this last two day working on this report please share Query or any alternate method to pull all SQL Server list from SCCM or SQL database also fine.


    subu

    Wednesday, September 18, 2019 4:55 PM
  • Hi All 


    I have write like this but  arp. ResourceID is not available in Add_Remove_Programs_Data  Kindly advice correct table name need list of SQL Server installed servers list.

    select vr.Name0, arp.displayname00 from v_R_System vr inner join Add_Remove_Programs_DATA arp on vr.ResourceID = arp.ResourceID where arp.DisplyaName00 like '%SQLServer%'


    subu

    First it is never supported to query the tables directly. 

    secondly. 

    Select 
    	RV.Netbios_Name0,
    	ARP.DisplayName0
    From 
    	dbo.v_R_System_Valid RV
    	join dbo.v_Add_Remove_Programs ARP on RV.ResourceID = ARP.ResourceID
    Where
    	ARP.DisplayName0 like '%SQL%'


    Garth Jones

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

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

    • Proposed as answer by subu999 Saturday, September 21, 2019 11:01 AM
    Wednesday, September 18, 2019 6:13 PM
    Moderator
  • Hi SMEs,

    Kindly help on this last two day working on this report please share Query or any alternate method to pull all SQL Server list from SCCM or SQL database also fine.


    subu

    You know that there is a built-in report that will show you all software installed on all computers. You can use it and its drilldowns to get this info. 

    Count of all instances of software registered with Add or Remove Programs


    Garth Jones

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

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

    Wednesday, September 18, 2019 6:14 PM
    Moderator
  • Hi Garth,

    How can we create built in reports please suggest me.


    subu

    Thursday, September 19, 2019 2:38 AM
  • Hi Garth,

    How can we create built in reports please suggest me.


    subu

    Learn the course (SSRS) to create reports .These will help you to start learning https://www.systemcenterdudes.com/sccm-report-creation-report-builder/ https://docs.microsoft.com/en-us/sccm/core/servers/manage/creating-custom-report-models-in-sql-server-reporting-services

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

    Thursday, September 19, 2019 2:57 PM
  • Hi Garth,

    How can we create built in reports please suggest me.


    subu

    I would look at my book on how to do this. https://www.amazon.com/System-Configuration-Manager-Reporting-Unleashed/dp/0672337789/

    It gives you a step by step on creating reports. 


    Garth Jones

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

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

    Thursday, September 19, 2019 3:00 PM
    Moderator
  • Hi All,

    Thank you so much your help i have pulled the reports as per my requirements 


    subu

    Saturday, September 21, 2019 11:02 AM