data for spreadsheet

dwcasey · August 24, 2012, 11:37am

I'm pulling down some LUN usage data once per day. I store the data in a file name that matches the name of the LUN. Then I just append new usage amounts to the same file each day.

Filename might be serv01_luna, serv01_lunb, serv01_lunc, etc, etc.

Inside the file it would like the following to put into Excel and chart. Sizes may or may not increase:

08/21/2012
tier_capacity 101.62GB
tier generic_hdd
tier_capacity 398.38GB

08/22/2012
tier_capacity 101.62GB
tier generic_hdd
tier_capacity 398.38GB

08/23/2012
tier_capacity 101.62GB
tier generic_hdd
tier_capacity 398.38GB

What I want for Excel is something like

lun            date        sdd sz      hdd sz
serv01_luna, 08/21/2012,101.62GB,398.38GB
serv01_luna, 08/22/2012,101.62GB,398.38GB
serv01_luna, 08/23/2012,101.62GB,398.38GB

serv01_lunb, 08/21/2012,9.75GB,240.25GB
serv01_lunb, 08/22/201210.25GB,239.75GB
serv01_lunb, 08/23/2012101.31GB,239.69GB

I've tried paste, but it didn't give the correct output, way too many commas and they just roll on and on.

DGPickett · August 24, 2012, 5:14pm

It is pretty easy to import data into Excel, by putting it in tab-separated-text or Comma-Separated-Value (csv). Note that the rules for csv include " to hide ',' and even newline inside columns, "" to encode a literal " and lines ending in cr-lf. Old versions of MS Access ignored these rules, so tab-separated is also great unless you need tabs in your columns!

If your data is not going to stay within the span of a worksheet, 256 x 4096 cells, send it to a database. You can still attach the database to Excel with a data fetch query.

There are PERL and other libraries to write code that writes Excel, and the new XML xlsx format must make that a cinch. Then, you can chop big data into many worksheets.

Still, it is nice to tuck it away in a RDBMS in the middle. Many free fine ones are out there, many with ODBC and JDBC access so you can work them with other free tools like IDE plugins and SQuirreL, the universal JDBC RDBMS GUI.

figaro · August 25, 2012, 4:32am

At the expense of coming across as pedantic, isn't the data you are storing going to be used for something else, such as a report or a graph? The data structure example you provide would not seem the most efficient way to build a report or graph off of. I dont even think Excel would be the optimal solution for this.
I concur with DGPickett that your first choice should be to write a script in for example Perl that transforms your data that is better suited for a database and subsequently analyse your data from there. Then Excel is probably not going to be part of the solution.

RudiC · August 25, 2012, 5:44am

Still he/she might want an answer to his/her problem. Try this:

awk '   BEGIN {printf "lun,date,sdd sz,hdd sz"}
        /^$/ {sameline=0; next}
        {if (!sameline || oldfn!=FILENAME) {printf "%s%s", ORS, FILENAME; oldfn=FILENAME; sameline=1}}
        {if ($2=="") printf ",%s", $1};
        /capacity/  {printf ",%s", $2};
        END {print ""}
        ' serv*

DGPickett · August 27, 2012, 8:28am

If you put it into a database daily, there is much less to format. If you format as you collect, in distributed collection scripts, bash could do all the parsing necessary to make each row. A cron on each host could ensure the data is collected same time every day, or even hourly, even if the network or report host is down. The report host could collect the files via ssh, rsync, rsh, ftp or they could NFS to the report host from local on the end host (not the other way around, see prior sentence).

dwcasey · August 27, 2012, 4:16pm

rudic:

Still he/she might want an answer to his/her problem. Try this:

awk '   BEGIN {printf "lun,date,sdd sz,hdd sz"}
   /^$/ {sameline=0; next}
   {if (!sameline || oldfn!=FILENAME) {printf "%s%s", ORS, FILENAME; oldfn=FILENAME; sameline=1}}
   {if ($2=="") printf ",%s", $1};
   /capacity/  {printf ",%s", $2};
   END {print ""}
   ' serv*

Holy smokes that worked perfectly. The last three are where I added the date. Thanks so much.

lun,date,sdd sz,hdd sz
serv01_lun01.out,1.06GB,248.94GB
serv01_lun01.out,1.06GB,248.94GB
serv01_lun01.out,1.06GB,248.94GB
serv01_lun01.out,960.00MB,249.06GB
serv01_lun01.out,08/25/12,960.00MB,249.06GB
serv01_lun01.out,08/26/12,896.00MB,249.12GB
serv01_lun01.out,08/27/12,896.00MB,249.12GB

---------- Post updated at 03:03 PM ---------- Previous update was at 02:56 PM ----------

I have a perl script I use to pull down Cisco switch info and use a perl DB mod to shove it into a DB. Might do that with data as well.

At present, wanted a quick way to put it into Excel, next is to put it on a webpage/dashboard, then I'll look at the DB option.

---------- Post updated at 03:16 PM ---------- Previous update was at 03:03 PM ----------

Now that I'm reading the comments, I see what you guys mean. This actually might be a better way overall to report on the data. Create something like:

serv01_lun01
08/25/2012,100,250
08/26/2012,105,245
08/27/2012,110,240

serv12_lun03
08/25/2012,55,100
08/26/2012,60,95
08/27/2012,60,95

Can the awk script be easily modified to do something like this instead?

Dunno...now that I'm thinking more about DB stuff, wondering how my columns might look. the LUN name will always be the same. The date and sizes are what are changing.

RudiC · August 28, 2012, 4:09am

It certainly can. But - at least for the relational DBs that I know - if the data go into one table, you would need a foreign key in there to reference the server/lun the data belong to. Thus you could use the script as is to populate the DB. If it goes to different tables, you might be able to use your original files.

DGPickett · September 6, 2012, 4:30pm

The usual thing is to stage each file to a staging table, and then move it to the transaction tables using additional parameters.