Как импортировать конфигурацию Windows Firewall в SQL Server. Часть 3.
В первой серии мы импортировали конфигурацию файрвола (netsh advfirewall firewall show rule name=all verbose) в промежуточную таблицу #t (Рис.5 первой серии), из которой затем получили сводную таблицу FirewallRules и ее XMLное представление (Рис.2, 10 второй серии). Здесь мы попытаемся получить такой же XML напрямую из таблицы #t.
Прямолинейный способ состоит в том, чтобы пройтись циклом по всем заголовочным строкам правил в таблице #t (у которых parent_id = NULL) и для каждой такой строки организовать вложенный цикл по детальным строкам правила (у которых parent_id = id заголовочной строки). Но это не есть истинное дао БД-программиста. SQL не какой-нибудь процедурный язык и в циклах не нуждается. Взыскующие, прошедшие шаолиньский курс в монастыре Сергея Игнатьевича Моисеенко, должны уметь решить все свои проблемы одним запросом. И хотя, как говорилось выше, select for xml path/explicit по-хорошему требует априорного знания будущих элементов и атрибутов, т.е. им лучше бы находиться в виде названий полей, а в нашем случае они представлены в записях колонки Name, такая мелочь не способна остановить благородного мужа, следующего путем поиска истины. Вместо алиасов полей в as, построим структуру XML динамически непосредственно в выводе select:
declare @x xml = (select CAST('<Rule Name="' + value + '">' + (select '<' + name + '>' + value + '</' + name + '>' from #t where parent_id = t.id for xml path('')) + '</Rule>' as xml) from #t t where parent_id is null for xml path(''), root('WF'), type)
select @x
Рис.1
Замечательно. Единственно, что в данной ситуации смущает благородного мужа, - XML внутри элементов Rule получился какой-то фиговый. Больше смахивает на plain text, чем на XML. Запрос
select @x.value('*[1]/Rule[1]/Enabled[1]', 'nvarchar(MAX)')
Рис.2
подтверждает опасение. Поскольку вложенный запрос на Рис.1 содержит строку, она трактуется как текстовая нода, а не вложенный фрагмент XML. Превратить строку в XML посредством директивы type не представляется возможным, т.к. слева и справа данный фрагмент конкатенируется со строками, символизирующими родительский элемент Rule с атрибутом Name. Не мудрствуя лукаво я заменил в строке эскапированные угловые скобки на обычные и уже результат замены превратил в XML:
declare @s nvarchar(max) = (select CAST('<Rule Name="' + value + '">' + (select '<' + name + '>' + value + '</' + name + '>' from #t where parent_id = t.id for xml path('')) + '</Rule>' as xml) from #t t where parent_id is null for xml path(''), root('WF'))
declare @x xml = cast(REPLACE(REPLACE(@s, '<', '<'), '>', '>') as xml)
select @x
Рис.3
Результат запроса представляет собой честный густопсовый XML, совпадающий с Рис.10 прошлого поста, полученного как XQuery над сводной таблицей, и целеполаганием Скрипт 8 в первой серии. По нему тоже можно писать всяческие XQuery-запросы. Нижеприведенный запрос выбирает правила, в которых дочерний элемент Enabled имеет значение Yes, дочерний элемент Direction - значение In, ..., дочерний элемент LocalPort имеет значение 1433 или Any или не имеет значения (пуст) или вообще отсутствует:
select @x.query('/WF/Rule[Enabled = "Yes" and Direction = "In" and Protocol = "TCP" and Action = "Allow" and (LocalPort = "1433" or LocalPort = "Any" or not(LocalPort/text()) or not(LocalPort))]')
Рис.4
Управляться с XQuery-запросами мне непривычней, нежели со старым добрым SQL, к тому же скорость их выполнения ощутимо уступает. Посему предлагаю переделать Рис.3 в реляционное представление. Это можно сделать при помощи метода value(). В дополнение к упомянутым выше методам query(), позволяющему выполнять XPath и XQuery-запросы, и modify(), позволяющему добавлять, удалять узлы и модифицировать значения узлов в масштабе заданного конкретного узла, переменные и поля типа XML в SQL Server имеют метод value(), вытаскивающий из XML значение по заданному XPath-пути. Мы уже пользовались им на Рис.2, чтобы проверить на XMLность содержимое элемента Rule. При этом нам понадобилось четко оговорить, значение какого конкретно элемента Enabled мы вытягиваем (метод Value ничего не знает про устройство данного документа, может, в нем внутри каждого элемента Rule полагается несколько элементов Enabled) и из какого конкретно элемента Rule. На выходе получится скаляр, что нас не вполне устраивает. Хотелось бы превратить все элементы Rule в строки и из каждой строки вытащить ее Enabled, чтобы эти скаляры образовали колонку. А если вытаскивать не только Enabled, но и другие дочерние элементы, полученные колонки образуют искомую результирующую таблицу. Этого можно добиться в сочетании с четвертым методом, относящимся к типу XML в SQL Server - nodes(). Если в качестве аргумента передать ему XPath-путь к элементу Rule - @x.nodes('/WF/Rule') - на выходе получится набор записей элементов Rule. Напрямую обратится к полю r не удастся, но никто не мешает написать, допустим,
select r.query('.') from @x.nodes('/WF/Rule') t(r)
Рис.5
Пользуясь случаем хочу напомнить, что все XML-методы чувствительны к регистру вне зависимости от выбранной на сервере коллации, т.е. @x.Nodes('/WF/Rule') t(r) SQL Server не поймет: Msg 317, Level 16, State 1... Table-valued function 'Nodes' cannot have a column alias.
Нам осталось вытащить в виде колонок интересующие элементы правил при помощи метода value():
select
r.value('@Name', 'varchar(1000)') Name,
r.value('Enabled[1]', 'varchar(3)') Enabled,
r.value('Direction[1]', 'varchar(3)') Direction,
r.value('Profiles[1]', 'varchar(25)') Profiles,
r.value('Grouping[1]', 'varchar(100)') Grouping,
r.value('LocalIP[1]', 'varchar(15)') LocalIP,
r.value('RemoteIP[1]', 'varchar(15)') RemoteIP,
r.value('Protocol[1]', 'varchar(5)') Protocol,
r.value('LocalPort[1]', 'varchar(5)') LocalPort,
r.value('RemotePort[1]', 'varchar(5)') RemotePort,
r.value('Edge_traversal[1]', 'varchar(3)') EdgeTraversal,
r.value('Program[1]', 'varchar(2000)') Program,
r.value('InterfaceTypes[1]', 'varchar(5)') InterfaceTypes,
r.value('Security[1]', 'varchar(15)') Security,
r.value('Rule_source[1]', 'varchar(15)') Source,
r.value('Action[1]', 'varchar(5)') Action
from @x.nodes('/WF/Rule') t(r)
Рис.6
Классно. Теперь вспомним, что прошлую серию я начал с разглагольствований, что перечислять явно поля в списке IN оператора PIVOT - это негибко, т.к. где гарантия того, что этот список полный, т.е. содержит все уникальные значения пивотной колонки, которые мы хотим превратить в поля сводной таблицы? Так же и здесь. Недостаток решения Рис.6 состоит в том, что колонки приходится вытаскивать и указывать явно. Как и в предыдущем случае, доработка будет состоять в виде (никуда не денешься) динамического SQL. Вначале требуется добыть полный список имен элементов, встречающихся внутри <Rule>. Из каждой записи Рис.5 желательно получить список элементов в виде столбца, затем объединить эти столбцы в одну колонку и выбрать из нее все уникальные значения. Здесь опять выручает функция nodes(), позволяющая вытащить элементы из каждой ячейки в XML-колонке:
select r.query('local-name(.)') from @x.nodes('/WF/Rule/*') t(r)
Рис.7
Остается преобразовать значения в колонке из XML в строки и сделать по ней distinct. Динамический список select в операторе Рис.6 получим тем же макаром, что и на Рис.1 прошлой серии. Единственное отличие - динамический оператор в данном случае включает переменную @x (Рис.3), содержащую, собственно, тот самый XML, над которым мы изгаляемся. Динамический SQL выполняется отдельным батчем, в контексте которого переменные внешнего скрипта не видны. Следовательно, вместо exec будем использовать расширенную хранимую процедуру sp_executesql, передавая в нее @x в качестве параметра.
set @s = (select distinct ', r.value(''' + cast(r.query('local-name(.)') as varchar(100)) + '[1]'', ''varchar(1000)'')' from @x.nodes('/WF/Rule/*') t(r) for xml path(''))
set @s = 'select r.value(''@Name'', ''varchar(1000)'') Name' + @s + ' from @x.nodes(''/WF/Rule'') t(r)'
exec sp_executesql @statement = @s, @params = N'@x xml', @x = @x
Рис.8
Замечательно. К сожалению, пора закругляться и подвести какой-нибудь итог. Итак, в данной статье мы разобрали некоторые способы представления и преобразования в SQL Server данных по сценарию EAV (Entity - Attribute - Value). Пример с конфигурацией файрвола был взят лишь в качестве иллюстрации и, вообще говоря, достаточно условен. Вместо него может выступать любой набор сущностей, содержащих пары атрибут - значение, причем полный набор атрибутов заранее неизвестен. Этот набор был нами представлен в виде таблицы сущностей, колонками которой выступали атрибуты, которая затем была преобразована в XML, и, наоборот, сначала представлен в виде XML и затем преобразован в таблицу. В основном, получилось упражнение на оператор PIVOT и XQuery-запросы. Формирование полного перечня атрибутов, таблицы, XML-документа и преобразования между ними носили динамический характер. В качестве дальнейшего углубления темы читателям предлагается рассмотреть вариант построения таблицы с разреженными колонками (sparse columns), дабы превзойти ограничение в 1024 колонки на таблицу.
Алексей Шуленин