AnsweredAssumed Answered

Interface Utilization SQL Query

Question asked by Herin on Sep 5, 2016
Latest reply on Jul 11, 2017 by rtirak

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

Outcomes