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

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

    Re: Maintaining Accurate Machine Inventory

    ‏2013-03-04T21:24:22Z  
    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

    Re: Maintaining Accurate Machine Inventory

    ‏2013-03-05T20:35:16Z  
    Thank you Jim!
  • PatCole
    PatCole
    31 Posts

    Re: Maintaining Accurate Machine Inventory

    ‏2013-10-04T19:05:12Z  
    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

    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
    49 Posts

    Re: Maintaining Accurate Machine Inventory

    ‏2013-10-06T21:02:45Z  
    • PatCole
    • ‏2013-10-04T19:05:12Z

    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

    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

    Re: Maintaining Accurate Machine Inventory

    ‏2013-10-07T12:28:55Z  

    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.

    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

    Re: Maintaining Accurate Machine Inventory

    ‏2013-10-11T13:55:08Z  

    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.

    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
    49 Posts

    Re: Maintaining Accurate Machine Inventory

    ‏2013-10-14T21:23:10Z  
    • PatCole
    • ‏2013-10-11T13:55:08Z

    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

    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
    49 Posts

    Re: Maintaining Accurate Machine Inventory

    ‏2013-10-14T21:30:49Z  

    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.

    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

    Re: Maintaining Accurate Machine Inventory

    ‏2013-10-15T17:31:15Z  

    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.

    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
    49 Posts

    Re: Maintaining Accurate Machine Inventory

    ‏2013-10-16T04:06:47Z  
    • PatCole
    • ‏2013-10-15T17:31:15Z

    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    

     

    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

    Re: Maintaining Accurate Machine Inventory

    ‏2013-10-24T16:30:21Z  

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