1 Reply Latest reply on Sep 19, 2014 7:28 PM by Catalysttgj

    Need Help Creating Query to Find Computers Without Updated Program


      Hi everyone,


      I'm fresh to operating LANdesk and myself and others in our staff are really racking our brains about how to resolve this. So, being a total n00b, I'll try to explain this as best possible. And please be simple in response! (Anyone have a LANdesk for dummies?)


      I'm trying to create a query to find computers that do not have a specific version of a program installed. I figured the simple answer is to just use "not exist" and the title/version of the program I'm looking for.But that doesn't seem to work like that. I can't effectively do a search of all previous versions, because the program can run the new version with or without the old version. We need to find what PC's do not have the program installed so we can make sure to get the program pushed out or get to it by foot.



        • 1. Re: Need Help Creating Query to Find Computers Without Updated Program
          Catalysttgj Expert

          The "Not Exists" is actually stating that there is no instance of whatever you're referring to. So something like:

          "Computer"."Software"."Add or Remove Programs"."Program"."Name" Not Exists

          would mean that there isn't any software in a device inventory at all, as its not stating that a particular name of a software doesn't exist, but rather the object "Name" does not exist, which will effectively only return devices that have no software in their inventory. This would be pretty rare, but not impossible. This type of query would likely return device entries that are of perhaps empty device records like inventory that's been inserted through CSV import, or Data Analytics import of bare metal devices that do not actually have agents on them yet. There's a few other possibilities, but basically it wouldn't be the type of list you are looking for obviously.


          Use the "<>" evaluation instead...


          So either go by what appears under "Add/Remove Programs":

          "Computer"."Software"."Add or Remove Programs"."Program"."Name" <> "Whatever you need here."


          or perhaps you want to go by filename located in a specific path on a system, so something like this:

          "Computer"."Software"."Package"."Path" <> "C:\Program Files\... blah blah blah..\something.exe" <-- make sure its precise!


          In either case the "<>" evaluation is basically stating that the inventory of a device can not have the particular item in its list of items, so you just have to be mindful of what the query evaluation really means. Sometimes you're going to get a result that isn't really what you want, but its all how you think about what the query is really designed to do.


          To go a step further... you may need to get only a list of devices that have an OLD version of a particular software.

          In this case, you would need a compound query, so something that looks like this:


          "Computer"."Software"."Package"."Path" = "C:\Program Files\... blah blah blah..\something.exe"

          AND "Computer"."Software"."Package"."Path" < "desired version info here"

          Notice the first line has the "=" evaluation. This will cause the query to only provide the devices that have the software in question, but then the following line uses the "AND" operator which will constrain the list of returned devices even further to only provide those machines that have a version of the software less than what is specified. Keep in mind that "AND" operators will not require grouping as long as only the "AND" operator is used between each line. The same is also true for only using "OR" operators, but when both operators are used in the same query grouping will be required.


          You may need to include a further filter for operating system or something like this, but you might want it to be a little loose, so that it includes several, so something like this works well:


          "Computer"."OS"."Name" Like "Windows"

          AND "Computer"."Software"."Package"."Path" = "C:\Program Files\... blah blah blah..\something.exe"

          AND "Computer"."Software"."Package"."Path" < "desired version info here"


          This would get every device that has the word Windows occurring in this particular "Name" field.


          Further you might need to only get a list of workstations, and perhaps not servers, so this would get it done:


          "Computer"."OS"."Name" Like "Windows"

          AND "Computer"."OS"."NT Info"."Server" = "No"

          AND "Computer"."Software"."Package"."Path" = "C:\Program Files\... blah blah blah..\something.exe"

          AND "Computer"."Software"."Package"."Path" < "desired version info here"


          Note: Since the "Server" item is beneath "NT Info", its not useful in evaluating other types of systems like Macs or other nix based OS's. Not that it matters here, but you just have to watch out for these types of things. Some data is only going to be useful against some devices, and so queries need to be robust enough to accommodate this.


          Finally, you might find that you need to pick out specific versions of software to update, while also including systems that do not have the software at all:


          "Computer"."OS"."Name" Like "Windows"

          AND "Computer"."OS"."NT Info"."Server" = "No"

          AND ("Computer"."Software"."Package"."Path" <> "C:\Program Files\... blah blah blah..\something.exe"

          OR ("Computer"."Software"."Package"."Path" = "C:\Program Files\... blah blah blah..\something.exe"

          AND "Computer"."Software"."Package"."Path" < "desired version info here”))


          In this query you would need to highlight only the last two items, and then group them. Then you would need to highlight the last three items and group them as well. This would make the query perform properly. Think algebra here, and you got it right. First the inner group gets evaluated, then the outer group, and then the rest, so the logic works out to be:

          It has to be a windows machine, AND its not a server, AND the software isn't there OR the software is there, but its a lesser version.


          Okay, I'm done.. and there's a basic primer for LDMS queries. Hopefully, its useful!