Issues to separate a colum

tidyverse
21

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.

22

Ok. I got another report today to compare with the previous one. I applied the same script @andresrcs provided in this thread but I got a different behavior. I generated a new CSV report with cves and ips as shown below:

CVE,IP Address
"CVE-2020-1216,CVE-2020-1315,CVE-2020-1219,CVE-2020-1213,CVE-2020-1214,CVE-2020-1215,CVE-2020-1230,CVE-2020-1260","192.168.100.107"
"CVE-2020-1476,CVE-2020-1046","192.168.100.107"
"CVE-2021-21997","192.168.100.55"
"CVE-2015-0008","192.168.100.55"
"CVE-2018-3620,CVE-2017-5753,CVE-2018-12127,CVE-2017-5754,CVE-2019-11135,CVE-2018-3639,CVE-2018-3615,CVE-2018-12126,CVE-2018-12130,CVE-2018-3646,CVE-2017-5715","192.168.100.55"
"CVE-2021-1727,CVE-2021-24080,CVE-2021-24083,CVE-2021-24094,CVE-2021-25195,CVE-2021-24074,CVE-2021-24086,CVE-2020-1472,CVE-2021-24077,CVE-2021-24088,CVE-2021-1722,CVE-2021-24078,CVE-2021-24103,CVE-2021-1734,CVE-2021-24102","192.168.100.107"
"CVE-2014-3566","192.168.100.107"
"CVE-2020-17052","192.168.100.107"
"CVE-2022-22977","192.168.100.107"
"","192.168.100.107"
"CVE-2020-1091,CVE-2020-0718,CVE-2020-0912,CVE-2020-0836,CVE-2020-1508,CVE-2020-0838,CVE-2020-0921,CVE-2020-1559,CVE-2020-0922,CVE-2020-0761,CVE-2020-1256,CVE-2020-1598,CVE-2020-0664,CVE-2020-1038,CVE-2020-1115,CVE-2020-1039,CVE-2020-1252,CVE-2020-1593,CVE-2020-1012,CVE-2020-0782,CVE-2020-1013,CVE-2020-1376,CVE-2020-1596,CVE-2020-1491,CVE-2020-1030,CVE-2020-1052,CVE-2020-1074,CVE-2020-1250,CVE-2020-1031,CVE-2020-1097,CVE-2020-0648,CVE-2020-1319,CVE-2020-1228,CVE-2020-0856,CVE-2020-0878,CVE-2020-0911,CVE-2020-1245,CVE-2020-1589,CVE-2020-0790,CVE-2020-1285,CVE-2020-1083","192.168.100.107"
"CVE-2021-43222,CVE-2021-43233,CVE-2021-43893,CVE-2021-43223,CVE-2021-43234,CVE-2021-43245,CVE-2021-43883,CVE-2021-43230,CVE-2021-43217,CVE-2021-43207,CVE-2021-43229,CVE-2021-41333,CVE-2021-40441,CVE-2021-43224,CVE-2021-43236,CVE-2021-43215,CVE-2021-43226,CVE-2021-43216,CVE-2021-43238","192.168.100.107"
"CVE-2021-24107,CVE-2021-26869,CVE-2021-26878,CVE-2021-26901,CVE-2021-27063,CVE-2021-27077,CVE-2021-1640,CVE-2021-26861,CVE-2021-26872,CVE-2021-26894,CVE-2021-26862,CVE-2021-26873,CVE-2021-26895,CVE-2021-26411,CVE-2021-26881,CVE-2021-26882,CVE-2021-26893,CVE-2021-26898,CVE-2021-26877,CVE-2021-26899,CVE-2021-26896,CVE-2021-26875,CVE-2021-26897","192.168.100.107"
"","192.168.100.107"
"CVE-2015-6161","192.168.100.55"

But this time I got this results:

vuln <- read.csv("vulnerabilities.csv", header = FALSE)

vuln %>%

  • separate_rows(V1, sep = ",(?=CVE)") %>%
  • separate(V1, into = c("CVE", "IP"), sep = ",") %>%
  • fill(IP, .direction = "up")

A tibble: 43,916 × 3

CVE IP V2

1 CVE NA IP Address
2 CVE-2020-1216 NA 192.168.100.107
3 CVE-2020-1315 NA 192.168.100.107
4 CVE-2020-1219 NA 192.168.100.107
5 CVE-2020-1213 NA 192.168.100.107
6 CVE-2020-1214 NA 192.168.100.107
7 CVE-2020-1215 NA 192.168.100.107
8 CVE-2020-1230 NA 192.168.100.107
9 CVE-2020-1260 NA 192.168.100.107
10 CVE-2020-1476 NA 192.168.100.107

… with 43,906 more rows

Warning message:
Expected 2 pieces. Missing pieces filled with NA in 43916 rows [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, ...].

image
image803×324 13 KB

Honestly, I don't understand why. Or maybe, the tool just generated a true csv this time?

Thanks again.

23

Yes, the sample csv you are showing has a different structure than the first one, this time it has headers and it has two columns, but you still have several CVE values pasted together so if you want a similar output as before simple do the separate_rows() step.

library(tidyverse)

sample_df <- read.csv("sample.txt")

sample_df %>% 
    separate_rows(CVE, sep = ",(?=CVE)")
#> # A tibble: 127 × 2
#>    CVE           IP.Address     
#>    <chr>         <chr>          
#>  1 CVE-2020-1216 192.168.100.107
#>  2 CVE-2020-1315 192.168.100.107
#>  3 CVE-2020-1219 192.168.100.107
#>  4 CVE-2020-1213 192.168.100.107
#>  5 CVE-2020-1214 192.168.100.107
#>  6 CVE-2020-1215 192.168.100.107
#>  7 CVE-2020-1230 192.168.100.107
#>  8 CVE-2020-1260 192.168.100.107
#>  9 CVE-2020-1476 192.168.100.107
#> 10 CVE-2020-1046 192.168.100.107
#> # … with 117 more rows

Created on 2022-06-28 by the reprex package (v2.0.1)

1 Like