6 Replies Latest reply on May 18, 2017 9:44 AM by brad.e.smith

    Query help

    brad.e.smith Apprentice

      LDMS 9.6 SP3

       

      I'm looking to create a query for AMT SCS Information> firmware version in our environment. The versioning across our 3600+ nodes runs a broad scope of ranges from 5.2.10.1023 all the way to 11.0.18.1002 with multiple variations in between. The challenge in this case [for us] is we're only wanting to target last 4 digits in the versioning for versions greater than *.*.*.3000

       

      "Computer"."AMT Information"."AMT SCS Information"."Firmware Version"  >  "5."

       

      ("Computer"."AMT Information"."AMT SCS Information"."Firmware Version"  >  "5."

      and "Computer"."AMT Information"."AMT SCS Information"."Firmware Version"  >  "*.3000")

       

      ("Computer"."AMT Information"."AMT SCS Information"."Firmware Version"  >  "5."

      and "Computer"."AMT Information"."AMT SCS Information"."Firmware Version"  >  "%.3000")

       

      "Computer"."AMT Information"."AMT SCS Information"."Firmware Version"  >  "*.3000")

       

      "Computer"."AMT Information"."AMT SCS Information"."Firmware Version"  >  "%.3000")

       

      Have all failed to produce any viable results, of course.

       

      Can any of you query gurus out there get me pointed in the right direction?

        • 1. Re: Query help
          phoffmann SupportEmployee

          Your problem is that you are trying to use a LIKE operator (where the "*.3000" syntax would be correct for the console tool) with "the wrong kind" of operator. So you're attempting to combine a "less than / larger than" with a "LIKE..." based operation where that won't work.

           

          *IF* you have a SQL guy (usually your DBA), have him have a look at your problem (from a raw SQL Syntax side of things) and get him to add his 5 pence on how he'd solve it (apologies for not being able to think straight due to jet lag & sleep deprivation thanks to a "kind" firealarm deciding to act like a yo-yo) .

           

          Once I've got some SQL pattern to look at, I can help you with either trying to translate it into a "Console SQL statement" (if possible - I suspect it may not be) - or help you with locating the relevant tables / fields so that you can run a SQL staement for it.

           

          Does that sound fair?

          1 of 1 people found this helpful
          • 2. Re: Query help
            brad.e.smith Apprentice

            We'd actually discussed talking to one of our DBA's yesterday. I'll see what information I can get.

            • 3. Re: Query help
              phoffmann SupportEmployee

              Sure - let me know what they come up with. Still bouncing around the country side a bit - but if your DBA's don't mind sharing a leg up, I can sort out the DB-tables and such pretty quickly for you .

              • 4. Re: Query help
                brad.e.smith Apprentice

                Here’s the query they came up with for the database:

                 

                select top 1 FWVersion, REVERSE(LEFT(REVERSE(FWVersion),CHARINDEX('.', REVERSE(FWVersion),0)-1)) as 'Last4 of FWVer'

                from [LANDesk].[dbo].[AMT_SCS_Info]

                WHERE FWVersion <> '3000'

                 

                • 5. Re: Query help
                  phoffmann SupportEmployee

                  Awesome stuff!

                   

                  That will work *as long* as you actually have string that have a "1.2.3.4" type format.

                   

                  Here's a slightly more friendly / operable version (giving hostnames) linked up:

                   

                  select COMP.DeviceName, AMT.FWVersion, REVERSE(LEFT(REVERSE(FWVersion),CHARINDEX('.', REVERSE(FWVersion),0)-1)) as 'Last4 of FWVer'
                  from AMT_SCS_Info AMT
                  LEFT OUTER JOIN COMPUTER COMP (nolock) on AMT.Computer_Idn = COMP.Computer_Idn
                  WHERE FWVersion <> '3000'
                  

                   

                  ... Need any other fields added while we're at it?

                   

                  (COMPUTER is the "mother of all tables" and COMPUTER_IDN's are how we keep track of individual devices across 90% of database tables)

                  1 of 1 people found this helpful
                  • 6. Re: Query help
                    brad.e.smith Apprentice

                    You've been a great help, phoffmann!