LinuxQuestions.org
Share your knowledge at the LQ Wiki.
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices


Reply
  Search this Thread
Old 01-19-2017, 11:00 AM   #1
davnetuk
Member
 
Registered: Feb 2003
Location: Great Britain
Distribution: Fedora 5
Posts: 62

Rep: Reputation: 15
Convert filtered Java keytool output into a CSV file


Hi,

I've used ansible to go to a number of servers and export ssl certificate information back to the ansible server so I can piple filtered output into a text file.

There is a requirement to port this information into a csv file (spreadsheet).

A sample text file resembles:

Code:
ok: [server1] => {
        "Alias name: alias1",
        "Valid from: Mon Jan 04 12:47:46 GMT 2016 until: Wed Jan 03 12:47:46 GMT 2018"
ok: [server2] => {
        "Alias name: alias1",
        "Valid from: Tue Jul 12 08:23:02 BST 2016 until: Wed Jul 12 08:23:02 BST 2017",
        "Alias name: ali2",
        "Valid from: Mon Jul 11 14:59:14 BST 2016 until: Tue Jul 11 14:59:14 BST 2017",
        "Alias name: alia3",
        "Valid from: Mon Jul 11 14:42:34 BST 2016 until: Tue Jul 11 14:42:34 BST 2017"
What I need to take the above and convert it into csv format, so servername, alias name, expiry date

server2 would look something like..

server2, alias1, Jul 11 2017
server2, ali2, Jul 11 2017
server2, alia3, Jul 11 2017

server 1 would look something like..
server1, alias1, Jan 03 2018

I am relatively competent with sed/awk etc, however this is currently a little beyond me. I've previously converted data into a comma-separated list via awk -vORS=, '{ print $1}'

The issue here is that I'm stomped on how I can accommodate that some servers may have 1 alias, others may have 5 or more.

Thanks for any help.

Last edited by davnetuk; 01-20-2017 at 05:42 AM. Reason: Added much needed content.
 
Old 01-19-2017, 11:43 AM   #2
grail
LQ Guru
 
Registered: Sep 2009
Location: Perth
Distribution: Manjaro
Posts: 9,529

Rep: Reputation: 2896Reputation: 2896Reputation: 2896Reputation: 2896Reputation: 2896Reputation: 2896Reputation: 2896Reputation: 2896Reputation: 2896Reputation: 2896Reputation: 2896
Please remember to use [code][/code] tags when displaying code or data.

I am curious about the current output as it sort of looks like json data but there does not appear to be any closing curly braces in your example ... is this correct?

Assuming the data is not what I am thinking and the example is correct, show us what you are thinking of when using awk to extract your data?
As a hint, you would need to keep looking for aliases and end dates up until the next 'ok'
 
1 members found this post helpful.
Old 01-19-2017, 12:39 PM   #3
danielbmartin
Senior Member
 
Registered: Apr 2010
Location: Apex, NC, USA
Distribution: Mint 17.3
Posts: 1,575

Rep: Reputation: 478Reputation: 478Reputation: 478Reputation: 478Reputation: 478
With this InFile ...
Code:
ok: [server1] => {
"Alias name: alias1",
"Valid from: Mon Jan 04 12:47:46 GMT 2016 until: Wed Jan 03 12:47:46 GMT 2018"
ok: [server2] => {
"Alias name: alias1",
"Valid from: Tue Jul 12 08:23:02 BST 2016 until: Wed Jul 12 08:23:02 BST 2017",
"Alias name: ali2",
"Valid from: Mon Jul 11 14:59:14 BST 2016 until: Tue Jul 11 14:59:14 BST 2017",
"Alias name: alia3",
"Valid from: Mon Jul 11 14:42:34 BST 2016 until: Tue Jul 11 14:42:34 BST 2017"
... this awk ...
Code:
awk '{gsub(/\"|,/,"")
  if (index($0,"ok:")) {gsub(/.*\[|\].*/,""); serverID=$0}
  if (index($0,"Alias name:")) AliasName=$3
  if (index($0,"until:")) {Expiry=$(NF-4)" "$(NF-3)" "$NF
    print serverID", "AliasName", "Expiry}}'  \
$InFile >$OutFile
... produced this OutFile ...
Code:
server1, alias1, Jan 03 2018
server2, alias1, Jul 12 2017
server2, ali2, Jul 11 2017
server2, alia3, Jul 11 2017
Daniel B. Martin
 
1 members found this post helpful.
Old 01-19-2017, 01:42 PM   #4
grail
LQ Guru
 
Registered: Sep 2009
Location: Perth
Distribution: Manjaro
Posts: 9,529

Rep: Reputation: 2896Reputation: 2896Reputation: 2896Reputation: 2896Reputation: 2896Reputation: 2896Reputation: 2896Reputation: 2896Reputation: 2896Reputation: 2896Reputation: 2896
Normally I would wait until the OP has presented his attempt, but as Daniel has jumped the gun:
Code:
awk '{gsub(/[[:punct:]]/,"")}/ok/{s=$NF}/Alias/{a=s","$NF}/until/{print a","$(NF-4),$(NF-3),$NF}' file
Essentially the same solution.
 
2 members found this post helpful.
Old 01-19-2017, 02:15 PM   #5
danielbmartin
Senior Member
 
Registered: Apr 2010
Location: Apex, NC, USA
Distribution: Mint 17.3
Posts: 1,575

Rep: Reputation: 478Reputation: 478Reputation: 478Reputation: 478Reputation: 478
Quote:
Originally Posted by grail View Post
Code:
awk '{gsub(/[[:punct:]]/,"")}/ok/{s=$NF}/Alias/{a=s","$NF}/until/{print a","$(NF-4),$(NF-3),$NF}' file
Short and sweet. Very nice!

Daniel B. Martin
 
Old 01-20-2017, 05:46 AM   #6
davnetuk
Member
 
Registered: Feb 2003
Location: Great Britain
Distribution: Fedora 5
Posts: 62

Original Poster
Rep: Reputation: 15
grail/danielbmartin - I can't tell you how grateful I am for this. I'm blown away by it and I have to admit, the awk statements blow me away.

I wish I could say I fully understood the awk command there, but certainly I want to understand it. If you have any links/tutorials which explain what you've done there - much appreciated. I've not used gsub/punct previously. Or even if you have the time, take the awk command and break down what that bit is doing.

It works a treat and for others, if you extract verbose keystore output and grep based on alias and valid from lines - the solution here is of fantastic help.

In response to grail - I deliberately filtered out the closing } (its there, but I left it out in the final pass/filter).

Thanks,
David.
 
Old 01-20-2017, 07:50 AM   #7
danielbmartin
Senior Member
 
Registered: Apr 2010
Location: Apex, NC, USA
Distribution: Mint 17.3
Posts: 1,575

Rep: Reputation: 478Reputation: 478Reputation: 478Reputation: 478Reputation: 478
Quote:
Originally Posted by davnetuk View Post
I wish I could say I fully understood the awk command there, but certainly I want to understand it.
I greatly admire the concise solution posted by grail. Here is an expanded version of his work which may be easier to explain.
Code:
awk '{gsub(/[[:punct:]]/,"")}
  /ok /        {serverID=$NF}
  /Alias name/ {AliasName=$3}
  /until /     {Expiry=$(NF-4)" "$(NF-3)" "$NF
   print serverID", "AliasName", "Expiry}'  \
$InFile >$OutFile
{gsub(/[[:punct:]]/,"")}
eliminates all punctuation marks to simplify the following steps.

/ok / {serverID=$NF}
says if the current line contains the character string "ok "
then take the last blank-delimited field and save it in the
variable "serverID".
NF always contains the Number of Fields in the current line,
so $NF is the last field, $(NF-1) is the penultimate field, etc.

/Alias name/ {AliasName=$3}
says if the current line contains the character string "Alias name"
then take the third blank-delimited field and save it in the
variable "AliasName".

/until / {Expiry=$(NF-4)" "$(NF-3)" "$NF
says if the current line contains the character string "until "
then pluck three blank-delimited fields and save them in the
variable "Expiry". The selected fields are the fourth from the last,
the third from the last, and the last.
In the variable Expiry they are separated by blanks.

At this point the stage is set for printing one line to the OutFile.
print serverID", "AliasName", "Expiry

If the test for Server Name is not successful, that's okay, continue to use whatever value is in the variable ServerId.

Daniel B. Martin
 
Old 01-20-2017, 08:54 AM   #8
grail
LQ Guru
 
Registered: Sep 2009
Location: Perth
Distribution: Manjaro
Posts: 9,529

Rep: Reputation: 2896Reputation: 2896Reputation: 2896Reputation: 2896Reputation: 2896Reputation: 2896Reputation: 2896Reputation: 2896Reputation: 2896Reputation: 2896Reputation: 2896
As far as links go, the only one I use is the manual page.

With the closing brace in the data is either json or perhaps a yaml type output. If json then there is a command line tool called jq which I have been told is very useful.

My explanation is obviously much the same as Daniel's, so here are some of the subtle differences:

/Alias/{a=s","$NF} :- Here I append the alias name (last field on the line, hence $NF) after the comma. As these two items are the same for all times, the 'a' variable contains all needed data prior to date

/until/{print a","$(NF-4),$(NF-3),$NF} :- The date fields are in the exact same positions as for Daniel's solution, the subtle difference here is that when you use a comma between items for the print command,
it will automatically place the OFS (Output Field Separator) between each item. As I have not altered it the default OFS is a space.

As for [[:punct:]], this a regular expression term and on the manual page you can see here an explanation and a list of other such constructs.

As a final note, whenever confronted with columned data, awk is my first go to command
 
1 members found this post helpful.
Old 05-31-2017, 11:11 AM   #9
davnetuk
Member
 
Registered: Feb 2003
Location: Great Britain
Distribution: Fedora 5
Posts: 62

Original Poster
Rep: Reputation: 15
Hi,

Thanks for the explanations and apologies its taken me a while to come back to this. I've tried to have another go based on what I've taken in above (I get the usefulness of $NF and the [[unct:]] feature)

I had an issue yesterday, but after a reasonable nights sleep - I've been able to figure this out.

I am now attempting to take a json-like output such as below:
Code:
ok: [server1] => {
        "Alias name: alias1",
        "Valid from: Thu Sep 08 14:51:17 BST 2016 until: Fri Sep 08 14:51:17 BST 2017",
        "  DNSName: server1",
        "  DNSName: server1.mydomain.local",
        "  DNSName: somelburl.mydomain.local",
        "  DNSName: somelburl2.mydomain.local"
    ],
}
ok: [server2] => {
        "Alias name: alias2",
        "Valid from: Mon Sep 12 08:52:22 BST 2016 until: Tue Sep 12 08:52:22 BST 2017",
        "  DNSName: server2",
        "  DNSName: server2.theaa.local",
        "  DNSName: somelburl.mydomain.local",
        "  DNSName: somelburl2.mydomain.local"
    ],
}
and have it so it reads....

Code:
server1,alias1,08,Sep,2017,server1
server1,alias1,08,Sep,2017,server1.mydomain.local
server1,alias1,08,Sep,2017,somelburl2.mydomain.local
server1,alias1,08,Sep,2017,somelburl.mydomain.local
server2,alias2,12,Sep,2017,server2
server2,alias2,12,Sep,2017,server2.mydomain.local
server2,alias2,12,Sep,2017,somelburl2.mydomain.local
server2,alias2,12,Sep,2017,somelburl.mydomain.local
Having had many goes, I eventually achieved the above with command:

Code:
awk '{gsub(/[[:punct:]]/,"")}/ok/{s=($NF)}/Alias/{a=s","$NF}/until/{b=a","$(NF-3)","$(NF-4)","$NF}/DNSName/{print b","$NF}' /tmp/ssl4.txt  | sed 's/mydomainlocal/.mydomain.local/g' | sort
No doubt the final sed to re-introduce the pointers may be inefficient, but it works. I'm sure this could easily be achieved with awk as well.

Thanks again to all of you who helped me understand this - its made a real difference to my day job.

David.

Last edited by davnetuk; 06-01-2017 at 05:05 AM.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
GPSbabel convert from gpx file to csv file Can't get all elements of waypoints pizzipie Linux - General 2 11-23-2015 10:10 AM
Help me convert this xml file to csv xmrkite Linux - Software 2 06-21-2014 09:48 PM
convert log file to CSV ablanco20 Linux - Newbie 3 07-23-2013 02:21 PM
Convert a number on each line to CSV file OlRoy Programming 9 08-20-2008 11:05 AM
Convert into CSV file say_hi_ravi Programming 4 07-17-2008 03:25 AM

LinuxQuestions.org > Forums > Non-*NIX Forums > Programming

All times are GMT -5. The time now is 02:52 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration