5 Replies Latest reply on Nov 1, 2011 9:49 AM by Catalysttgj

    Query to exclude




      I'm new to LANDesk (LANDesk Management Suite v9.0.2.3). I have been given access to all machines on a particular sub-net.


      I have a query to search these machine's:   "Computer"."Software","Add or Remove Programs"."Program"."Name" Like " "


      This works and returns about 5,000 instances (essentially all programs installed on all machines).


      If I modify the query to only return instances of "service pack" this also works and returns 224 rows:


      "Computer"."Software","Add or Remove Programs"."Program"."Name" Like "service pack"


      If I invert the query to exclude "service pack" then the query returns zero rows (whereas I would expect it to return 5,000 minus 224 rows):


      "Computer"."Software","Add or Remove Programs"."Program"."Name" Not Like "service pack"


      Any ideas why please?!


      Essentially I want to build a query that I can run weekly, which will search all installed programs, but I want to exclude programs that I don't need licenses for (such as readers, service packs, etc).


      Any help greatly appreciated :-)



        • 1. Re: Query to exclude

          Hi Andy,


          If I understand you correctly, you want to create a query to get a list of programs installed on machines without service pack included. If so, did you try creating a query for the programs to be included (all LIKE) by grouping it together (with multiple ORs) and exclude service pack (with NOT LIKE) by keep it out ouf the group with an AND.


          Please find the screenshot attched.


          I used to do this kinda reports with a SQL query instead of messing around with a big query


          - Regis

          • 2. Re: Query to exclude

            Hi Regis,


            Thanks for the quick reply and suggestions. I really need to find all programs and then exclude based on wildcard (e.g. *Adobe Flash*) rather than write to include. I can't quite see why my current query fails - perhaps I should try to get access to the SQL Server db underneath and write a query using a different interface :-) I hadn't used the Group option previously so thanks for the info on that.



            • 3. Re: Query to exclude
              MarXtar ITSMMVPGroup

              The issue you have is that the query engine only really cares about returning results for the machine. Your first query will give lots of results because those machines HAVE the string you included in at least one of their programs.


              The second query says show me the machines that DO NOT HAVE that string in at least one of their programs.


              Remember that you are querying machines, not software, so if even one piece of software has that string then the machine will be excluded.


              Mark McGinn

              MarXtar Ltd


              LANDesk Silver ESP


              The One-Stop Shop for LANDesk Enhancements

              - Wake-On-WAN - Distributed Wake-On-LAN, Scheduled Power Down, and SWDist Sequencing

              - Inventory Archiving/Change & Root Cause Analysis

              - Self-Service Password Reset

              - PC Power Management

              - Help/Service Desk Integration

              - LANDesk Data Manipulation/Integration/Bar Coding

              - LANDesk Executive Reporting Pack

              - Network Asset Discovery and Management

              - LANDesk Software Store

              • 4. Re: Query to exclude

                Hi MarXtar,


                You are absolutely right. If any machine has a particular string that particular machine will be removed from the results. My bad.


                I think a small SQL query like the below would be helpful.



                SELECT Distinct a.SUITENAME,

                Count (a.SUITENAME) AS 'Instances',






                FROM AppSoftwareSuites a

                JOIN COMPUTER c ON C.COMPUTER_Idn = A.COMPUTER_idn




                AND SUITENAME NOT LIKE 'Security Update%'

                AND SUITENAME NOT LIKE '%Hotfix%'

                AND SUITENAME NOT LIKE '%Service Pack%'




                Group By SUITENAME, Version, Publisher, C.domainName


                Order By SUITENAME



                This query can even be filtered C.DeviceName like 'ABC%', so the machines start with ABC would only be queried against.


                - Regis

                1 of 1 people found this helpful
                • 5. Re: Query to exclude
                  Catalysttgj Expert

                  Also, if this query is going to generate a lot of rows, it might be overloading the memory for the console app. the console app can die pretty quick. Around 1.5 gig of ram and its done! We see this A LOT. If a query brings back too much information, it will just return nothing. You'll be better served to just do the SQL query and bypass the console app with massive amounts of data. Hope that helps!