I have a big xml file produced by windows log and i need to import it into R to analyse some correlation beetween the events logged.
The problem i encounter is that the events’details (EventData) are kept as xml’s attributes and the number of attributes changes by the kind of the events.
The simplified xml to import is:
<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<Events>
<Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
<System>
<EventID>259</EventID>
<Version>0</Version>
<Level>2</Level>
<TimeCreated SystemTime="2024-07-10T15:02:35.203376200Z"/>
</System>
<EventData>
<Data Name="TCP">0</Data>
<Data Name="InterfaceIP">0.0.0.0</Data>
<Data Name="Source">192.168.109.4</Data>
<Data Name="QNAME"/>
</EventData>
</Event>
<Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
<System>
<EventID>256</EventID>
<Version>0</Version>
<Level>4</Level>
<TimeCreated SystemTime="2024-07-10T15:02:35.203362300Z"/>
</System>
<EventData>
<Data Name="TCP">0</Data>
<Data Name="InterfaceIP">0.0.0.0</Data>
<Data Name="Source">192.168.109.4</Data>
<Data Name="QNAME">microsoft.com.</Data>
<Data Name="QTYPE">0</Data>
<Data Name="XID">22342</Data>
<Data Name="Port">65220</Data>
<Data Name="Flags">16979</Data>
<Data Name="BufferSize">108</Data>
</EventData>
</Event>
</Events>
This is what I expect.
The code I wrote is:
library(xml2)
library(tibble)
library(dplyr)
file_dns <- c("./test_ridotto_2.xml")
read_xml(file_dns) -> dns
dns_items <- tibble(
event_id = xml_find_all(x = dns, xpath = "/Events/Event/System/EventID") |> xml_text(),
time_created = xml_find_all(x = dns, xpath = "/Events/Event/System/TimeCreated") |> xml_attr(attr = "SystemTime"),
info4 = xml_find_all(x = dns, xpath = "/Events/Event/EventData") |> as_list()
)
The problem I have, is that I don’t understand how to continue to split columns and give them the name of the attribute, as the number of attributes and so the number of columns change row by row and depend from the type of the event logged.
I tried the unnest_wider command but it lost attributes and seems slow on my simplified test file.
Production file is 1Gb.
How to obtain the expected result?
2
This script extracts out the eventID and createdtime from each event and then loops through the events and extracts out the data elements and node ID to create a dataframe for your final answer.
See the comments below for a step-by-step.
library(xml2)
library(dplyr)
#read page and strip namespace
page <- read_xml(text)
xml_ns(page)
#find the events nodes
events <- xml_find_all(page, ".//Event")
#get the eventID and Created time
eventID <- xml_find_first(events, ".//EventID") %>% xml_text()
CreatedTime <-xml_find_first(events, ".//TimeCreated") %>% xml_attr("SystemTime")
#loop through the events and extract the data
#make a data frame and create the proper column headings
dfs <- lapply(events, function(node){
data <- xml_find_all(node, ".//Data") %>% xml_text()
tag <- xml_find_all(node, ".//Data") %>% xml_attrs() %>% unlist()
names(tag)<-NULL
df<-as.data.frame(t(data))
names(df) <- tag
df
})
#make a data frame for the answer
answer <- bind_rows(dfs)
#add the event ID and timecreated
answer <-bind_cols(EventID=eventID, CreatedTime=CreatedTime, answer)
EventID CreatedTime TCP InterfaceIP Source QNAME QTYPE XID Port Flags BufferSize
1 259 2024-07-10T15:02:35.203376200Z 0 0.0.0.0 192.168.109.4 <NA> <NA> <NA> <NA> <NA>
2 256 2024-07-10T15:02:35.203362300Z 0 0.0.0.0 192.168.109.4 microsoft.com. 0 22342 65220 16979 108
An approach using xml2::as_list. The attributes of the elements stay intact even when they are put in a dataframe/tibble with as_tibble.
The performance will be limited by purrr since it has to process columns element-wise.
library(xml2)
library(dplyr)
library(tidyr)
as_tibble(as_list(read_xml("event.xml"))) %>%
unnest_wider(Events) %>%
mutate(EventID = purrr::map_vec(System, ~ unlist(.x$EventID)),
TimeCreated = purrr::map_vec(System, ~ attr(.x$TimeCreated, "SystemTime"))) %>%
select(-System) %>%
unnest(EventData) %>%
mutate(name = purrr::map_vec(EventData, ~ unlist(attributes(.x))),
value = purrr::map_vec(EventData, ~ toString(.x)), EventData = NULL) %>%
pivot_wider()
output
# A tibble: 2 × 11
EventID TimeCreated TCP InterfaceIP Source QNAME QTYPE XID Port Flags
<chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr>
1 259 2024-07-10T15:… 0 0.0.0.0 192.1… "" NA NA NA NA
2 256 2024-07-10T15:… 0 0.0.0.0 192.1… "mic… 0 22342 65220 16979
# ℹ 1 more variable: BufferSize <chr>