Okay, I have been trying my hardest to get this to split out but have no idea what i am doing. I do apologize as I don't have the greatest reproducible example.
This column still has several other items within it that need to be split out into columns with the original dataset.
# SCRIPT for Tenable Connection and Retrieval
headers = c('x-apikey' = 'accesskey=redacted; secretkey=redacted', 'type' = 'vuln')
#connection string, change parameters as needed. This points to 'All NMHS Systems'.
tenable <- httr::POST(url='https://scnessus.redacted.org/rest/analysis?sourceType=cumulative&type=vuln&query[id]=6075',
httr::add_headers(.headers=headers), config = httr::config(ssl_verifypeer = FALSE))
#convert from raw text, to JSON, then Data.Frame
tenable_text <- httr::content(tenable, as = "text")
tenable_json <- jsonlite::fromJSON (tenable_text, flatten = TRUE)
tenable_json <- lapply(tenable_json$response$results, function(x) {
x[sapply(x, is.null)] <- NA
unlist(x)
})
tenable.df <- as.data.frame(tenable_json)
Since we can't get to the source data (we don't have the API key and you should not share it), why don't you share a part of the JSON file that then needs to be converted into a data frame. Use the reprex guide to help you share the data more easily, or even just copy paste a sample JSON input here.
A reprex consists of the minimal code and data needed to recreate the issue/question you're having. You can find instructions how to build and share one here:
You did not provide the original JSON (tenable_json), so I worked from the data frame you already have after first changes (tenable.df) by recreating it:
library(tidyverse)
library(jsonlite)
#Example of a JSON string
string = '[{"id":"age_of_vuln","name":"Vulnerability Age",
"type":"string","value":"730 days +"},
{"id":"cvssV3_impactScore","name":"CVSS v3 Impact Score"}]'
#Generate data frame as input
myData = tibble(
pluginID = 1:5, name = paste0("test", 1:5),
vprContext = c("[]", "[]", string,"[]", string)
)
myData
#> # A tibble: 5 x 3
#> pluginID name vprContext
#> <int> <chr> <chr>
#> 1 1 test1 "[]"
#> 2 2 test2 "[]"
#> 3 3 test3 "[{\"id\":\"age_of_vuln\",\"name\":\"Vulnerability Age\",\n\"t~
#> 4 4 test4 "[]"
#> 5 5 test5 "[{\"id\":\"age_of_vuln\",\"name\":\"Vulnerability Age\",\n\"t~
#COnvert the json part to a new data frame
vprDF = apply(myData, 1, function(x){
y = fromJSON(x["vprContext"])
y$pluginID = x["pluginID"]
y
})
vprDF = bind_rows(vprDF, row.names = NULL) %>%
mutate(pluginID = as.integer(pluginID))
vprDF
#> # A tibble: 7 x 5
#> pluginID id name type value
#> <int> <chr> <chr> <chr> <chr>
#> 1 1 <NA> <NA> <NA> <NA>
#> 2 2 <NA> <NA> <NA> <NA>
#> 3 3 age_of_vuln Vulnerability Age string 730 days +
#> 4 3 cvssV3_impactScore CVSS v3 Impact Score <NA> <NA>
#> 5 4 <NA> <NA> <NA> <NA>
#> 6 5 age_of_vuln Vulnerability Age string 730 days +
#> 7 5 cvssV3_impactScore CVSS v3 Impact Score <NA> <NA>
#Join it with rest of the data
myData = myData %>% select(-vprContext) %>%
left_join(vprDF, by = "pluginID")
myData
#> # A tibble: 7 x 6
#> pluginID name.x id name.y type value
#> <int> <chr> <chr> <chr> <chr> <chr>
#> 1 1 test1 <NA> <NA> <NA> <NA>
#> 2 2 test2 <NA> <NA> <NA> <NA>
#> 3 3 test3 age_of_vuln Vulnerability Age string 730 days +
#> 4 3 test3 cvssV3_impactScore CVSS v3 Impact Score <NA> <NA>
#> 5 4 test4 <NA> <NA> <NA> <NA>
#> 6 5 test5 age_of_vuln Vulnerability Age string 730 days +
#> 7 5 test5 cvssV3_impactScore CVSS v3 Impact Score <NA> <NA>
I'm guessing you arent reporting an error with the same data going in as has been shared here so far ?
because again I have to say, I copy and paste Pieters code top to bottom, and it produces results without errors.
To help us help you, could you please prepare a reproducible example (reprex) illustrating your issue? Please have a look at this guide, to see how to create one:
