12 Replies Latest reply on Jan 17, 2012 3:35 PM by KyleCompassion

    Creating a new query based on file exist on system

    Rookie

      Hi,

       

      I need to create a query to list down all the systems which have one specific file stored at any perticular location. (e.g. All systems which has abc.bat at c:\documents and settings\all users\Desktop).

       

      Please help me to create such query if possible.

       

      Regards,

       

      Pawan Kumar

        • 1. Re: Creating a new query based on file exist on system
          Rookie

          We recently had to do this on an application that SLM didn't detect tjhe executable. So, this SHOULD work in your case since it is a specific file you are looking for.

           

          Try doing this...

           

          Create a new query (under network view, "core server," queries, my queries) and give it a name.

          Now, here is where it gets tricky....depending on the file location.

          For us, we added the following machine component "Computer, Software, Package, File Name"

          After you do this, if "display scanned values" is checked, it should generate a list.

          Search through here to see if the file name you are looking for displays in this list.

          If it does, simply click on the value and it will paste it to "Edit Values" and click the = sign in the middle.

          The scripts box should display similar to this "Computer"."Software"."Package."File Name" = "filename.fileextention"

          Click save and then double click on the query. This will generate a report showing you which systems currently have that file.

          Alternately, you can create a separate query and change = to "Not Exists" to show which systems do NOT have it installed.

           

          See if this works and, if not, let me know and we'll move on to the next option.

           

           

          • 2. Re: Creating a new query based on file exist on system
            Rookie

            Hi,

             

            Thanks for the help! but this not helps to serve my purpose.

             

            Basically we placed one batch file to all systems using script in all users startup folder (this batch file is running one ppt for users when they login). After running scheduled task to push script. We are messed up, not known how many systems have batch file copied & how many left.

             

            Now i want to run a query which gives the system names where this batch file copied, so that we can run only on systems which does not have batch file copied.

             

            Using your suggestion I am not able to see the result.

             

            Thanks!

             

            Pawan Kumar

            • 3. Re: Creating a new query based on file exist on system
              Apprentice

              You can create a custom definition under patch management to look for the specific checksum/hash of the file that you want the clients to have.

               

              Whatever computers show up as vulnerable will have the file missing.  Then you can set it to autofix so that the clients will get the new file whenever they run their vulscan (daily hopefully) or you can create a repair task and control the deployment.

              • 4. Re: Creating a new query based on file exist on system
                Rookie

                "I need to create a query to list down all the systems which have one specific file stored at any perticular location."

                 

                The instructions I gave you will work if you are looking for a particular file. You can specify that .bat file if it doesn't show up in the list. Been using this religiously here; especially for finding systems that may not have received an updated .dll file for a system upgrade that is critical. Must have misunderstood your intentions, but just going off what information was given. Hope macaddicts idea can accomodate! :-)

                • 5. Re: Creating a new query based on file exist on system
                  Rookie

                  In regards to bgarcia's solution, i can't get this to work.

                   

                  I'm trying to identify systems that dont have broker.crt on them. If I create a query in Software>>Package>>File Name NOT EXISTS broker.crt and add insert it to the query., the query comes out to "Computer"."Software"."Package"."File Name" NOT EXISTS. where "file Name" literally says "file name" and doesn't specify broker.crt. I've tried manually typing broker.crt into the "edit values" field, as well as pick it from the Scanned Values field, and whenever i hit Insert it just puts in 'file name" instead of broker.crt. You can see it in the screenshot below. I literally selected the values i want, hit Insert and get "file name" Not Exists instead of "Broker.crt" Not Exists like i would expect.

                   

                  What am I doing wrong? or is this a known bug? I did some searching and couldn't find anything like this.

                  broker.jpg

                  • 6. Re: Creating a new query based on file exist on system
                    Rookie

                    The "Exists" and "Not Exists" for some reason do not show the identified string. I tried testing on my end and am having the exact same problem as you. I'll play around with it more today and see...

                    • 7. Re: Creating a new query based on file exist on system
                      LANDeskWizrd SSMMVPGroup

                      Yeah, I have seen this before and one could say it's a bug or just a misrepresentation of the word. I think "Not Exists" is actually looking at the inventory entry and not the value, if that makes sense. You could use "Not Like" or maybe even "<>". Try those out and see what results you get.

                      • 8. Re: Creating a new query based on file exist on system
                        Rookie

                        Are you on SP2 or SP3? I'm on SP2 for now, will be upgrading to SP3 in a couple weeks but am interested to find out if it's still a problem in SP3 or not.

                        • 9. Re: Creating a new query based on file exist on system
                          Rookie

                          I'm on 8.8 SP4 with the issue. 

                          • 10. Re: Creating a new query based on file exist on system
                            Rookie

                            Doesnt that just get me results of files that aren't broker.crt? which would find a lot of machines since theres a ton of files that would exist that arent broker.crt or are <>. And it still wouldn't find the machines I'm looking for. But decent idea. I'm wondering if maybe I should do a query to find all machines and then compare it to a query of all machines that DO have broker.crt and see what's missing. It's a horrible way to do it, but at this point it's all I've come up with query wise. I know i could do it via the statistics in Patch and Compliance on how many machines have that definition detected, but I'm trying to provide this to some technicians that only have Web Console access, so they wouldn't be able to use that method.

                            • 11. Re: Creating a new query based on file exist on system
                              Apprentice

                              You should be able to accomplish this by creating a custom definition and then create a query based on this detected "definition". First, create a custom definition to detect the computers that do not have the broker.crt file. I think I used this doc and xml file to get me started last year when I had to do this- (http://community.landesk.com/support/docs/DOC-2397)

                              After creating the definition make sure that it is in the scan folder. The section in the device inventory- "Detected Patch and Compliance Definitions" will list this custom definition if it applies to a particular device. You can then create a query to find what computers are missing the broker.crt file.

                               

                              This worked great for me last year when I needed to replace some outdated certificate files on clients. I used the query in a PSP task instead of using autofix to make sure the right computers were targeted.

                              • 12. Re: Creating a new query based on file exist on system
                                Rookie

                                That is a great idea, thank you Sundiata. I think it will meet my needs!

                                 

                                Regarding the EXISTS and NOT EXISTS quantifier, LANDesk says those values are "just looking to see if the column in the database is Null or Not Null". So that explains that - it's not a bug, just a not very obvious function.

                                 

                                Thanks all.

                                 

                                -Kyle