DX Unified Infrastructure Management

 View Only
Expand all | Collapse all

CA UIM Fetch 1 month report

  • 1.  CA UIM Fetch 1 month report

    Posted Aug 13, 2019 12:54 PM
    Edited by Dharmender Singh Aug 13, 2019 01:46 PM
    Hi Team,

    We are using UIM 8.5.1

    CDM version 5.80

    No UMP

    Only IM or AC we are using for server administration, there is a requirement to fetch the data for earlier 1 month of CPU / Memory / Disk utilization of PROD servers.

    So is there is any way to achieve the requirement. 

    Thank you in advance.

    Regards
    Dharmender


  • 2.  RE: CA UIM Fetch 1 month report

    Posted Aug 13, 2019 01:53 PM
    There use to be a SLM Manager tool that installs with Infrastructure manager not sure if they took it out or if it still exist but you can get data that way if the data exist.


  • 3.  RE: CA UIM Fetch 1 month report

    Posted Aug 13, 2019 02:51 PM
    Can you please let me know what is the location of SLM Manager tool?

    I mean how we will confirm either SLM Manager tool is running or not, because we have Infrastructure manager which is Installed and running.




  • 4.  RE: CA UIM Fetch 1 month report

    Posted Aug 13, 2019 03:38 PM
    I'm not aware of or see a slm manager with IM. 
    Seems you can use sql query or just install ump, the later being much quicker.

    ------------------------------
    Support Engineer
    Broadcom
    ------------------------------



  • 5.  RE: CA UIM Fetch 1 month report

    Posted Aug 13, 2019 04:12 PM
    Where and how will use sql query to get the data for earlier 1 month of CPU / Memory / Disk utilization of PROD servers.


  • 6.  RE: CA UIM Fetch 1 month report

    Posted Aug 13, 2019 04:29 PM
    if cdm is used to get the stats, then start at:
    https://docops.ca.com/ca-unified-infrastructure-management-probes/ga/en/alphabetical-probe-articles/cdm-cpu-disk-memory-performance-monitoring/cdm-metrics
    get the qos names for the metrics you are using
    query S_QOS_DATA to get the list for that qos
    pick the ones you want the stats for and get the RN_QOS_DATA_xxxx table and the table_id
    then query that RN_QOS_DATA_xxxx table for that table_id and add into the query to only show data less that 32 days ago. 
    each qos will have its own RN table
    table_id is unique to all the specifics such as qos, source, target.

    ------------------------------
    Support Engineer
    Broadcom
    ------------------------------



  • 7.  RE: CA UIM Fetch 1 month report

    Posted Aug 13, 2019 04:31 PM
    Why is there no UMP?

    ------------------------------
    Daniel Blanco
    Enterprise Tools Architect
    Alphaserve Technologies
    ------------------------------



  • 8.  RE: CA UIM Fetch 1 month report
    Best Answer

    Posted Aug 13, 2019 04:52 PM

    You can do this:
    First find all the QoS you want to pull from the S_QOS_Data table.

    --Find the QoS your looking for
    Query 1:

    select origin,robot,probe,qos,target,r_table,h_table,v_table,table_id
    FROM S_QOS_DATA
    where robot like ('%loc5-lap136%') and probe = 'cdm'
    ORDER BY origin,robot,probe,qos,target

    Result:


    You then need to identify what QoS you want. So for example CPU Usage from above you'd want to pull the values from the RN_QOS_DATA_0003 table. You then need to play around and find the right Target that the probe is populating. I had to try all 3 before I found the right TABLE_ID to use in the next query.
    You need to take note of the r_table (raw data) h_table (hourly data) and also the table_id. Depending on when you do QoS rollup the data might be in one of these 3 tables RN_QOS_DATA_#### or HN_QOS_DATA_####  You'd need to check/verify your data_engine settings.

    Query#2:
    select top 1000 * from RN_QOS_DATA_0003
    where table_id = '4709239' and sampletime >= '2019-07-01 00:00:00' and sampletime <= '2019-08-01 00:00:00'
    order by sampletime





    ------------------------------
    Daniel Blanco
    Enterprise Tools Architect
    Alphaserve Technologies
    ------------------------------



  • 9.  RE: CA UIM Fetch 1 month report

    Posted Aug 13, 2019 06:12 PM

    Hello Daniel,


    Thank you very much for your suggestions and really appreciate for your efforts.

    We checked we have 207 Servers which are in production Environment.

    CDM 5.80 is running on all of the 207 servers, We need to fetch sql data for last one month, data which contain Minimum, Average, Maximum Utilization values of CPU, Disk and Memory.

    As we checked in data_engine probe we need data for below qos.



    How will get these values, which contain Minimum, Average, Maximum Utilization values of CPU, Disk and Memory.

    Kindly suggest.

    Regards
    Dharmender




  • 10.  RE: CA UIM Fetch 1 month report

    Posted Aug 13, 2019 06:23 PM
    Edited by David Michel Aug 13, 2019 06:33 PM
    207 servers times the number of disk and cpu each has and your count is already way above 207. 
    as for the min. max, av. just do the math with all the data points you get for each. 
    if using the default monitoring cycle of 5min. that's going to be a lot.
    correction on this, the min & max you can probably get via query and for the average export it into a spreadsheet 

    ------------------------------
    Support Engineer
    Broadcom
    ------------------------------



  • 11.  RE: CA UIM Fetch 1 month report

    Posted Aug 14, 2019 06:02 AM
    If you run the qos_view sql script (see: https://community.broadcom.com/enterprisesoftware/viewdocument/qos-views-ms-sql-script-feb-2016) you can create more relax sql queries like: 
    -----
    select top 10 source, target, avg(samplevalue) as avg, max(samplevalue) as max from
    v_qos_cpu_usage
    where sampletime between dateadd(day, -30, getdate()) and getdate()
    and target not in ('System', 'Idle', 'User', 'Wait') and probe in ('cdm', 'rsp')
    group by source, target
    order by avg desc

    select source, target, avg(samplevalue) as avg , MIN(samplevalue) as min ,MAX(samplevalue) as max
    from V_QOS_MEMORY_PHYSICAL
    where sampletime >= DATEADD(day, -30, GETDATE())
    group by source, target
    order by source,target


  • 12.  RE: CA UIM Fetch 1 month report

    Posted Aug 14, 2019 02:42 PM
    Hello Luc,

    Thank you for suggesting, qos_view sql script just wanted to know where we need to install/run this attached 

    QOS_Views MS SQL script?

    I mean we install/run  qos_view sql script on the server which is hosting CA UIM database or the server which is hosting primary hub, or in other secondary hub!





  • 13.  RE: CA UIM Fetch 1 month report

    Posted Aug 15, 2019 01:19 AM
    This is a SQL procedure that you need to execute on the database server. It will only create qos views, no modifications are done.
    (you can re execute this procedure when you want/need)


  • 14.  RE: CA UIM Fetch 1 month report

    Posted Aug 15, 2019 11:14 AM
    Hello,
    Why are you not using the UMP? For that many machines that you need to generate a report on just setup a UMP instance and setup a CABI box.
    Otherwise it's just SQL nightmares. Even if you write all the SQL queries you still have to graph it.

    But anyway in your data_engine screenshot above, I noticed you have your RAW # higher than your historic #. Why?
    That's backwards. ​​
    The raw data values should be kept up until # of days then, after that # of days it should be converted to hourly, then after that # rolled up into daily.
    In the Admin console data_engine configuration you can set that # accordingly:

    0 - 14 days it's all raw data
    15 - 75 days is hourly data
    76 - 490 days its daily QoS data



    ------------------------------
    Daniel Blanco
    Enterprise Tools Architect
    Alphaserve Technologies
    ------------------------------



  • 15.  RE: CA UIM Fetch 1 month report

    Posted Aug 19, 2019 11:14 AM
    Hello Daniel,

    In above query results.

    samplevalue are not showing any Utilization units in above query#2 results, CPU utilization must be showing units either in MB or in GB or in percentage.

    However it's quite difficult to understand the samplevalue without any unit.

    Kindly confirm without UMP and CABI, through CA UIM sqlserver database is it possible to export data utilization for earlier 1 month of CPU / Memory / Disk with units?

    or these  database query are used for only QOS view?

    Regards 
    Dharmender


  • 16.  RE: CA UIM Fetch 1 month report

    Posted Aug 19, 2019 11:49 AM
    Hi Dharmender,
    Sorry I do not know where the 'units' for each QoS measurement can be found. Why can you not use the UMP?

    ------------------------------
    Daniel Blanco
    Enterprise Tools Architect
    Alphaserve Technologies
    ------------------------------



  • 17.  RE: CA UIM Fetch 1 month report

    Posted Aug 19, 2019 02:05 PM
    Check the probe metrics page, it shows the unit for each qos.

    ------------------------------
    Support Engineer
    Broadcom
    ------------------------------



  • 18.  RE: CA UIM Fetch 1 month report

    Posted Aug 19, 2019 03:18 PM
    Are you talking about UMP/CABI or for CA UIM sqlserver database?


  • 19.  RE: CA UIM Fetch 1 month report

    Posted Aug 19, 2019 04:21 PM
    no
    your comment:
    CPU utilization must be showing units either in MB or in GB or in percentage.
    the units for a qos are shown at the probe metrics page. 
    you are getting a list of metric samplevalue for some qos
    go to the probe metric page, find the qos, and check the 'unit's column

    ------------------------------
    Support Engineer
    Broadcom
    ------------------------------



  • 20.  RE: CA UIM Fetch 1 month report

    Posted Aug 19, 2019 05:32 PM
    Ok "the units for a qos are shown at the probe metrics page" 

    What is the location of probe metrics page?

    While running the query in CA UIM database, for a qos, how will see the unit's? Can you please share the sample results with us?


  • 21.  RE: CA UIM Fetch 1 month report

    Posted Aug 19, 2019 05:37 PM
    The home page for the probes is:
    https://docops.ca.com/ca-unified-infrastructure-management-probes/ga/en

    fyi: uim 8.51 downloads are no longer available so unless you already have a copy of the ump installer it will be necessary to upgrade to 9.0.2 to have ump.

    ------------------------------
    Support Engineer
    Broadcom
    ------------------------------



  • 22.  RE: CA UIM Fetch 1 month report

    Posted Aug 20, 2019 06:25 AM
    If you look a little bit in the CA_UIM tables you will find: Q_QOS_DEFINITION.
    Example:
    select name, unit, unit_short, qos_group, description from S_QOS_DEFINITION


  • 23.  RE: CA UIM Fetch 1 month report

    Posted Aug 26, 2019 09:57 AM
    Thank you Luc,

    For the suggestions, we have suggested client to Install UMP for the same.


    Regards

    Dharmender