(─━┘_└━─)/ 머리만1톤(Head1Ton)

SELECT  * FROM SYS.DM_OS_PERFORMANCE_COUNTERS

WHERE REPLACE(RIGHT([OBJECT_NAME], PATINDEX('%:%', REVERSE([OBJECT_NAME]))-1), ' ', '') =

 'GeneralStatistics'

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;

        }

    }

}

--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

--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', '')

SELECT * FROM dbo.UDF_WMI('Servername', 'UserID', 'P@ssword', 'Win32_LogicalDisk', 'Name', 'FreeSpace',

 'DriveType=3')