How can I get data from system to show availability based on the net_connect probe that will show my origin and have a date range?

Document ID:  TEC1061693
Last Modified Date:  08/09/2017
{{active ? 'Hide' : 'Show'}} Technical Document Details

Products

  • CA Unified Infrastructure Management

Releases

  • CA Unified Infrastructure Management:Release:8.31
  • CA Unified Infrastructure Management:Release:8.4
  • CA Unified Infrastructure Management:Release:8.41
  • CA Unified Infrastructure Management:Release:8.42
  • CA Unified Infrastructure Management:Release:8.47
  • CA Unified Infrastructure Management:Release:8.5
  • CA Unified Infrastructure Management:Release:8.51

Components

  • UNIFIED INFRASTRUCTURE MGMT:CAUIM
  • UIM - UMP:UIMUMP
Introduction:

Many clients use net_connect to monitor availability of systems.
Currently as of UIM 8.51 no out of the box reports to provide this information.
This article provides a MS SQL server script that can be run with a few changes to
to produce an availability report including origin.

 

 

Environment:
UIM 8.X MS SQL Server 2008+
Instructions:

You will need to change the following lines in the script:

Change the start time and end time to meet your needs:

SET @STARTIME = '20170808 00:00:00'
SET @ENDTIME = '20170808 15:00:00'

Change the where clause to meet your needs for the target:

where s.target like ''%ca.com%'' and rx.reportdate >='''+ @STARTIME + '''and rx.reportdate <='''+ @ENDTIME + '''

 

Example Output:

Target                                           Available    NotAvailable    Percentage    origin
howeu01-e5658.ca.com:ping        20            0                     100.00        PrimaryUIM_hub
howeu01-F3747.ca.com:ping        20            0                     100.00        PrimaryUIM_hub
howeu01-f3748.ca.com:ping         20            0                     100.00        PrimaryUIM_hub
howeu01-s4800.ca.com:ping        20            0                     100.00        PrimaryUIM_hub
howeu01-s4801.ca.com:ping        20            0                     100.00        PrimaryUIM_hub
howeu01-u156920.ca.com:ping    21            0                     100.00        PrimaryUIM_hub
howeu01-u179045.ca.com:ping    20            0                     100.00        PrimaryUIM_hub
lodibm12l.ca.com:ping                   20            0                     100.00        PrimaryUIM_hub
taoyu04-w7vm1.ca.com:ping        20            0                    100.00        PrimaryUIM_hub

Additional Information:

DECLARE @SQL NVARCHAR(4000)
DECLARE @RTABLE VARCHAR(50)
DECLARE @STARTIME VARCHAR(25)
DECLARE @ENDTIME VARCHAR(25)

SET @RTABLE = (select distinct r_Table from S_QOS_DATA where qos='qos_net_connect')

SET @STARTIME = '20170808 00:00:00'
SET @ENDTIME = '20170808 15:00:00'

SET @SQL = '
    select
        [Target],
        [Available],
        [NotAvailable],
        CAST(CAST([Available] * 100.0 AS Numeric(10,2)) / sum([Available]+[NotAvailable]) as Numeric(10,2)) as [Percentage],
        origin
    from

    (   
        select s.target as [Target],
            sum(case when [value] IS NOT NULL then 1 else 0 end) as [Available],
            sum(case when [value] IS NULL then 1 else 0 end) as [NotAvailable],
            s.origin
        from S_QOS_DATA s
        join (select
            DATEPART(yyyy, r.sampletime) as [sampleyear],
            DATEPART(mm, r.sampletime) as [samplemonth],
            DATEPART(dd, r.sampletime) as [sampleday],
            r.samplevalue as [value],
            r.sampletime as [time],
            r.sampletime as [reportdate],
            r.table_id
        from ' + @RTABLE + ' r
        join (select distinct table_id from ' + @RTABLE + ') rr
        on r.table_id = rr.table_id
    ) rx
    on s.table_id = rx.table_id
    where s.target like ''%ca.com%'' and rx.reportdate >='''+ @STARTIME + '''and rx.reportdate <='''+ @ENDTIME + '''
    group by s.target, s.origin
    ) b
    group by b.target,b.Available,b.NotAvailable,b.origin
    '

EXECUTE sp_executesql @SQL

Please help us improve!

Will this information enable you to resolve your issue?

Please tell us what we can do better.

{{feedbackText.length ? feedbackText.length : '0'}}/255

{{status}}

Not what you were looking for?

Search Again >

Product Information

Support by Product >

Communities

Join a Community >