Combining daily S.M.A.R.T data in a meaningfull sheet (2 problems)

V

vitolio

New member
Joined
Jul 9, 2013
Messages
5
Reaction score
0
Points
0
G'evening :clap2:

I have breaking my head for days over a problem I am having, and I was hoping somebody who has mercy with a noob like me would help me out. I will be in debt for this, and I always pay off my debts whenever I can :tongue1:

My situation is this:

  1. I have a NAS (Network Attached Storage, which is a file server within my LAN). Within it are 8 disks. They are giving me problems. I need to monitor these disks.
  2. The NAS is based on Linux, and there is a tool for monitoring hard disk Self Monitoring Analysis and Reporting (hence: S.M.A.R.T, more information here:: EDIT: I am not allowed to post a link here since I am new, so please google for 'hard disk smart wikipedia', it will get you the link on the top of the page that I am not allowed to post here).
  3. What I want to do is run smartctl -a (the command that checks the SMART data), daily, for a week, for each of the 8 disks. I will do this daily, manually. I want to see if the SMART-characteristics increase over time. (And with SMART-characteristics I mean the values of the "Known SMART characteristics', the lines in the table in the wiki article, for example the 0x01, 0x02, and so on).
  4. So, as you can see, there are some 60 SMART parameters. Given 8 disks and 7 days that means 60x8x7 = 3360 values over one week.

I have attached an example SMART-report that the SMART-tool will output (again, I will run that report manually, and so I will have 8 reports every day, 56 reports after one week, each report giving me the values of about 60 parameters for that day). As you can see, it does not contain all the parameters that the wikipedia article lists, but less. I am only interested in the serial number (WMAY) for indentification, and then next the SMART values that it does list (the content in the middle of the document, so from ID# 1 to ID# 200).

My problems are twofold:

  1. Is there an easy way to get this data into Excel? I don't want to type it in by hand, because then I would have to quit my day time job and can't feed my wife and children anymore :)shocked:).
  2. How would one construct a meaningful Excelsheet out of this, which can be used to easily monitor the evolution of each disk over time (in a graph)? As I get stuck at: Excel is 2 dimensions (rows x columns), yet I have three (disks, days, SMART-parameters)?
I am stuck at this for days, and I was hoping somebody would be so kind to help me out. Again, I would be in your debt for this, and I always intend to pay my debts
C:\Users\mos\AppData\Local\Temp\msohtmlclip1\01\clip_image001.gif


Thank you in advance for your reply,

Bye,

EDIT: I am trying to find out how to attach a file. When I attached it and clicked 'done' it told me to 'attach a file'.
 
To distill this down, you have reports that you want to import into Excel, and you're looking for help in building a meaningful report out of them? Is that correct?

We can import data using VBA, no issue there. I assume the reports will always be in the same format, so that should be easy. As far as building useful reports though... the challenge we have here is that you know what you need to see. We can try, but you're going to need to do some work on that front.

My suggestion would be to pull your data into a table, then build a PivotTable off it, and maybe some PivotCharts. Question though... what version of Excel are you using?
 
Oh, and to upload the file, it sounds like you were part way there. You do need to select the file and then click the upload button in order to make it work.
 
Thank you very much for replying, Ken :pray2:

I still can't upload, I must be stupido:D

I have uploaded the sample log to pastebin.com, but I am not allowed to post an url here, so I'll cheat (as I am not a spammer :thumb:) p a st e b i n . c o m / yB45KEcg (there's a space after .com/ that needs to be removed.

As I tried to explain, the only data relevant in this log would be:

The disk serial number:
Code: 
WD-WMAV02649841

And then this data (so, this is per disc):

Code: 
ID# ATTRIBUTE_NAME          FLAG     VALUE WORST THRESH TYPE      UPDATED  WHEN_FAILED RAW_VALUE
  1 Raw_Read_Error_Rate     0x002f   200   200   051    Pre-fail  Always       -       0
  3 Spin_Up_Time            0x0027   253   253   021    Pre-fail  Always       -       8500
  4 Start_Stop_Count        0x0032   100   100   000    Old_age   Always       -       249
  5 Reallocated_Sector_Ct   0x0033   200   200   140    Pre-fail  Always       -       0
  7 Seek_Error_Rate         0x002e   100   253   000    Old_age   Always       -       0
  9 Power_On_Hours          0x0032   096   096   000    Old_age   Always       -       3390
 10 Spin_Retry_Count        0x0032   100   100   000    Old_age   Always       -       0
 11 Calibration_Retry_Count 0x0032   100   253   000    Old_age   Always       -       0
 12 Power_Cycle_Count       0x0032   100   100   000    Old_age   Always       -       55
192 Power-Off_Retract_Count 0x0032   200   200   000    Old_age   Always       -       4
193 Load_Cycle_Count        0x0032   200   200   000    Old_age   Always       -       244
194 Temperature_Celsius     0x0022   124   099   000    Old_age   Always       -       28
196 Reallocated_Event_Count 0x0032   200   200   000    Old_age   Always       -       0
197 Current_Pending_Sector  0x0032   200   200   000    Old_age   Always       -       0
198 Offline_Uncorrectable   0x0030   200   200   000    Old_age   Offline      -       0
199 UDMA_CRC_Error_Count    0x0032   200   200   000    Old_age   Always       -       7
200 Multi_Zone_Error_Rate   0x0008   200   200   000    Old_age   Offline      -       1

