Parse an GPO XML with R or Python
I need to parse a bunch of exported AD GPO XMLs to csv/xls or any other filetype with unpivoted data. Generally, I need only GPO Name, GPO Description and whom affected by this policy.
So, I tried to make it by myself with R, but I had a problem with parsing itself.
<?xml version="1.0" encoding="utf-8"?>
<GPO xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.microsoft.com/GroupPolicy/Settings">
<Identifier>
<Identifier xmlns="http://www.microsoft.com/GroupPolicy/Types">{0E8443B7-E547-4C6C-B201-A60D567795CF}</Identifier>
<Domain xmlns="http://www.microsoft.com/GroupPolicy/Types">********</Domain>
</Identifier>
<Name>ITSEC Event Log Forward</Name>
<IncludeComments>true</IncludeComments>
<CreatedTime>2012-11-21T05:54:01</CreatedTime>
<ModifiedTime>2020-09-02T09:05:31</ModifiedTime>
<ReadTime>2022-03-01T14:52:26.625719Z</ReadTime>
<SecurityDescriptor>
<SDDL xmlns="http://www.microsoft.com/GroupPolicy/Types/Security">O:S-1-5-21-;;S-1-5-21-)(OA;OICI;CR;edacfd8f-ffb3-11d1-b41d-00a0c968f939;;S-1-5-21-)(OA;OICI;CR;edacfd8f-ffb3-11d1-b41d-00a0c968f939;;AU)(A;;CCDCLCSWRPWPDTLOSDRCWDWO;;;S-1-5-21-)(A;;CCDCLCSWRPWPDTLOSDRCWDWO;;;S-1-5-21-685724207-866877060-2473498247-36290)(A;OICI;LCRPRC;;;S-1-5-21-685724207-866877060-2473498247-18511)(A;OICI;LCRPRC;;;S-1-5-21-685724207-866877060-2473498247-37622)(A;OICIIO;0x1f00ff;;;S-1-5-21-685724207-866877060-2473498247-18510)(A;OICIIO;0x11f00ff;;;S-1-5-21-685724207-866877060-2473498247-36290)(A;;CCDCLCSWRPWPDTLOSDRCWDWO;;;SY)(A;OICI;LCRPRC;;;ED)(A;OICI;LCRPRC;;;AU)(A;OICIIO;0x1f00ff;;;SY)S:AI(OU;CIIDSA;CCDC;f30e3bc2-9ff0-11d1-b603-0000f80367c1;;WD)(OU;CIIDSA;WPWD;;f30e3bc2-9ff0-11d1-b603-0000f80367c1;WD)(OU;CIIDSA;WPSD;;f30e3bc2-9ff0-11d1-b603-0000f80367c1;WD)(OU;CIIOIDSA;WP;f30e3bbe-9ff0-11d1-b603-0000f80367c1;bf967aa5-0de6-11d0-a285-00aa003049e2;WD)(OU;CIIOIDSA;WP;f30e3bbf-9ff0-11d1-b603-0000f80367c1;bf967aa5-0de6-11d0-a285-00aa003049e2;WD)(OU;CIIDSA;CR;9923a32a-3607-11d2-b9be-0000f87a36b2;;WD)(OU;CIIDSA;CR;bae50096-4752-11d1-9052-00c04fc2d4cf;;WD)(OU;CIIDSA;CR;440820ad-65b4-11d1-a3da-0000f875ae0d;;WD)(OU;CIIDSA;CCDC;bf967a86-0de6-11d0-a285-00aa003049e2;;WD)(OU;CIIDSA;CCDC;bf967a9c-0de6-11d0-a285-00aa003049e2;;WD)(OU;CIIDSA;CCDC;bf967aa5-0de6-11d0-a285-00aa003049e2;;WD)(OU;CIIDSA;CCDC;bf967aba-0de6-11d0-a285-00aa003049e2;;WD)(OU;CIIOIDSA;WPSD;;bf967a86-0de6-11d0-a285-00aa003049e2;WD)(OU;CIIOIDSA;WPSD;;bf967a9c-0de6-11d0-a285-00aa003049e2;WD)(OU;CIIOIDSA;WPSD;;bf967aa5-0de6-11d0-a285-00aa003049e2;WD)(OU;CIIOIDSA;WPSD;;bf967aba-0de6-11d0-a285-00aa003049e2;WD)(AU;CIIDSA;CCWPDTSDWDWO;;;WD)</SDDL>
<Owner xmlns="http://www.microsoft.com/GroupPolicy/Types/Security">
<SID xmlns="http://www.microsoft.com/GroupPolicy/Types">S-1-5-21-</SID>
<Name xmlns="http://www.microsoft.com/GroupPolicy/Types">AD\AGPMService</Name>
</Owner>
<Group xmlns="http://www.microsoft.com/GroupPolicy/Types/Security">
<SID xmlns="http://www.microsoft.com/GroupPolicy/Types">S-1-5-21-685724207-866877060-2473498247-512</SID>
<Name xmlns="http://www.microsoft.com/GroupPolicy/Types">AD\Domain Admins</Name>
</Group>
<PermissionsPresent xmlns="http://www.microsoft.com/GroupPolicy/Types/Security">true</PermissionsPresent>
<Permissions xmlns="http://www.microsoft.com/GroupPolicy/Types/Security">
<InheritsFromParent>false</InheritsFromParent>
<TrusteePermissions>
<Trustee>
<SID xmlns="http://www.microsoft.com/GroupPolicy/Types">S-1-5-21-</SID>
<Name xmlns="http://www.microsoft.com/GroupPolicy/Types">AD\All users</Name>
</Trustee>
<Type xsi:type="PermissionType">
<PermissionType>Allow</PermissionType>
</Type>
<Inherited>false</Inherited>
<Applicability>
<ToSelf>true</ToSelf>
<ToDescendantObjects>true</ToDescendantObjects>
<ToDescendantContainers>true</ToDescendantContainers>
<ToDirectDescendantsOnly>false</ToDirectDescendantsOnly>
</Applicability>
<Standard>
<GPOGroupedAccessEnum>Read</GPOGroupedAccessEnum>
</Standard>
<AccessMask>0</AccessMask>
</TrusteePermissions>
<TrusteePermissions>
<Trustee>
<SID xmlns="http://www.microsoft.com/GroupPolicy/Types">S-1-5-9</SID>
<Name xmlns="http://www.microsoft.com/GroupPolicy/Types">NT AUTHORITY\ENTERPRISE DOMAIN CONTROLLERS</Name>
</Trustee>
<Type xsi:type="PermissionType">
<PermissionType>Allow</PermissionType>
</Type>
<Inherited>false</Inherited>
<Applicability>
<ToSelf>true</ToSelf>
<ToDescendantObjects>true</ToDescendantObjects>
<ToDescendantContainers>true</ToDescendantContainers>
<ToDirectDescendantsOnly>false</ToDirectDescendantsOnly>
</Applicability>
<Standard>
<GPOGroupedAccessEnum>Read</GPOGroupedAccessEnum>
</Standard>
<AccessMask>0</AccessMask>
</TrusteePermissions>
<TrusteePermissions>
<Trustee>
<SID xmlns="http://www.microsoft.com/GroupPolicy/Types">S-1-5-11</SID>
<Name xmlns="http://www.microsoft.com/GroupPolicy/Types">NT AUTHORITY\Authenticated Users</Name>
</Trustee>
<Type xsi:type="PermissionType">
<PermissionType>Allow</PermissionType>
</Type>
<Inherited>false</Inherited>
<Applicability>
<ToSelf>true</ToSelf>
<ToDescendantObjects>true</ToDescendantObjects>
<ToDescendantContainers>true</ToDescendantContainers>
<ToDirectDescendantsOnly>false</ToDirectDescendantsOnly>
</Applicability>
<Standard>
<GPOGroupedAccessEnum>Apply Group Policy</GPOGroupedAccessEnum>
</Standard>
<AccessMask>0</AccessMask>
</TrusteePermissions>
<TrusteePermissions>
<Trustee>
<SID xmlns="http://www.microsoft.com/GroupPolicy/Types">S-1-5-21-</SID>
<Name xmlns="http://www.microsoft.com/GroupPolicy/Types">AD\AGPMowner</Name>
</Trustee>
<Type xsi:type="PermissionType">
<PermissionType>Allow</PermissionType>
</Type>
<Inherited>false</Inherited>
<Applicability>
<ToSelf>true</ToSelf>
<ToDescendantObjects>true</ToDescendantObjects>
<ToDescendantContainers>true</ToDescendantContainers>
<ToDirectDescendantsOnly>false</ToDirectDescendantsOnly>
</Applicability>
<Standard>
<GPOGroupedAccessEnum>Apply Group Policy</GPOGroupedAccessEnum>
</Standard>
<AccessMask>0</AccessMask>
</TrusteePermissions>
</Permissions>
<AuditingPresent xmlns="http://www.microsoft.com/GroupPolicy/Types/Security">false</AuditingPresent>
</SecurityDescriptor>
<FilterDataAvailable>true</FilterDataAvailable>
<Computer>
<VersionDirectory>287</VersionDirectory>
<VersionSysvol>287</VersionSysvol>
<Enabled>true</Enabled>
<ExtensionData>
<Extension xmlns:q1="http://www.microsoft.com/GroupPolicy/Settings/Windows/Registry" xsi:type="q1:RegistrySettings">
<q1:RegistrySettings clsid="{A3CCFC41-DFDB-43a5-8D26-0FE8B954DA51}">
<q1:Collection clsid="{53B533F5-224C-47e3-B01B-CA3B3F3FF4BF}" name="EventLogSubscription" status="Values generated by the Registry Wizard" changed="2013-04-24 06:23:32" uid="{E5A36FB5-C34A-4156-8EAF-7E8D243AB7EC}" bypassErrors="1">
<q1:GPOSettingOrder>1</q1:GPOSettingOrder>
<q1:Filters />
<q1:Registry clsid="{9CD4B2F4-923D-47f5-A062-E897DD1DAD50}" name="EventLogSubscription for 000" descr="Wizard Generated Registry Item" image="7" status="1" changed="2015-11-06 07:59:00" uid="{009827A2-E767-418E-8C84-7250030565E8}" bypassErrors="1">
<q1:GPOSettingOrder>1</q1:GPOSettingOrder>
<q1:Properties action="U" displayDecimal="0" default="0" hive="HKEY_LOCAL_MACHINE" key="SOFTWARE\Policies\Microsoft\Windows\EventLog\EventForwarding\SubscriptionManager" name="1" type="REG_SZ" value="Server=">
<q1:Values />
</q1:Properties>
<q1:Filters>
<q1:FilterCollection bool="AND" not="0">
<q1:FilterCollection bool="AND" not="0">
<q1:FilterComputer bool="AND" not="0" type="NETBIOS" name="00*" />
<q1:FilterComputer bool="OR" not="0" type="NETBIOS" name="01*" />
</q1:FilterCollection>
</q1:FilterCollection>
</q1:Filters>
</q1:Registry>
<q1:Registry clsid="{9CD4B2F4-923D-47f5-A062-E897DD1DAD50}" name="EventLogSubscription for 030" descr="Wizard Generated Registry Item" image="7" status="1" changed="2015-11-06 07:58:51" uid="{D1757DF9-B1D4-4646-8BC1-57A22C883EC4}" bypassErrors="1">
<q1:GPOSettingOrder>2</q1:GPOSettingOrder>
<q1:Properties action="U" displayDecimal="0" default="0" hive="HKEY_LOCAL_MACHINE" key="SOFTWARE\Policies\Microsoft\Windows\EventLog\EventForwarding\SubscriptionManager" name="1" type="REG_SZ" value="Server=http://030">
<q1:Values />
</q1:Properties>
<q1:Filters>
<q1:FilterCollection bool="AND" not="0">
<q1:FilterComputer bool="AND" not="0" type="NETBIOS" name="03*" />
</q1:FilterCollection>
</q1:Filters>
</q1:Registry>
<q1:Registry clsid="{9CD4B2F4-923D-47f5-A062-E897DD1DAD50}" name="EventLogSubscription for 040" descr="Wizard Generated Registry Item" image="7" status="1" changed="2015-11-06 07:59:19" uid="{976CF190-22E6-43A6-9444-002F4B1E1BBA}" bypassErrors="1">
<q1:GPOSettingOrder>3</q1:GPOSettingOrder>
<q1:Properties action="U" displayDecimal="0" default="0" hive="HKEY_LOCAL_MACHINE" key="SOFTWARE\Policies\Microsoft\Windows\EventLog\EventForwarding\SubscriptionManager" name="1" type="REG_SZ" value="Server=http://040">
<q1:Values />
</q1:Properties>
<q1:Filters>
<q1:FilterCollection bool="AND" not="0">
<q1:FilterComputer bool="AND" not="0" type="NETBIOS" name="04*" />
</q1:FilterCollection>
</q1:Filters>
</q1:Registry>
<q1:Registry clsid="{9CD4B2F4-923D-47f5-A062-E897DD1DAD50}" name="EventLogSubscription for 0600" descr="Wizard Generated Registry Item" image="7" status="1" changed="2015-11-06 07:59:34" uid="{1CC2A25B-30C6-42CD-BC86-2ED9B2E1A874}" bypassErrors="1">
<q1:GPOSettingOrder>4</q1:GPOSettingOrder>
<q1:Properties action="U" displayDecimal="0" default="0" hive="HKEY_LOCAL_MACHINE" key="SOFTWARE\Policies\Microsoft\Windows\EventLog\EventForwarding\SubscriptionManager" name="1" type="REG_SZ" value="Server=http://060">
<q1:Values />
</q1:Properties>
<q1:Filters>
<q1:FilterCollection bool="AND" not="0">
<q1:FilterComputer bool="AND" not="0" type="NETBIOS" name="06*" />
</q1:FilterCollection>
</q1:Filters>
</q1:Registry>
<q1:Registry clsid="{9CD4B2F4-923D-47f5-A062-E897DD1DAD50}" name="EventLogSubscription for 070" descr="Wizard Generated Registry Item" image="7" status="1" changed="2015-11-06 07:59:48" uid="{8A3FCBF8-FD61-4ECB-AA91-DEB9C681682C}" bypassErrors="1">
<q1:GPOSettingOrder>5</q1:GPOSettingOrder>
<q1:Properties action="U" displayDecimal="0" default="0" hive="HKEY_LOCAL_MACHINE" key="SOFTWARE\Policies\Microsoft\Windows\EventLog\EventForwarding\SubscriptionManager" name="1" type="REG_SZ" value="Server=http://070">
<q1:Values />
</q1:Properties>
<q1:Filters>
<q1:FilterCollection bool="AND" not="0">
<q1:FilterComputer bool="OR" not="0" type="NETBIOS" name="07*" />
</q1:FilterCollection>
</q1:Filters>
</q1:Registry>
<q1:Registry clsid="{9CD4B2F4-923D-47f5-A062-E897DD1DAD50}" name="EventLogSubscription for 090" descr="Wizard Generated Registry Item" image="7" status="1" changed="2015-11-06 08:00:15" uid="{08CF4699-0EE7-4761-8244-7B1901138BD5}" bypassErrors="1">
<q1:GPOSettingOrder>6</q1:GPOSettingOrder>
<q1:Properties action="U" displayDecimal="0" default="0" hive="HKEY_LOCAL_MACHINE" key="SOFTWARE\Policies\Microsoft\Windows\EventLog\EventForwarding\SubscriptionManager" name="1" type="REG_SZ" value="Server=http://090">
<q1:Values />
</q1:Properties>
<q1:Filters>
<q1:FilterCollection bool="AND" not="0">
<q1:FilterComputer bool="AND" not="0" type="NETBIOS" name="09*" />
</q1:FilterCollection>
</q1:Filters>
</q1:Registry>
<q1:Registry clsid="{9CD4B2F4-923D-47f5-A062-E897DD1DAD50}" name="EventLogSubscription for 080" descr="Wizard Generated Registry Item" image="7" status="1" changed="2015-11-06 08:00:02" uid="{D2DE191A-43A9-476D-94FA-9C8F341C470A}" bypassErrors="1">
<q1:GPOSettingOrder>7</q1:GPOSettingOrder>
<q1:Properties action="U" displayDecimal="0" default="0" hive="HKEY_LOCAL_MACHINE" key="SOFTWARE\Policies\Microsoft\Windows\EventLog\EventForwarding\SubscriptionManager" name="1" type="REG_SZ" value="Server=http://080">
<q1:Values />
</q1:Properties>
<q1:Filters>
<q1:FilterCollection bool="AND" not="0">
<q1:FilterComputer bool="AND" not="0" type="NETBIOS" name="08*" />
</q1:FilterCollection>
</q1:Filters>
</q1:Registry>
</q1:Collection>
</q1:RegistrySettings>
</Extension>
<Name>Windows Registry</Name>
</ExtensionData>
<ExtensionData>
<Extension xmlns:q2="http://www.microsoft.com/GroupPolicy/Settings/Registry" xsi:type="q2:RegistrySettings">
<q2:Policy>
<q2:Name>Configure log access</q2:Name>
<q2:State>Enabled</q2:State>
<q2:Comment>INC000007330355</q2:Comment>
<q2:Explain>This policy setting specifies the security descriptor to use for the log using the Security Descriptor Definition Language (SDDL) string. You cannot configure write permissions for this log. You must set both "configure log access" policy settings for this log in order to affect the both modern and legacy tools.
If you enable this policy setting, only those users whose security descriptor matches the configured specified value can access the log.
If you disable or do not configure this policy setting, only system software and administrators can read or clear this log.
Note: If you enable this policy setting, some tools and APIs may ignore it. The same change should be made to the "Configure log access (legacy)" policy setting to enforce this change across all tools and APIs.</q2:Explain>
<q2:Supported>At least Windows Vista</q2:Supported>
<q2:Category>Windows Components/Event Log Service/Security</q2:Category>
<q2:EditText>
<q2:Name>Log Access</q2:Name>
<q2:State>Enabled</q2:State>
<q2:Value>O:BAG:SYD:(A;;0xf0005;;;SY)(A;;0x5;;;BA)(A;;0x1;;;S-1-5-32-573)(A;;0x1;;;s-1-5-20)</q2:Value>
</q2:EditText>
</q2:Policy>
</Extension>
<Name>Registry</Name>
</ExtensionData>
</Computer>
<User>
<VersionDirectory>21</VersionDirectory>
<VersionSysvol>21</VersionSysvol>
<Enabled>false</Enabled>
</User>
<LinksTo>
<SOMName>Domain Controllers</SOMName>
<SOMPath>m/Domain Controllers</SOMPath>
<Enabled>true</Enabled>
<NoOverride>false</NoOverride>
</LinksTo>
</GPO>
Thanks for helping such newbies as me. Cheers!
Solution
After setting the document to UTF-8 using <?xml version="1.0" encoding="utf-8"?> and fixing a missing " using value="Server=http://080">, one can extract XML nodes using XPath and convert them to a list and then to a data frame, so it can be saved as an CSV table:
library(xml2)
library(tidyverse)
xml <-
read_xml("data.xml", encoding = "UTF-8") %>%
xml_ns_strip()
data <-
xml %>%
xml_find_all("//Name") %>%
as_list() %>%
enframe() %>%
unnest_auto(value)
data
# name value
# <int> <chr>
# 1 1 "ITSEC Event Log Forward"
# 2 2 "AD\\AGPMService"
# 3 3 "AD\\Domain Admins"
# 4 4 "AD\\All users"
# 5 5 "NT AUTHORITY\\ENTERPRISE DOMAIN CONTROLLERS"
# 6 6 "NT AUTHORITY\\Authenticated Users"
# 7 7 "AD\\AGPMowner"
# 8 8 "Windows Registry"
# 9 9 "Registry"
data %>% write_csv("data.csv")
# extract a particular user id
xml %>%
xml_find_all("//User/VersionSysvol") %>%
xml_text() %>%
as.numeric()
#[1] 21
xml %>%
xml_find_all("//Trustee/Name") %>%
xml_text()
# [1] "AD\\All users" "NT AUTHORITY\\ENTERPRISE DOMAIN CONTROLLERS" "NT AUTHORITY\\Authenticated Users"
- How to get the selected tab's name of a Quarto Tabset Panel?
- Separting alphanumeric string using tidyr separate wider regex
- Convert JSON type scraped string to dataframe in R
- Testing private methods in R6 classes in R
- How can I align the tag with the displayed text?
- Date labels in the x axis are misaligned by one month
- How can I force a shiny app to switch colors after a specific time?
- pretty_breaks not working with geom_spatraster
- pretty_breaks does not working with geom_sf
- overwrite a facet panel with custom plot
- How to draw a shapefile with ggpolar
- Exported file from R using `haven` cannot be opened by SAS
- How to install libpng-dev on Mac
- Ignoring NAs while aggregating character vector
- Shinyselected row in a striped DT::datatable() disappears when style = "bootstrap5" and RowGroup extension is used
- ggplot2 secondary axis tidyeval
- parse dynamic case_when based on thresholds values
- dbWriteTable or dbAppendTable fail for date column when appending to DB2-table
- How can I use a loop in RMarkdown to programmatically plot figures and text on individual pages in a document?
- Conditional recursive formula in dplyr
- Current development in how to adjust individual x axis title using ggplot facet_wrap
- How to make a probability figure using a logistic regression?
- dplyr - Need to add the CSV filename after importing all files from a directory
- How to reorder a stacked bar chart in ggplot2?
- how can I render a quarto website to a single pdf
- Assign specific values to unique values in all dataframe columns in R
- Unable to install tidyverse package for r kernel in jupyter
- How to replace values in a matrix based on a conditional variable
- How can I make my enormous dataframe from wide to long in R?
- Group variable stacking when using geom_ridgeline_gradient()