Page 1 of 2 1 2 LastLast
Results 1 to 10 of 12

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

  1. #1

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    G'evening

    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

    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 ().
    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 [IMG]file:///C:\Users\mos\AppData\Local\Temp\msohtmlclip1\01\clip_image001.gif[/IMG]

    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'.

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,225
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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?
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,225
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  4. #4
    Thank you very much for replying, Ken

    I still can't upload, I must be stupido

    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 ) 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

  5. #5
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,225
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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?
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  6. #6
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,225
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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?
    Attached Files Attached Files
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  7. #7
    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

    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

    Thank you again very much, Ken :-)
    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	2013-07-13_131019.jpg 
Views:	8 
Size:	104.9 KB 
ID:	1490   Click image for larger version. 

Name:	2013-07-13_131049.jpg 
Views:	9 
Size:	81.4 KB 
ID:	1491  

  8. #8
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,225
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  9. #9
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,225
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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.
    Attached Files Attached Files
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  10. #10
    Dear Ken

    Thank you very, very, much, this did something

    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 ).

    Thank you again very, very, much, Ken, I am in your debt

Page 1 of 2 1 2 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •