Interface Utilization SQL Query

[Herin Domadia]

Hi All,

   I am pretty new to SQL so not sure if the below can be achieved or not. I am trying to extract data from DB using SQL query for 'QOS_INTERFACE_UTILIZATION' for last 7 days. The statement covers MIN,MAX,AVG of sample value. I am able to get the data which I require but the problem I am facing is that it shows all the interfaces for network devices. I don't want to get data for interfaces which are not being used. The interfaces are under Target column and filtering it out as per interfaces is not ideal on target. Here is the SQL statement 

  SELECT [Origin] as Client
      ,[source] as Device
      ,[target] as Interface
      ,CONVERT (DATE, [sampletime]) as Date
      ,MIN ([samplevalue]/100) as [Min]
      ,MAX ([samplevalue]/100) as [Max]
      ,AVG ([samplevalue]/100) as [Avg]
      ,STDEV ([samplevalue]/100) as [Std]

 

  FROM [UIM].[dbo].[V_QOS_INTERFACE_UTILIZATION]
 
  WHERE ORIGIN = 'StrangerThings' and (Source like '%.st.com%') and sampletime > dateadd(day,-7,GETDATE())
  GROUP BY origin, source, target, CONVERT (DATE, [sampletime])

 

After doing some digging i was able to find that dbo.CM_SNMP_INTERFACE has     

      ,[if_index]
      ,[if_name]
      ,[if_alias]
      ,[if_descr]
      ,[if_type]
      ,[if_oper_status]
      ,[if_admin_status]
      ,[if_phys_address]
      ,[if_speed]

 

I can use the above but I dont know how to join them to the query. Essentially I want to get Source, Target (will have to use some added statements to filter out only the correct interface using the above for eg. if_oper_status =1 , if_admin_status=1, then we it will filter the interface),    CONVERT (DATE, [sampletime]) as Date
      ,MIN ([samplevalue]/100) as [Min]
      ,MAX ([samplevalue]/100) as [Max]
      ,AVG ([samplevalue]/100) as [Avg]

 

Any help or advise is appreciated.

 

Thank You

[Sayeed Islam]

Hello,

 

There is no direct relation between V_QOS_INTERFACE_UTILIZATION and CM_SNMP_INTERFACE.

To bridge this, the V_QOS_INTERFACE_UTILIZATION is joined with CM_COMPUTER_SYSTEM using the source column and then join CM_SNMP_INTERFACE with with CM_COMPUTER_SYSTEM using cs_id.

 

Hope this helps.

Thanks.

 

Regards

-Sayeed

[rtirak]

Herin or Sayeed,

 

Where you ever able to get this to work? I am looking for the same kind of thing.I would like to get the Min, Max, AVG UtilizationIn for an interface for the last month during business hours 8:00 AM to 5:00 PM EST? Thanks for your help in advance.

 

Thanks,