DX Unified Infrastructure Management

Expand all | Collapse all

CA UIM Database

  • 1.  CA UIM Database

    Posted Sep 30, 2016 04:55 AM

    Hi Team, 

     

    Can you please tell me what is the sql query for servers by which we get Total Physical Memory usage and CPU Usage in single table?

     

    Regards,

    Anuj Choudhary 



  • 2.  Re: CA UIM Database

    Broadcom Employee
    Posted Sep 30, 2016 06:07 AM

    Hi Anuj,

    Here's a little query you could probably use, although it relies on the qos_views script already been run.

    The qos_views script is available elsewhere on this community, version 3.1 is current I think for mssql.

    You need to put in your origin (or remove that line)

    It calculates the average per day because memory and cpu might come in with different timestamps so difficult to match to the same line.

    You could change it to hourly averages if you need more detail.

    cheers

    Rowan

     

    select a.customer

    ,a.source

    ,a.Date2

    ,CONVERT(DECIMAL(10,2),a.cpu) as cpu

    ,CONVERT(DECIMAL(10,2),b.mem) as memory

     from

    (

    SELECT cpu.origin as customer

    ,cpu.source as source

    ,CONVERT(char(8), cast(cpu.sampletime as datetime), 112) as Date2

    ,avg(cpu.samplevalue) as cpu

    FROM [CA_UIM].[dbo].[V_QOS_CPU_USAGE] CPU

    where CPU.origin = 'rc82_phub'

    group by CONVERT(char(8), cast(cpu.sampletime as datetime), 112), cpu.source,cpu.origin

    ) a

    full join

    (

    SELECT mem.origin as customer

    ,mem.source as source

    ,CONVERT(char(8), cast(mem.sampletime as datetime), 112) as Date2

     ,avg(mem.samplevalue) as mem

    FROM [CA_UIM].[dbo].[V_QOS_MEMORY_PHYSICAL_PERC] mem

    where mem.origin = 'rc82_phub'

    group by CONVERT(char(8), cast(mem.sampletime as datetime), 112), mem.source,mem.origin

    ) b on a.customer = b.customer and a.source = b.source and a.date2 = b.date2

    order by a.date2 desc



  • 3.  Re: CA UIM Database

    Posted Sep 30, 2016 07:35 AM

    Hi Rowan,

     

    Thanks for reply!

    Can you please provide me the query by using which I will only get the latest time interval CPU Usage and Physical Memory Usage?

    The sql query which is provided by you showing the output like given below:

     

    Thanks,

    Anuj



  • 4.  Re: CA UIM Database

    Posted Jan 30, 2017 05:53 AM

    HI RowanCollis

     

    while running the query im getting an error

     



  • 5.  Re: CA UIM Database

    Broadcom Employee
    Posted Jan 30, 2017 02:40 PM

    Hi La La

    You are probably missing the view in your database.

    This needs to be created manually with the QOS_VIEWS script, which can be  found here

    cheers

    Rowan



  • 6.  Re: CA UIM Database

    Posted Jan 30, 2017 03:31 AM

    Hi RowanCollis

     

    Can u pls include 2 more column i.e disk and ipaddress/hostname so the remaining % of each machines disk  with their ip addresses will also show in output.?



  • 7.  Re: CA UIM Database

    Broadcom Employee
    Posted Jan 30, 2017 03:25 PM

    Try this...

    select a.customer
    ,a.source
    ,f.ip
    ,a.date
    ,CONVERT(DECIMAL(10,2),a.cpu) as cpu
    ,CONVERT(DECIMAL(10,2),b.mem) as memory
    ,c.files as files
    ,100 - (CONVERT(DECIMAL(10,2),c.disk_perc)) as disk_perc_free
    -- ,CONVERT(DECIMAL(10,2),d.disk_mb) as disk_mb
    from
    (
    SELECT cpu.origin as customer
    ,cpu.source as source
    ,CAST(MONTH(cpu.sampletime) AS VARCHAR(2)) + '-' + CAST(YEAR(cpu.sampletime) AS VARCHAR(4)) as Date
    ,avg(cpu.samplevalue) as cpu
    FROM [CA_UIM].[dbo].[V_QOS_CPU_USAGE] CPU
    where CPU.origin = 'colro22-i147369'
    --and CPU.source = 'colro22-i147369'
    and month(cpu.sampletime) = 12
    and year(cpu.sampletime) = 2016
    group by CAST(MONTH(cpu.sampletime) AS VARCHAR(2)) + '-' + CAST(YEAR(cpu.sampletime) AS VARCHAR(4)), cpu.source,cpu.origin
    ) a
    join
    (
    SELECT mem.origin as customer
    ,mem.source as source
    ,CAST(MONTH(mem.sampletime) AS VARCHAR(2)) + '-' + CAST(YEAR(mem.sampletime) AS VARCHAR(4)) as Date
    ,avg(mem.samplevalue) as mem
    FROM [CA_UIM].[dbo].[V_QOS_MEMORY_USAGE] mem
    where mem.origin = 'colro22-i147369'
    --and mem.source = 'colro22-i147369'
    and month(mem.sampletime) = 12
    and year(mem.sampletime) = 2016
    group by CAST(MONTH(mem.sampletime) AS VARCHAR(2)) + '-' + CAST(YEAR(mem.sampletime) AS VARCHAR(4)), mem.source,mem.origin
    ) b on a.customer = b.customer and a.source = b.source and a.Date = b.Date
    join
    (
    SELECT disk.origin as customer
    ,disk.source as source
    ,disk.target as files
    ,CAST(MONTH(disk.sampletime) AS VARCHAR(2)) + '-' + CAST(YEAR(disk.sampletime) AS VARCHAR(4)) as Date
    ,avg(disk.samplevalue) as disk_perc
    FROM V_QOS_DISK_USAGE_PERC disk
    where disk.origin = 'colro22-i147369'
    --and disk.source = 'colro22-i147369'
    and month(disk.sampletime) = 12
    and year(disk.sampletime) = 2016
    group by CAST(MONTH(disk.sampletime) AS VARCHAR(2)) + '-' + CAST(YEAR(disk.sampletime) AS VARCHAR(4)), disk.source,disk.origin,disk.target
    ) c on a.customer = c.customer and a.source = c.source and a.Date = c.Date
    join
    (
    SELECT diskmb.origin as customer
    ,diskmb.source as source
    ,diskmb.target as files
    ,CAST(MONTH(diskmb.sampletime) AS VARCHAR(2)) + '-' + CAST(YEAR(diskmb.sampletime) AS VARCHAR(4)) as Date
    ,avg(diskmb.samplevalue) as disk_mb
    FROM V_QOS_DISK_USAGE diskmb
    where diskmb.origin = 'colro22-i147369'
    --and diskmb.source = 'colro22-i147369'
    and month(diskmb.sampletime) = 12
    and year(diskmb.sampletime) = 2016
    group by CAST(MONTH(diskmb.sampletime) AS VARCHAR(2)) + '-' + CAST(YEAR(diskmb.sampletime) AS VARCHAR(4)), diskmb.source,diskmb.origin,diskmb.target
    ) d on c.customer = d.customer and c.source = d.source and c.Date = d.Date and c.files = d.files
    join
    (SELECT distinct cm.ip,cm.name
    from cm_computer_system cm
    ) f on f.name = d.source

     

    produces something like this...



  • 8.  Re: CA UIM Database

    Posted Jan 31, 2017 02:17 AM

    Hi Rowan

     

    the following error im getting while running the query. how can i fix this pls. i have ms sql 2012

     



  • 9.  Re: CA UIM Database

    Posted Jan 31, 2017 03:20 AM

    Did you implement the qos view script?

    See: QOS_Views SQL Script 



  • 10.  Re: CA UIM Database

    Posted Feb 03, 2017 04:56 AM

    Hi RowanCollis  and chrlu01

     

    i have now run qos_views script .

     

    Now when i run your query its not giving error but its not giving any output also. why is that. ???both of you query mentioned above results the same output...see the output result.. Please advise

     

     

     



  • 11.  Re: CA UIM Database

    Posted Feb 03, 2017 07:42 AM

    Hi RowanCollis

     

    Kindly confirm if this script will provide the real time information of CPU usage, Disk and Memory on all the servers?

     



  • 12.  Re: CA UIM Database

    Broadcom Employee
    Posted Feb 03, 2017 09:22 AM

    These are average values for the period selected ie the whole month.

    You could change it so that it selects the oldest value in the time period.

    regards

    Rowan



  • 13.  Re: CA UIM Database

    Posted Feb 05, 2017 03:06 AM

    Thanks Rowan

     

    Where to change exactly so that i can get both time period that is oldest and current.?



  • 14.  Re: CA UIM Database

    Posted Feb 09, 2017 07:09 AM

    Hi RowanCollis

     

    Please we need to get the total capacity for each parameter for Disk, Memory and CPU of the follwoing query. Can you please edit the following query where necessary

     

    select a.customer
    ,a.source
    ,f.ip
    ,a.date
    ,CONVERT(DECIMAL(10,2),a.cpu) as cpu
    ,CONVERT(DECIMAL(10,2),b.mem) as memory
    ,c.files as files
    ,100 - (CONVERT(DECIMAL(10,2),c.disk_perc)) as disk_perc_free
    -- ,CONVERT(DECIMAL(10,2),d.disk_mb) as disk_mb
    from
    (
    SELECT cpu.origin as customer
    ,cpu.source as source
    ,CAST(DAY(cpu.sampletime) AS VARCHAR(2)) + '-' + CAST(MONTH(cpu.sampletime) AS VARCHAR(2)) + '-' + CAST(YEAR(cpu.sampletime) AS VARCHAR(4)) as Date
    ,avg(cpu.samplevalue) as cpu
    FROM [CA_UIM].[dbo].[V_QOS_CPU_USAGE] CPU
    where month(cpu.sampletime) = 01
    and year(cpu.sampletime) = 2017
    and day(cpu.sampletime) = 01
    --AND datepart(dd,cpu.sampletime) = 01
    group by CAST(DAY(cpu.sampletime) as VARCHAR(2)) + '-' + CAST(MONTH(cpu.sampletime) AS VARCHAR(2)) + '-' + CAST(YEAR(cpu.sampletime) AS VARCHAR(4)), cpu.source,cpu.origin
    ) a
    join
    (
    SELECT mem.origin as customer
    ,mem.source as source
    ,CAST(DAY(mem.sampletime) AS VARCHAR(2)) + '-' + CAST(MONTH(mem.sampletime) AS VARCHAR(2)) + '-' + CAST(YEAR(mem.sampletime) AS VARCHAR(4)) as Date
    ,avg(mem.samplevalue) as mem
    FROM [CA_UIM].[dbo].[V_QOS_MEMORY_USAGE] mem

    where month(mem.sampletime) = 01
    and year(mem.sampletime) = 2017
    and day(mem.sampletime) = 01
    --and datepart(dd,mem.sampletime) = 01
    group by CAST(DAY(mem.sampletime) AS VARCHAR(2)) + '-' + CAST(MONTH(mem.sampletime) AS VARCHAR(2)) + '-' + CAST(YEAR(mem.sampletime) AS VARCHAR(4)), mem.source,mem.origin
    ) b on a.customer = b.customer and a.source = b.source and a.Date = b.Date
    join
    (
    SELECT disk.origin as customer
    ,disk.source as source
    ,disk.target as files
    ,CAST(DAY(disk.sampletime) AS VARCHAR(2)) + '-' + CAST(MONTH(disk.sampletime) AS VARCHAR(2)) + '-' + CAST(YEAR(disk.sampletime) AS VARCHAR(4)) as Date
    ,avg(disk.samplevalue) as disk_perc
    FROM V_QOS_DISK_USAGE_PERC disk


    where month(disk.sampletime) = 01
    and year(disk.sampletime) = 2017
    and day(disk.sampletime) = 01
    --datepart(dd,disk.sampletime) = 01
    group by CAST(DAY(disk.sampletime) AS VARCHAR(2)) + '-' + CAST(MONTH(disk.sampletime) AS VARCHAR(2)) + '-' + CAST(YEAR(disk.sampletime) AS VARCHAR(4)), disk.source,disk.origin,disk.target
    ) c on a.customer = c.customer and a.source = c.source and a.Date = c.Date
    join
    (
    SELECT diskmb.origin as customer
    ,diskmb.source as source
    ,diskmb.target as files
    ,CAST(DAY(diskmb.sampletime) AS VARCHAR(2)) + '-' + CAST(MONTH(diskmb.sampletime) AS VARCHAR(2)) + '-' + CAST(YEAR(diskmb.sampletime) AS VARCHAR(4)) as Date
    ,avg(diskmb.samplevalue) as disk_mb
    FROM V_QOS_DISK_USAGE diskmb
    where month(diskmb.sampletime) = 01
    and year(diskmb.sampletime) = 2017
    and day(diskmb.sampletime) = 01
    --datepart(dd,diskmb.sampletime) = 01
    group by CAST(DAY(diskmb.sampletime) AS VARCHAR(2)) + '-' + CAST(MONTH(diskmb.sampletime) AS VARCHAR(2)) + '-' + CAST(YEAR(diskmb.sampletime) AS VARCHAR(4)), diskmb.source,diskmb.origin,diskmb.target
    ) d on c.customer = d.customer and c.source = d.source and c.Date = d.Date and c.files = d.files
    join
    (SELECT distinct cm.ip,cm.name
    from cm_computer_system cm
    ) f on f.name = d.source



  • 15.  Re: CA UIM Database

    Posted Feb 09, 2017 08:05 AM

    hi Rowan RowanCollis

     

    to be more specfic what im trying to saying...is y some of the cpu showing NULL and files just /  as shown below highlighted?? 

     

    please advise

     

     

     



  • 16.  Re: CA UIM Database

    Posted Feb 03, 2017 05:00 AM

    Try to run the script with the "where" clause removed



  • 17.  Re: CA UIM Database

    Posted Feb 03, 2017 05:36 AM

    Hi Luc

     

    Many thanks i have now got the output. Now one i discovered both the queries mention above by Rowan .In the output one query giving 364 records data and one is giving 150599 records. See the output below 

     

    my requirement is to pull all those machines which is currently being monitored on ump with the same ouput as in first screenshot below. (i mean with ip date cpu memory files disk space)