I am using Excel 2010/X32 on W7/X64.

I would really appreciate extremely very much your help :)

Thank you & bye :pray2:
 
Hi Votolio,

So a question here... the output that comes out is stored in a txt file, I assume? I'm also going to guess that the format is the same each and every time, and that the number of lines of data never changes?

Curious if this scenario would work:
-You dump your reports into a specific directory
-We have some code you trigger to import each file in that directory
-Once the file is imported, it is deleted (since the data is in the workbook)

Does that sound like it will work?
 
So, this file doesn't delete the imported files from the directory, but as long as everything is in the same format, I think it should get you started.

To use it:
  • Change the path to the folder where you dump in the text files in B2 (and don't forget the trailing slash).
  • Click the button.

That should append all data from all files into the table you see. Next you select the "Pivot" worksheet, right click the PivotTable and click refresh.

Once we've confirmed that your data is being imported, we can work on the pivottable/pivotchart part. But I'd like you to upload a workbook, so we're going to need to get that working. Can you Private Message me your email address? :)
 

Attachments

  • SMART Tracker.xlsm
    33 KB · Views: 16
Hi Ken,

I was extremely excited when I just saw that you've been so kind to create something for me; thank you, thank you, thank you, very much :tea:

I tried your file, but it appears it doesn't take the files. I created a directory e:\1000\, put the smart logs in there as well as your file, enabled all macros allowed (trust center) in Excel2010, but nothing appears to be happening? I am sure I did something wrong, but what :redface:

Thank you again very much, Ken :)
 

Attachments

  • 2013-07-13_131019.jpg
    2013-07-13_131019.jpg
    104.9 KB · Views: 17
  • 2013-07-13_131049.jpg
    2013-07-13_131049.jpg
    81.4 KB · Views: 14
Can you email me one of those files? Ken at this website .ca

I'm guessing that maybe the file extension is actually .log not .txt but since you have file extensions hidden its hard to see on your screen.
 
This version has been reformatted to work slightly differently based on the line breaks in the file you emailed. Give it a go and let me know. (Don't forget to update the file path in B2). It still doesn't wipe out old files though, so you'll need to remove them manually at the moment. But let's be sure it works before we deal with that part.
 

Attachments

  • SMART Tracker.xlsm
    32.7 KB · Views: 16
Dear Ken :israel:

Thank you very, very, much, this did something :dance:

I tested the sheet, it does import the file (after you change the directory to the right one *and* click 'save' the sheet first, before that it refused to import anything). So I added the logs from the other disks (there are 8 disks, so 8 logs) to the directory, but it didn't import the remaining 7 logs (they are called all the same, but ending in: sda to sdh (sda, sdb, sdc...sdh). It did however re-import the already existing log, so the data is in there in duplicate.

Also, the 'date' column says 17.06.2013, but the date of the log file is 13.07.2013, and I also couldn't find this 17.06.2013 in the content of the log file itself. Would you happen to know where the 17.06.2013 comes from? Ideally it will import the right date, as I will be running the logs once a day, so every day, I will 8 logs from that date, and the next day there will be 8 new logs from date+1. If I have all the SMART-data from every disk per day, I can then run an analysis per smart-characteristic per disk over time, to see if my disks are in danger (which I really need to monitor since some extremely valuable personal memories of my wife, her family, and our animals, are stored there :grouphug:).

Thank you again very, very, much, Ken, I am in your debt :yo:
 
Hi there,

First thing... if your files are in danger, back them up immediately. You don't want to risk them by just watching them die. ;)

Now, I've updated the workbook with a couple of changes:
  1. The routine will now work to pull in all files in the folder.
  2. The routine will delete all of the source files (so make a backup of them the first time, just in case you want to throw it away.)
  3. Every PivotTable in the workbook will be updated when you change worksheets

The dates in the file I was working with were contained in the line:
Local Time is: Mon Jul 1 12:00:44 2013 CEST

They extracted correctly to 2013-07-01 on my side here.

From here, the next piece of the puzzle is to build the reports you want. If you select any cell in B6:B8, you'll see that a PivotTable fields list comes up on the right. Just drag and drop fields from the top into the row/column and value areas in the bottom right, and you should be able to build a table that shows info you're looking for.

Let me know how you make out with this, and if there is more info you need.

Cheers,
 

Attachments

  • SMART Tracker.xlsm
    34.4 KB · Views: 9
Dear Ken :violin:

Thank you, again, very, very, much :kiss:

It appears to be working nicely, it now takes all the records. I will test for a couple of days, and then I can report back if it works --- but I completely trust it will. This, you know: :tea:

And something else, but I'll send you a personal message about that :D

Thank you again & bye,
 
You must log in or register to reply here.
Back
Top