Programming for Non-IT related Job Part 2

Okay, after telling you about the reason why you should start to learn computer programming, in this blog, I will tell you the code that I used to do my task in my office. If you have not read the reason why you should start to learn computer programming, you can go to the post here. Okay, let’s start our technical part.

Thinking Process

Looking back at the document we have to process, what is the (human) logic here? I will repost the screenshot of the NIST CSF document.

A snippet of NIST CSF

So, for me, the logic is really simple.

1. We will loop for every subcategory because that is our main concern
2. For every subcategory, we will look at informative references and look for a cell that contains “NIST SP 800–53 Rev. 4”.
3. For several first data, it seems consistent hence, we can skip the “NIST SP 800–53 Rev. 4” and we just take the data after that.
4. We will the selected string by space (‘ ’).
5. We will loop for every separated item, remove the trailing and leading spaces (‘ ’) so just it will be cleaner.
6. We will open the reference document which is the “NIST SP 800–53 Rev. 5” (Use rev 5 instead of 4 because I do not find the rev 4 in the excel file)
7. We will look at the detail of the control by looking for the items in step 5 inside the “Control Identifier” column in the “NIST SP 800–53 Rev. 5” document.
8. Because this is an official document, I suppose that every data we need is available so there will not be any “no reference” error.
9. Combine them all together, and export them into an excel file.

Those steps above were immediately popped at first thought. After more deep analysis, I found additional information that needs to be considered.

1. The rows under the “subcategory” column are merged for every subcategory, It is better to normalize them, hence I unmerged the cell and fill the values to the unmerged cell.
2. There are possibly some additional points for certain controls like CM-8 has 9 more additional points. Because of this, we need additional code to check whether the control has additional points.

That concludes the plan for now. We will build the code. Before, we need to tidy up our excel. What I mean by tidy up is no merged cell and fill all the empty cell because of the unmerging process. You can learn it on here.

Tidied Up NIST CSF Excel

By then, we are ready!

Snipping 1

Snippet 1 above is doing:

1. Line 2. Import the necessary library, in this case, we use the “pandas” library.
2. Line 4–6. We load the excel into a data frame and get only data that starts with “NIST” in their “Informative References” column.
3. Line 8. We initiate an empty array to be a kind of placeholder for our data.
4. Line 9–26. We do a loop. For every subcategory data, we map to NIST SP 800–53 rev 5 controls.
5. Line 12–14. We get the value of the function, category, subcategory. We will use them when we build the excel.
6. Line 16. We get the value of the cell from the “Informative References” column. The value must something like “NIST SP 800–53 Rev. 4 CP-2, IR-4, IR-8”.
7. Line 17. We get the list of controls we need by skipping the first 22 characters (to remove the “NIST SP 800–53 Rev. 4 ”) part and take the rest from it (that is the meaning by [22:]
8. Line 18. We split the string by comma “,” so we can go through each control.
9. Line 20–26. We do another loop. For every control data, we map to each control detail.
10. Line 22. We call the “get_nist800r5_ctrl_detail” function that will be examined quickly. The point is, this function gives us the mapping of a subcategory to the NIST SP 800–53 rev 5 controls.
11. Line 23–25, We append 3 columns (Function, Category, Subcategory) and give it the value we have gotten before (Line 12–14).
12. Line 26, We append into the placeholder we have prepared before (Line 8)
13. Line 28. We convert the container into a data frame (So it can be exported to excel)
14. Line 29. We change the column's name so it is more informative.
15. Line 30. We change the column order.
16. Line 32. We export the final data frame to excel.

Note: If you wonder how did I come with all the syntax? I googled it every line. Of course, I did not copy the whole thing, I wrote it, but the syntax itself, I did look on google.

The second part we will examine is the “get_nist800r5_ctrl_detail” function.

Snippet 2

This looks like a long one but bare with me, it is not that hard. If you look carefully, there are a lot of codes that repeats. So basically, the idea is simple.

1. Line 4. We can ignore this (literally). This value will be used to add some information that every data generated from this function is from “NIST 800–53 rev 5” document.
2. Line 5. We load the value of the “NIST SP 800–53 rev 5” document and put it into a data frame.
3. Line 7. We prepare the placeholder for our data.
4. Line 9. We take the data that contains the exact value of nist800r5_ctrlID that we pass. For example, if we pass a value like “CM-8”, this code will take the only row that the “Control Identifier”’s value is “CM-8”.
5. Line 10–14. We set a default value for several variables (“control_identifier” = the “nist800r5_ctrlID” value, “control_name”, ”control”, ”discussion”, and ”related_controls” to “-”.
6. Line 16. We check, whether the row exists, if it does not, we continue, if it does it continues to next number.
7. Line 18–21. If the row exists, we take the value of control_name, control, discussion, and related_controls from the row we get.
8. Line 23–24. Whether the row exists or not, we transform the control_identifier, control_name, control, discussion, and related_controls variable into a data frame. After that we will add the value to our placeholder.
9. Line 26–41. We do the exact same thing like before but this time, we will get the additional controls. The additional controls have pattern like “control_identifier(“+number+”)”. For example, CM-8(1), CM-8(2), AC-3(8). That is why I try to look for rows that starts with “control_identifier(”, for example “CM-8(”.
10. Line 42. We “return” the value to whoever call this function, in our case, our main function above.

And this is the final excel generated by the program. All has been done in a matter of “seconds :)”

Final Excel

Additional Notes

So I found a little inefficiency in our code (I am so sorry) and I tried to fix it. I fixed it by moving the “NIST SP 800–53 rev 5” file loading process to the top (not inside the “get_nist800r5_ctrl_detail” function, but rather on the main function) because it is so inefficient if we have to reload the file every time we call the function. It makes the program much slower.

The Changes of the Code

Closing

I know it is a hard topic and maybe most of you will not read this message. But just in case you do, congratulation, and I hope my post gives a slight of knowledge about how coding works and how fun it is. I hope you are encouraged to learn computer programming on your own behalf. To GOD be all the glory, Soli Deo Gloria!