WMI 객체를 쿼리로 읽어오기

UDF_WMI.cs

WMICodeCreator.zip

DB서버를 관리하다 보면 성능카운터를 수집하거나 모니터링 하는 일이 많습니다. 성능 모니터를 사용해서 수집 및 모니터링을 할 수도 있고 써드파티툴을 사용할 수도 있습니다.   SQL 2005 부터는 DMV 등을 통해서 성능 카운터를 모니터링 할 수 있습니다.    SELECT  * FROM SYS.DM_OS_PERFORMANCE_COUNTERS WHERE REPLACE(RIGHT([OBJECT_NAME], PATINDEX('%:%', REVERSE([OBJECT_NAME]))-1), ' ', '') =  'GeneralStatistics'   그러나 DMV 도 모든 WMI 카운터를 제공하지는 않습니다. 특히 하드디스크의 용량 같은 물리적인 부분이나 OS 단의 카운터 중 제공 안 하는 것들이 있습니다. 이 부분을 따로 WMI 툴로 만들어서 볼 수도 있습니다만 이걸 CLR로 등록해서 쿼리로 바로 보도록 하겠습니다. WMI 개발에 관련된 자료는 쉽게 찾아볼 수가 있고 코드까지 만들어주는 툴도 있습니다. 예전에 한대성님이 ‘Scriptomatic2’ 도 소개해주셨습니다만 이번엔 ‘WMICodeCreator’ 이라는 툴로 해보겠습니다. (#파일첨부) 원하는 WMI클래스와 객체를 선택하면 바로 VB 또는 C# 코드를 만들어줍니다. Condition 설정도 가능합니다.   이렇게 만들어진 코드를 가지고 CLR로 등록해서 Function으로 만들어 직접 쿼리를 날려보겠습니다. 이 코드를 CLR로 등록하려면 사전에 한가지 설정을 해야 됩니다.   WMI 데이터를 읽기 위해선 System.management 네임스페이스의 클래스들을 사용합니다. 그래서 데이터베이스에 사전에 System.Management 어셈블리가 등록되어 있어야 됩니다.   USE TEST GO   --CLR ENABLE EXEC SP_CONFIGURE 'clr enabled', 1 RECONFIGURE WITH OVERRIDE GO   --TRUSTWORTHY ON ALTER DATABASE TEST SET TRUSTWORTHY ON GO   --System.Management 등록 IF EXISTS(SELECT 1 FROM SYS.ASSEMBLIES WHERE NAME = 'System.Management') BEGIN              DROP ASSEMBLY [System.Management] END GO CREATE ASSEMBLY [System.Management]              AUTHORIZATION [dbo]              FROM 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Management.dll'              WITH PERMISSION_SET = UNSAFE GO   System.Management 어셈블리가 등록되지 않은 상태에서 컴파일을 할 경우 아래와 같은 오류를 만나게 됩니다.   이 부분 외에 다른 설정은 보통 CLR 모듈을 등록하는 것과 동일합니다. 김종열님께서 다른 강좌에서 많이 보여주셨기 때문에 생략하도록 하겠습니다.. 비쥬얼 스튜디오에서 C# SQL Server 프로젝트를 생성하신 후 데이터베이스 참조를 설정하시고 아래 소스를 붙여 넣으시기 바랍니다.    using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; using System.Management; using System.Net; using System.Net.Sockets; using System.Collections;   public partial class UserDefinedFunctions {     [Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName="fillTable", TableDefinition="Name nvarchar(20), Value nvarchar(20)")]     public static IEnumerable UDF_WMI(string srvname, string username, string password, string wmiClass, string wmiName, string wmiValue, string condition)     {                //서버 연결         ManagementScope scope = ConnServer(srvname, username, password);           //조회 쿼리(WQL) 생성         if (condition.Length > 0)         {             condition = " WHERE " + condition;         }           try         {                           ObjectQuery query = new ObjectQuery("SELECT " + wmiName + ", " + wmiValue + " FROM " + wmiClass + condition);               //개체 컬렉션 검색             ManagementObjectSearcher searcher = new ManagementObjectSearcher(scope, query);             ManagementObjectCollection oCollection = searcher.Get();               //return 변수 선언             string[] retArray = new string[oCollection.Count];               //컬렉션 열거자             ManagementObjectCollection.ManagementObjectEnumerator                 oEnumerator = oCollection.GetEnumerator();             oEnumerator.MoveNext();               //return 변수 입력             for (int i = 0; i < oCollection.Count; i++)             {                 ManagementObject o = (ManagementObject)oEnumerator.Current;                 retArray[i] = Convert.ToString(o[wmiName]) + "," + Convert.ToString(o[wmiValue]);                 oEnumerator.MoveNext();             }             return (retArray);         }         catch (ManagementException e)         {             Console.WriteLine("An error occurred while querying for WMI data: " + e.Message);             return "-1";         }     }       public static ManagementScope ConnServer(string srvname, string username, string password)     {         try         {             //로컬인지 확인             bool local = CheckLocal(srvname);               //연결설정             ConnectionOptions conn = new ConnectionOptions();             conn.Username = username;             conn.Password = password;               //관리 범위 지정             ManagementScope scope = new ManagementScope();               //경로 지정             ManagementPath path = new ManagementPath();               //로컬일 경우 사용자 자격 증명 제외             if (local == true)             {                 path.Path = "root\\CIMV2";             }             else             {                 path.Path = "\\\\" + srvname + "\\root\\CIMV2";                 scope.Options = conn;             }             //연결             scope.Path = path;             scope.Connect();               return scope;         }         catch (ManagementException e)         {             Console.WriteLine("Server connecting error: " + e.Message);             return null;         }     }       public static bool CheckLocal(string srvname)     {         //로컬의 호스트명과 IP주소를 가져온다.         IPHostEntry ihe = Dns.GetHostEntry(Dns.GetHostName());         string strHostName = ihe.HostName;  //호스트명         foreach (IPAddress ip in ihe.AddressList)         {             if (ip.AddressFamily == AddressFamily.InterNetwork)             {                 if ((srvname == ip.ToString()) || (srvname.ToUpper() == strHostName.ToUpper()))                 {                     return true;                 }             }         }         return false;     }       private static void fillTable(Object obj, out SqlString Name, out SqlString Value)     {         string sTemp = Convert.ToString(obj);         string[] arr_temp = sTemp.Split(',');           try         {             Name = (SqlString)arr_temp[0];             Value = (SqlString)arr_temp[1];         }         catch         {             Name = null;             Value = null;         }     }     }       그리고 솔루션 탐색기에서 참조를 선택하시고 마우스 오른쪽 버튼 참조추가를 선택하시고 팝업에서 SQL Server탭의 System.Management 를 선택하시기 바랍니다.   이제 모든 준비가 끝났으니 dll을 등록하겠습니다. 배포를 하셔도 되고 빌드 후 수동으로 등록하셔도 됩니다.    --dll 등록 IF EXISTS(SELECT 1 FROM SYS.ASSEMBLIES WHERE NAME = 'WMICLR') BEGIN              DROP ASSEMBLY WMICLR END GO CREATE ASSEMBLY [WMICLR]              AUTHORIZATION [dbo]              FROM 'D:\WMICLR\WMICLR\bin\Debug\WMICLR.dll'              WITH PERMISSION_SET = UNSAFE GO     쿼리를 사용하기 위해 SQL Function 을 생성하고 Function 을 실행한 결과입니다.. 쿼리에서 서버명, UserID, Password 를 환경에 맞게 수정하신 후 실행 하시기 바랍니다. 참고로 로컬일 경우 계정 검사를 하지 않습니다.    --Function 등록 IF EXISTS(SELECT 1 FROM SYS.OBJECTS WHERE NAME = 'UDF_WMI') BEGIN              DROP FUNCTION DBO.UDF_WMI END GO CREATE FUNCTION DBO.UDF_WMI (              @SRVNAME NVARCHAR(4000) = '',              @USERNAME NVARCHAR(4000) = '',              @PASSWORD NVARCHAR(4000) = '',              @WMICLASS NVARCHAR(4000) = '',              @WMINAME NVARCHAR(4000) = '',              @WMIVALUE NVARCHAR(4000) = '',              @CONDITION NVARCHAR(4000) = '' ) RETURNS TABLE ([NAME] NVARCHAR(1000),[VALUE] nvarchar(1000)) AS EXTERNAL NAME [WMICLR].UserDefinedFunctions.UDF_WMI GO   SELECT * FROM dbo.UDF_WMI('Servername', 'UserID', 'P@ssword', 'Win32_LogicalDisk', 'Name', 'Size', '')    C와 D드라이브의 논리디스크 용량입니다. E와 F는 DVD드라이브와 가상드라이브입니다.   이번엔 하드디스크만 나오도록 Condition 을 설정하고 총 용량이 아닌 남은 용량을 보도록 하겠습니다. Condition 이나 WMI객체들은 ‘WMI Code Creator’ 등을 사용해서 확인하시기 바랍니다.    SELECT * FROM dbo.UDF_WMI('Servername', 'UserID', 'P@ssword', 'Win32_LogicalDisk', 'Name', 'FreeSpace',  'DriveType=3')       수집방식이 성능모니터와는 다른지 특정 카운터들은 성능모니터와 다른 값들을 보여주기도 합니다.  제한된 용도내에서 충분히 테스트 하시고 사용한다면 편리하게 쓰실 수 있을 겁니다.   참고로 WMI를 사용하기 위해 원격을 접속하는 계정은 윈도우 계정이며 Administrator 권한이 있을 경우엔 별 다른 문제가 없습니다만 보안상 Administrator 권한이 없을 경우 아래와 같이 약간 복잡한 설정을 해줘야 됩니다.