Topic
11 replies Latest Post - ‏2013-10-24T16:30:21Z by PatCole
PatCole
PatCole
31 Posts
ACCEPTED ANSWER

Pinned topic Maintaining Accurate Machine Inventory

‏2013-02-28T20:29:15Z |
What is the correct process for maintaining an accurate machine inventory after systems are moved among machines and machines are upgraded, replaced, etc? Our inventory has become inaccurate with systems showing up multiple times due these types of changes.
Updated on 2013-03-05T20:35:16Z at 2013-03-05T20:35:16Z by PatCole
  • SystemAdmin
    SystemAdmin
    47 Posts
    ACCEPTED ANSWER

    Re: Maintaining Accurate Machine Inventory

    ‏2013-03-04T21:24:22Z  in response to PatCole
    Hi Pat,

    I can send you some SQL that will get rid of the old machines. In TADz 8.1 we have a new process in the Analyzer which will do a physical delete of old hardware. The upgrade to 8.1 is not that complicated and only requires 3 or 4 batch jobs to upgrade the current Repository to the 8.1 version. Of course rolling out the Usage monitor etc will take longer but 8.1 will read 7.5 IQ and Usage data. We just won't collect all the extra information that the 8.1 IQ and Usage monitor provide.

    Below is the SQL to use. Make sure you use the full serial number for each machine. eg use all the leading zero's.

    DELETE FROM NODE WHERE HW_SERIAL = '00000001176B';

    DELETE FROM SYSTEM_NODE WHERE NODE_KEY NOT IN (SELECT NODE_KEY FROM NODE);
    DELETE FROM NODE_CAPACITY WHERE NODE_KEY NOT IN (SELECT NODE_KEY FROM NODE);
    DELETE FROM PRODUCT_NODE_CAPACITY WHERE NODE_KEY NOT IN (SELECT NODE_KEY FROM NODE);
    COMMIT;

    Jim Kyriakakis
    • PatCole
      PatCole
      31 Posts
      ACCEPTED ANSWER

      Re: Maintaining Accurate Machine Inventory

      ‏2013-10-04T19:05:12Z  in response to SystemAdmin

      Hi Jim,  I am revisiting this question.  We maintain aggregated usage data for 12 months.  How can we maintain an accurate hardware inventory when systems are moved among machines but the machines are not obsolete?  A given system might show up in two different serial numbers in the machine inventory report.  We don't want to remove the machine.    Interested in 7.5 and 8.1 both. 

      Thanks,

      Pat

      • Jim_Kyriakakis
        Jim_Kyriakakis
        46 Posts
        ACCEPTED ANSWER

        Re: Maintaining Accurate Machine Inventory

        ‏2013-10-06T21:02:45Z  in response to PatCole

        Hi Pat,

         

        Initially you asked why are systems showing up on different machines. I assume that you wanted to delete the old machine but now you are saying that you want to keep all the old data. Well you can do that by not doing anything at all. Just leave all the data as is. In 8.1 it's a lot easier to make sure where system is currently running on as the last update field is now correctly updated but we don't show this field in any of the reports. You would have create a custom report to show the current machine that an LPAR is running on. You can of course open a RFE asking for this information to be a report in Analyzer. But first I need to make sure that I understand what you guys are doing. Are you saying that an LPAR is copied from one machine to another on a regular basis and back again?

         

        Jim K.

        • PatCole
          PatCole
          31 Posts
          ACCEPTED ANSWER

          Re: Maintaining Accurate Machine Inventory

          ‏2013-10-07T12:28:55Z  in response to Jim_Kyriakakis

          Jim, we have been moving LPARs around actually.  This happens from time to time to accomodate new hardware and software license changes, things like that.  So in addition to obsolete hardware, we have LPARs showing up on multiple CECs because they lived there at one time but no longer do.   I would like the machine inventory report to reflect the current state.  Can I delete an LPAR from a CEC even though usage data exists for the period of time it ran there?  Is there any linkage between the CEC/LPAR and the usage data? 

          Pat

        • PatCole
          PatCole
          31 Posts
          ACCEPTED ANSWER

          Re: Maintaining Accurate Machine Inventory

          ‏2013-10-11T13:55:08Z  in response to Jim_Kyriakakis

          Jim, I know this question is confusing, but basically what we need is a machine inventory display that shows the current configuration.  We may have moved a system a month ago, and we still have usage data from when the system was on that CEC, but now it's on another CEC.   Perhaps some sql we can run that would show us only the most current machines/lpars? 

          Pat

          • Jim_Kyriakakis
            Jim_Kyriakakis
            46 Posts
            ACCEPTED ANSWER

            Re: Maintaining Accurate Machine Inventory

            ‏2013-10-14T21:23:10Z  in response to PatCole

            Hi Pat,

            I've modified the SQL to show the last update time for the Machine. Does this help?

            SELECT N.HW_TYPE, N.HW_MODEL
             , MAX (CASE WHEN NC.MODEL_CAPACITY = '   ' THEN NULL ELSE NC.MODEL_CAPACITY END) AS MODCAP
            , N.HW_PLANT, RIGHT(RTRIM(N.HW_SERIAL), 5) AS HW_SERIAL, N.HW_NAME
            , N.NODE_TYPE , N.LPAR_NUMBER, N.LPAR_NAME, S.IPADDR, S.HOSTNAME
               , MAX(CASE WHEN N.NODE_TYPE = 'HW' THEN N.LAST_UPDATE_TIME ELSE NULL END) AS LAST_UPDATE
               , MAX(CASE WHEN NC.METRIC_TYPE = 'MSU'       THEN NC.QUANTITY ELSE NULL END) AS MAX_MSU
               , MAX(CASE WHEN NC.METRIC_TYPE = 'MSULPAR'   THEN NC.QUANTITY ELSE NULL END) AS LPAR_MSU
               , MAX(CASE WHEN NC.METRIC_TYPE = 'SUBCAPMSU' THEN NC.QUANTITY ELSE NULL END) AS MAX_SUBCAPMSU

               , MAX(CASE WHEN NC.METRIC_TYPE = 'TOTONLPROC' THEN NC.QUANTITY ELSE NULL END) AS TOTONLPROC
               , MAX(CASE WHEN NC.METRIC_TYPE = 'CPUONL' THEN NC.QUANTITY ELSE NULL END) AS CPUONL
               , MAX(CASE WHEN NC.METRIC_TYPE = 'ZAAPONL' THEN NC.QUANTITY ELSE NULL END) AS ZAAPONL
               , MAX(CASE WHEN NC.METRIC_TYPE = 'ZIIPONL' THEN NC.QUANTITY ELSE NULL END) AS ZIIPONL
               , MAX(CASE WHEN NC.METRIC_TYPE = 'CRYPTOONL' THEN NC.QUANTITY ELSE NULL END) AS CRYPTOONL
               , MAX(CASE WHEN NC.METRIC_TYPE = 'CRYPTOINS' THEN NC.QUANTITY ELSE NULL END) AS CRYPTOINS
               , MAX(CASE WHEN NC.METRIC_TYPE = 'TOTSTORAGE' THEN NC.QUANTITY ELSE NULL END) AS TOTSTORAGE
               , CASE WHEN COUNT(DISTINCT S.SID) <= 1 THEN MIN(S.SID)
                      ELSE RTRIM(MIN(S.SID))
                           ||' ... '||RTRIM(CAST(COUNT(DISTINCT S.SID)-1 AS CHAR(8)))
                           ||' more'
                      END AS SYSTEMS
                  
            FROM NODE                     AS N
              LEFT OUTER JOIN NODE_CAPACITY AS NC ON NC.NODE_KEY   = N.NODE_KEY
              LEFT OUTER JOIN SYSTEM_NODE   AS SN ON SN.NODE_KEY   = N.NODE_KEY
              LEFT OUTER JOIN SYSTEM        AS S  ON S.SYSTEM_KEY  = SN.SYSTEM_KEY
            GROUP BY N.HW_TYPE, N.HW_MODEL, N.HW_PLANT, RIGHT(RTRIM(N.HW_SERIAL), 5), N.HW_NAME
            , N.NODE_TYPE , N.LPAR_NUMBER, N.LPAR_NAME, S.IPADDR, S.HOSTNAME, N.LAST_UPDATE_TIME
            ORDER BY N.HW_TYPE, N.HW_MODEL, N.HW_PLANT, RIGHT(RTRIM(N.HW_SERIAL), 5), N.HW_NAME
            , N.NODE_TYPE , N.LPAR_NUMBER, N.LPAR_NAME

             

            Jim K.

            • Jim_Kyriakakis
              Jim_Kyriakakis
              46 Posts
              ACCEPTED ANSWER

              Re: Maintaining Accurate Machine Inventory

              ‏2013-10-14T21:30:49Z  in response to Jim_Kyriakakis

              Hi Pat,

              Just realised that the SQL I sent you was from 8.1 Analyzer. The one below is for 7.5. Sorry.

              SELECT N.HW_TYPE, N.HW_MODEL
              , MAX (CASE WHEN NC.MODEL_CAPACITY = '   ' THEN NULL ELSE NC.MODEL_CAPACITY END) AS MODCAP
              , N.HW_PLANT, RIGHT(RTRIM(N.HW_SERIAL), 5) AS HW_SERIAL, N.HW_NAME
              , N.NODE_TYPE , N.LPAR_NUMBER, N.LPAR_NAME
                 , MAX(CASE WHEN N.NODE_TYPE = 'HW' THEN N.LAST_UPDATE_TIME ELSE NULL END) AS LAST_UPDATE
                 , MAX(CASE WHEN NC.METRIC_TYPE = 'MSU'       THEN NC.QUANTITY ELSE NULL END) AS MAX_MSU
                 , MAX(CASE WHEN NC.METRIC_TYPE = 'SUBCAPMSU' THEN NC.QUANTITY ELSE NULL END) AS MAX_SUBCAPMSU
                 , CASE WHEN COUNT(DISTINCT S.SID) <= 1 THEN MIN(S.SID)
                        ELSE RTRIM(MIN(S.SID))
                             ||' ... '||RTRIM(CAST(COUNT(DISTINCT S.SID)-1 AS CHAR(8)))
                             ||' more'
                        END AS SYSTEMS
                FROM NODE                     AS N
                LEFT OUTER JOIN NODE_CAPACITY AS NC ON NC.NODE_KEY   = N.NODE_KEY
                LEFT OUTER JOIN SYSTEM_NODE   AS SN ON SN.NODE_KEY   = N.NODE_KEY
                LEFT OUTER JOIN SYSTEM        AS S  ON S.SYSTEM_KEY  = SN.SYSTEM_KEY
              GROUP BY N.HW_TYPE, N.HW_MODEL, N.HW_PLANT, HW_SERIAL, N.HW_NAME
              , N.NODE_TYPE , N.LPAR_NUMBER, N.LPAR_NAME, N.LAST_UPDATE_TIME
              ORDER BY N.HW_TYPE, N.HW_MODEL, N.HW_PLANT, HW_SERIAL, N.HW_NAME
              , N.NODE_TYPE , N.LPAR_NUMBER, N.LPAR_NAME

               

              Jim K.

               

            • PatCole
              PatCole
              31 Posts
              ACCEPTED ANSWER

              Re: Maintaining Accurate Machine Inventory

              ‏2013-10-15T17:31:15Z  in response to Jim_Kyriakakis

              Thanks for the SQL Jim.  I'm afraid it doesn't help though.  In the below example, system DEVJ was moved to D99C6 at some point but the report doesn't really indicate what is current.  The last_update times are both 10/12 which is when we ran the IQ Scan on those machines/systems.  (there are other systems on these CECS).  I updated the SQL to show me the last update time for the LPARs but it doesn't make any sense that I can see.  

              What do the timestamps mean in this report?  The HW timestamp and the LPAR timestamp please. 

              Thanks, Pat

               

              HW_SERIAL HW_NAME NODE_TYPE LPAR_NUMBER LPAR_NAME LAST_UPDATE_TIME SYSTEMS  
              D99C6 KC30 HW 0   2013-10-12 08:59:53    
              D99C6 KC30 LPAR 28 KC3DEVJ 2012-07-31 23:34:44 DEVJ <<< CURRENT
              510E7 KC31 LPAR 28 KC3DEVJ 2013-06-14 23:55:00 DEVJ <<< OLD
              510E7 KC41 HW 0   2013-10-12 05:00:01    

               

              • Jim_Kyriakakis
                Jim_Kyriakakis
                46 Posts
                ACCEPTED ANSWER

                Re: Maintaining Accurate Machine Inventory

                ‏2013-10-16T04:06:47Z  in response to PatCole

                Hi Pat,

                Take 2. Try the SQL below and this time you need to select a PERIOD to view.

                SELECT N.HW_TYPE, N.HW_MODEL
                , MAX (CASE WHEN NC.MODEL_CAPACITY = '   ' THEN NULL ELSE NC.MODEL_CAPACITY END) AS MODCAP
                , N.HW_PLANT, RIGHT(RTRIM(N.HW_SERIAL), 5) AS HW_SERIAL, N.HW_NAME
                , N.NODE_TYPE , N.LPAR_NUMBER, N.LPAR_NAME, NC.PERIOD
                   , MAX(CASE WHEN N.NODE_TYPE = 'HW' THEN N.LAST_UPDATE_TIME ELSE NULL END) AS LAST_UPDATE
                   , MAX(CASE WHEN NC.METRIC_TYPE = 'MSU'       THEN NC.QUANTITY ELSE NULL END) AS MAX_MSU
                   , MAX(CASE WHEN NC.METRIC_TYPE = 'SUBCAPMSU' THEN NC.QUANTITY ELSE NULL END) AS MAX_SUBCAPMSU
                   , CASE WHEN COUNT(DISTINCT S.SID) <= 1 THEN MIN(S.SID)
                          ELSE RTRIM(MIN(S.SID))
                               ||' ... '||RTRIM(CAST(COUNT(DISTINCT S.SID)-1 AS CHAR(8)))
                               ||' more'
                          END AS SYSTEMS
                  FROM NODE                     AS N
                  LEFT OUTER JOIN NODE_CAPACITY AS NC ON NC.NODE_KEY   = N.NODE_KEY
                  LEFT OUTER JOIN SYSTEM_NODE   AS SN ON SN.NODE_KEY   = N.NODE_KEY
                  LEFT OUTER JOIN SYSTEM        AS S  ON S.SYSTEM_KEY  = SN.SYSTEM_KEY
                  WHERE NC.PERIOD = '2013-05-01'
                GROUP BY N.HW_TYPE, N.HW_MODEL, N.HW_PLANT, HW_SERIAL, N.HW_NAME
                , N.NODE_TYPE , N.LPAR_NUMBER, N.LPAR_NAME, N.LAST_UPDATE_TIME, NC.PERIOD
                ORDER BY N.HW_TYPE, N.HW_MODEL, N.HW_PLANT, HW_SERIAL, N.HW_NAME
                , N.NODE_TYPE , N.LPAR_NUMBER, N.LPAR_NAME

                 

                So the idea here is to only show the machines that are current for that month.

                 

                Jim K.

  • PatCole
    PatCole
    31 Posts
    ACCEPTED ANSWER

    Re: Maintaining Accurate Machine Inventory

    ‏2013-03-05T20:35:16Z  in response to PatCole
    Thank you Jim!
  • PatCole
    PatCole
    31 Posts
    ACCEPTED ANSWER

    Re: Maintaining Accurate Machine Inventory

    ‏2013-10-24T16:30:21Z  in response to PatCole

    The latest SQL you sent with NC.PERIOD is exactly what we were looking for.  Thank you!