0

Trying to parse a text file into CSV. Catch is that I have multiple delimiters currently, that I'd ideally like to use as column headers but can strip out of the csv results. Ideally would rather use bash, but whatever works... running this on a Mac OS system.

Sample text (DISA STIG)


 ----------
Group ID (Vulid): V-81749
Group Title: SRG-OS-000067-GPOS-00035
Rule ID: SV-96463r1_rule
Severity: CAT II
Rule Version (STIG-ID): AOSX-13-067035
Rule Title: The macOS system must enable certificate for smartcards.
_
_

 Vulnerability Discussion: To prevent untrusted certificates the certificates on a smartcard card must be valid in these ways: its issuer is system-trusted, the certificate is not expired, its "valid-after" date is in the past, and it passes CRL and OCSP checking.
Check Content:
To view the setting for the smartcard certification configuration, run the following command:
sudo /usr/sbin/system_profiler SPConfigurationProfileDataType | /usr/bin/grep checkCertificateTrust
If the output is null or not "checkCertificateTrust = 1;" this is a finding.
Fix Text: This setting is enforced using the "Smartcard" configuration profile.
CCI: CCI-000186 ___________________________________________________
<Break>

----------

Basically, I want to break the CSV for the following columns:

Group ID (Vulid)
Group Title:
Rule ID:
Severity:
Rule Version (STIG-ID):
Rule Title:
Vulnerability Discussion:
Check Content:
Fix Text:
CCI:

The <Break> delimiter will go to the next row.

I'd like to end up with something like this for my columns:

    Group ID (Vulid)    Group Title:    Rule ID:    Severity:   Rule Version (STIG-ID)  Rule Title: Vulnerability Discussion    Check Content   Fix Text:   CCI:    CCI:

Would the best approach be to strip each of these headers out, replacing with an arbitrary delimiter, and then use awk to split there? Never tried splitting with multiple criteria like this, so just a little stumped on how to best approach it.

3
  • Maybe split by one delimiter, treating multiple logical fields as one field. Then, split that field by the other delimiter. Lastly write out the whole record.
    – RonJohn
    Commented Mar 12, 2019 at 18:53
  • Appreciate it... was wondering if something like that made the most sense, "nesting" it is something I think I can figure out - will try tonight if I have time. Appreciate it!
    – WTA
    Commented Mar 12, 2019 at 20:38
  • 1
    please edit your question to put the source data in a code block, and to show what the corresponding output should be. See the editing help for that first one.
    – ilkkachu
    Commented Mar 12, 2019 at 21:06

2 Answers 2

1

Answer

First, you need to clean the files and make it look like uniform, like the postscripted 505942.txt. I leave this job for you since you only know the original files and their intricacies and you can easily do it googling for simple sed commands. Notice that you might have to write specific sed commands for some lines which deviate from the norm or do the changes manually if they aren't too much trouble (e.g. I wouldn't write a 5-6 lines script for a simple character deletion).

When working with strings, you need to separate the job to simple tasks. I give an example of how to transform your given file into a comma separated value file (CSV). The final file is 505942.csv which I also postscript.

sed -i 's/^.*\(: \)/\1/g' 505942.txt # Use '-i' for editing files in place (in the file itself). replace everything until the first colon ':' excluding, in other words, remove the headers from each line.
sed -i 's/^\(: \)//g' 505942.txt # Remove the first colon and the subsequent white space.
sed -i 's/^/"/' 505942.txt # Add double quotes in the beginning of each line. Quotes whill help you to parse the final comma seperated value file, since some of the fields seem to already contain commas.
sed -i 's/$/",/' 505942.txt # Add double quotes in the end of each line.
cat 505942.txt | xargs -n10 -d'\n' > 505942-after-xargs.txt # Join every 10 lines of the file.
sed -i 's/,$//' 505942-after-xargs.txt # Remove the last comma from each line.

sed -n 1,10p 505942.txt > 505942-headers.txt # Keep the first 10 lines from which you will extract the headers.
sed -i 's/:.*//' 505942-headers.txt # Remove everything after (including) the first colon.
sed -i 's/^/"/' 505942-headers.txt # Similar to above command.
sed -i 's/$/",/' 505942-headers.txt # Similar to above command.
cat 505942-headers.txt | xargs -n10 -d'\n' > 505942-headers-after-xargs.txt # Similar to above command.
sed -i 's/,$//' 505942-headers-after-xargs.txt # Similar to above command.

cat 505942-after-xargs.txt >> 505942-headers-after-xargs.txt # Join the files; append to the header file.

cat 505942-headers-after-xargs.txt # Check everything seems fine.
cp 505942-headers-after-xargs.txt 505942.csv # Copy to the final .csv file.

