4 Replies Latest reply on Nov 24, 2015 11:04 AM by zackm

    Microsoft SQL Help!!!

    ben.schlick03

      I started reading "teach yourself sequel in 10 minutes" yesterday and I am trying to write a query to pull all patch updates after 07/01/2015.(July, 01, 2015) I am having a difficult time trying to figure out if its a format issue in the database or the more probable reason is I am just a noob and I am doing it backwards... This is what I have so far.

       

      select nodeid, name, installdate from [dbo].[assetinventory_osupdates] where datetime('mm-dd-yyyy' , installdate) >= 07-01-2015

       

      I would greatly appreciate any advice and/help on the subject!!!

        • Re: Microsoft SQL Help!!!
          gcp

          Ok, assuming that installdate is defined as a date or datetime datatype,  then this should work (depending on your DATEFORMAT):

           

          select nodeid, name, installdate from [dbo].[assetinventory_osupdates] where installdate >= '07-01-2015'


          Note that when you specify a date string you must enclose it in quotes e.g. '07-01-2015'

          In your select, you did not have it in quotes, so it would be treated as a mathematical expression 07-01-2015 which would equal -2009.


          Check out the different date & time formats you can use - https://technet.microsoft.com/en-us/library/ms180878(v=sql.105).aspx


          Also the CONVERT function allows many date styles and may be useful for you in some queries.

          https://msdn.microsoft.com/en-AU/library/ms187928

          1 of 1 people found this helpful
            • Re: Microsoft SQL Help!!!
              ben.schlick03

              Thanks!

              That did the trick to get the overall report started in solarwinds but I know there is a way in the database to get the whole report needed. I know you are looking at this and thinking its hard to come to a conclusion without all of the facts and information in front of you but any help you can give would be much appreciated.

               

              So here is what I am trying to do. I am trying to see all of the OSupdates from 'assetinventory_osupdates' that HAVE NOT been updated on servers after '07-01-2015', excluding Health Servers.

              I am running into a few problems.

              1.) I don't know the table where the 'Health Servers' are located therefor I am unable to create the necessary 'join' for them.

              2.)I don't know how to just look at datatypes in the selected fields.(there is no drop down tab or right click to see datatype.)

                • Re: Microsoft SQL Help!!!
                  gcp

                  I don't have NPM, so can't help with the details, however my Google-Fu found this which might give you a lead on the other tables:Report KB patches from Asset Inventory in NPM

                   

                  You are probably better to ask specific NPM questions in the forum: Network Performance Monitor

                  The Report Lab Report Lab may be useful too.

                  Plenty of resources on Thwack - have a look at the Contents page under the NPM forum.

                   

                  2.)I don't know how to just look at datatypes in the selected fields.(there is no drop down tab or right click to see datatype.)

                   

                  If it is a SQL Server database, connect to it with the SQL Server Management Studio (SSMS) and you can look at the database structures etc.  If you don't have access, ask your friendly neighborhood DBA if you can get read access, or if they can look for you.

                   

                  That still may not help a lot, as the tables are SW proprietary info and they don't publish the data dictionary or ER diagrams - but you can always raise a support ticket with them if you need help.

                  1 of 1 people found this helpful
                  • Re: Microsoft SQL Help!!!
                    zackm

                    How are your "Health Servers" identified? Is this a custom property you use for nodes, and if so, what is the name of that custom property?

                     

                    Also, +1 on gcp recommending use of SSMS. The database manager app that comes with SolarWinds is useful to a point, but missing a LOT of features from what you would want to have available for reporting directly our of the database. Specifically you cannot see views, functions, or stored procedures with that tool at all.