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.

  • 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 Mar 12 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 Mar 12 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 Mar 12 at 21:06
0

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"
  • 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 Mar 13 at 20:39
  • @Kusalananda True, and I could split it in multiple lines to keep answer's tutoring nature. Thanks! – Konstantinos Mar 14 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...

  • Sorry, what is bash? – Kusalananda Mar 13 at 16:39
  • @Kusalananda it's the standard shell on Linux machines. – RonJohn Mar 13 at 16:41
  • Downvoter, can you tell me why, so that I can improve my answer? – RonJohn Mar 13 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 Mar 13 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 Mar 13 at 17:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.