Postscripts

Contents of 505942.txt:

Group Title: SRG-OS-000067-GPOS-00035
Rule ID: SV-96463r1_rule
Severity: CAT II
Rule Version (STIG-ID): AOSX-13-067035
Rule Title: The macOS system must enable certificate for smartcards.
Vulnerability Discussion: To prevent untrusted certificates the certificates on a smartcard card must be valid in these ways: its issuer is system-trusted, the certificate is not expired, its "valid-after" date is in the past, and it passes CRL and OCSP checking.
Check Content: To view the setting for the smartcard certification configuration, run the following command: sudo /usr/sbin/system_profiler SPConfigurationProfileDataType | /usr/bin/grep checkCertificateTrust If the output is null or not "checkCertificateTrust = 1;" this is a finding.
Fix Text: This setting is enforced using the "Smartcard" configuration profile.
CCI: CCI-000186
Group ID (Vulid): V-81749
Group Title: SRG-OS-000067-GPOS-00035
Rule ID: SV-96463r1_rule
Severity: CAT II
Rule Version (STIG-ID): AOSX-13-067035
Rule Title: The macOS system must enable certificate for smartcards.
Vulnerability Discussion: To prevent untrusted certificates the certificates on a smartcard card must be valid in these ways: its issuer is system-trusted, the certificate is not expired, its "valid-after" date is in the past, and it passes CRL and OCSP checking.
Check Content: To view the setting for the smartcard certification configuration, run the following command: sudo /usr/sbin/system_profiler SPConfigurationProfileDataType | /usr/bin/grep checkCertificateTrust If the output is null or not "checkCertificateTrust = 1;" this is a finding.
Fix Text: This setting is enforced using the "Smartcard" configuration profile.
CCI: CCI-000186

Contents of 505942.csv:

"Group ID (Vulid)", "Group Title", "Rule ID", "Severity", "Rule Version (STIG-ID)", "Rule Title", "Vulnerability Discussion", "Check Content", "Fix Text", "CCI"
"V-81749", "SRG-OS-000067-GPOS-00035", "SV-96463r1_rule", "CAT II", "AOSX-13-067035", "The macOS system must enable certificate for smartcards.", "its issuer is system-trusted, the certificate is not expired, its "valid-after" date is in the past, and it passes CRL and OCSP checking.", "sudo /usr/sbin/system_profiler SPConfigurationProfileDataType | /usr/bin/grep checkCertificateTrust If the output is null or not "checkCertificateTrust = 1;" this is a finding.", "This setting is enforced using the "Smartcard" configuration profile.", "CCI-000186"
"V-81749", "SRG-OS-000067-GPOS-00035", "SV-96463r1_rule", "CAT II", "AOSX-13-067035", "The macOS system must enable certificate for smartcards.", "its issuer is system-trusted, the certificate is not expired, its "valid-after" date is in the past, and it passes CRL and OCSP checking.", "sudo /usr/sbin/system_profiler SPConfigurationProfileDataType | /usr/bin/grep checkCertificateTrust If the output is null or not "checkCertificateTrust = 1;" this is a finding.", "This setting is enforced using the "Smartcard" configuration profile.", "CCI-000186"
2
  • 1
    sed expression file; sed expression file can be combined into sed 'expression;expression' file. You don't have to read the file 15 times to do this.
    – Kusalananda
    Commented Mar 13, 2019 at 20:39
  • @Kusalananda True, and I could split it in multiple lines to keep answer's tutoring nature. Thanks! Commented Mar 14, 2019 at 3:28
-1

Split each by one delimiter, treating multiple logical fields as one field. Then, split that field by the other delimiter. Lastly write out the whole record.

It's probably not a "pure" solution, but this is bash...

5
  • Sorry, what is bash?
    – Kusalananda
    Commented Mar 13, 2019 at 16:39
  • @Kusalananda it's the standard shell on Linux machines.
    – RonJohn
    Commented Mar 13, 2019 at 16:41
  • Downvoter, can you tell me why, so that I can improve my answer?
    – RonJohn
    Commented Mar 13, 2019 at 16:42
  • That was me. Sorry, I did not mean to ask what bash was. I was asking what you meant by but this is bash.... The answer contains no code, just a description of a way of possibly going about solving the issue. This is why I wondered what this was that you referred to. The procedure that you wrote could be implemented by any standard Unix text-processing tool.
    – Kusalananda
    Commented Mar 13, 2019 at 16:50
  • @Kusalananda #1 bash is not a programming language optimized for Programming The Right Way. It's for one-off hacks. #2 When i wrote my answer, the question didn't have all the specifics it does now.
    – RonJohn
    Commented Mar 13, 2019 at 17:04

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .