12 Replies Latest reply on Oct 1, 2008 2:09 AM by phoffmann

    Is this type of query possible?

    Apprentice

      We have an issue with users not rebooting their computers often enough. I have created a query to display users that have not rebooted  in one week. Is there a way to create a query to display  the data for 1 week from today instead of having to modify the date in the query manually every time the query is run?

       

       

      LMDS 8.7 SP5+Patch Manager

        • 1. Re: Is this type of query possible?
          MarXtar ITSMMVPGroup

          Try creating a query where your attribute < GETDATE()-7

           

          Put this in the text box for the query instead of choosing a value.  In my testing, this works against LANDesk date fields.

           

          Mark Star - MarXtar LANDesk Enhancements

          Home of Power State Notifier & Wake-On-WAN for LANDesk

          1 of 1 people found this helpful
          • 2. Re: Is this type of query possible?
            phoffmann SupportEmployee

            Not easily, since Query Analyzer is trying to "make SQL look simple" - and the problem with that is that you'll run into problems once you're trying to do actually complicated stuff :).

             

            The concern if "just" relying on inventory data, would be that you'd have to be able to cross-check "last scan date" (i.e. - the device was actually up) at the same time as scanning for the OS uptime.

             

            There's a potentially "easier" way to do this (well - I say "easier") ... we pull the up-time from WMI. You could write a VBscript that pulls this information too, and compares it against system time, and calculates the "not rebooted in"... time out of it (for example).

             

            Then this data could be collected as custom data (i.e. - a value in the registry) ... this would allow for a "single value check" for custom data, where you could make sure that the value has to be larger than 7 days, or larger than 604800 seconds (7 days in seconds). This would be "nice and constant", and the only work you'd have to do would be in getting this script to (1) work and (2) run regularly - which can be done via local scheduler script and/or AD logon policy or whatever you prefer.

             

            Hope this helps nudge you in the direction of something that'll work for you.

             

            Paul Hoffmann

            LANDesk EMEA Technical Lead

            • 3. Re: Is this type of query possible?
              phoffmann SupportEmployee

              Interesting - should try this ... I can see how that'd work ... :).

               

              Nice thinking!

               

              Paul Hoffmann

              LANDesk EMEA Technical Lead

              • 4. Re: Is this type of query possible?
                ahe Expert

                Hello Mark,

                 

                same idea, but too slow to write...

                 

                Additionally this could be a good place:

                Computer - OS - NT Info - Last Boot Up Time <= getdate()-7

                 

                Best regards

                Axel

                 

                Message edited by ahe It seems this query does not work correct in 8.7, in 8.5 it did... :-| (changed date field?)

                • 5. Re: Is this type of query possible?
                  MarXtar ITSMMVPGroup

                  As a wise man once said...

                   

                  Ya Snooze, Ya Lose.  ;)

                   

                  Mark Star - MarXtar LANDesk Enhancements

                  Home of Power State Notifier & Wake-On-WAN for LANDesk

                  • 6. Re: Is this type of query possible?
                    ahe Expert

                     

                     

                    But I found it... too...(after I read and type my older query correct...)

                     

                    The better AND working field is:

                     

                    "Computer"."OS" ."Last Start Up Time" <= "getdate()-7"

                     

                     

                    This shows all clients which are not rebooted in the last seven days. If you want to see which clients have rebooted in thes time (that's the probe):

                     

                    "Computer"."OS" ."Last Start Up Time" >= "getdate()-7"

                     

                     

                    Regards

                    Axel

                    • 7. Re: Is this type of query possible?
                      MarXtar ITSMMVPGroup

                      No! Get off! They're my points I tell you, MINE!   <maniacal laughter>

                       

                      Mark Star - MarXtar LANDesk Enhancements

                      Home of Power State Notifier & Wake-On-WAN for LANDesk

                      • 8. Re: Is this type of query possible?
                        ahe Expert

                        Nice closing time Mark... I go home... (it seems Paul is gone too)

                        • 9. Re: Is this type of query possible?
                          SupportEmployee

                          Just an addendum here. The GETDATE()-7 is fine if your are using MS SQL. However, if you are on an oracle database you will want to use SYSDATE -7.

                           

                           

                          • 10. Re: Is this type of query possible?
                            Apprentice

                            Thanks for all the responses.Paul Hoffman had a good idea  on also checking the last scan dates reported by LANDesk. There were also other factors to consider, since we are a worldwide company being serviced by a single server, we do not want to start reboots in the middle of the day for a guy in London.

                             

                            Is the getdate trick documented anywhere (I do not remember seeing it before)

                            • 11. Re: Is this type of query possible?
                              phoffmann SupportEmployee

                              It won't be documented in LANDesk documentation, because it's actually using SQL. Technically, what Axel / Mark suggested is "a bit of a hack", in that they're inserting a straight SQL function into what should be "pretty simplistic" SQL - though it's a very benign hack (and just shows that they're good at what they're doing).

                               

                              In the end, the LANDesk Queries are all translated into specific SQL which gets queried against the database - if you have good knowledge of SQL and databases, you can make this work to your advantage (as the above two known suspects have done) .

                               

                              Paul Hoffmann

                              LANdesk EMEA Technical Lead

                              • 12. Re: Is this type of query possible?
                                phoffmann SupportEmployee

                                And just to add, Axel - just 'cos I'm not here doesn't necessarily mean I'm gone. End of quarter is (strangely enough) one of those periods where I am often busy with other stuff as well.

                                 

                                One of the many reasons why I'm of the opinion that it will be tech support in the end and not conventional science who'll come up with a way to make human cloning possible .

                                 

                                Paul Hoffmann

                                LANDesk EMEA Technical Lead