[Template] – Meeting Minute Taker in Excel

Slicers are a pretty cool filtering feature available in Excel 2010 and above. In a few words, slicers help you filter a table (pivot or otherwise) by simply clicking on the items you wish to see. You can create multiple slicers for a table, letting you play with data in ways which was not possible with simple Filters. Using filters was often cumbersome since you had to scroll left & right to find the right columns. Data available for filtering would be restricted to available entries from previous filters and often left you wondering which column was filtered causing your table to show partial data.

If I have you hooked on slicers, head over to the post at office.com and be sure to come back here for the meeting minutes template.

Meeting Minutes Template

The template consists of two sheets – Meeting Minutes and Team List. Some data has been pre-populated so that you can see how the slicer works. This data can be safely deleted (Select cells & hit delete, don’t delete entire rows). First, head over to the Team List sheet and enter your team members in the table. As you add more data, you will notice that the table automatically expands and you can see the alternate shading. On the Meeting Minutes sheet, you might want to replace The Science of Deduction with your own team name. Begin entering data in the table below the slicers and you are good to go.

Notes

Here are some additional points you will want to consider

  • The drop-down for Type and Status is from Data Validation. Feel free to change the list, by selecting the column in the table and modifiying the Data Validation Settings.
  • You can add more columns to the Meeting Minutes/Team List. If you add columns after the last column, make sure you the table has expanded to cover the new columns. Excel will automatically do this for you, however, if it doesn’t happen you need to pull the small blue inverted L shape on the last cell on the last row of the table
  • New slicers can be inserted easily. Select an cell within the table, a new tab should be visible in the Office Ribbon called Design (under Table Tools). Click on Insert Slicer and follow the simple instructions on screen
  • You can switch between sheets using the buttons on the left side of the sheet.

Download

Meeting Minute Tracker
Meeting Minute Tracker
Meeting_Minute_Tracker.xltx
21.9 KiB
438 Downloads
Details

Reset restrictions passcode on iPad/iPhone

A friend of mine had setup the restrictions (parental controls) passcode on his iPad and over time had forgotten what it was. His initial internet search brought him to one conclusion – factory reset the device and lose all data. (Even if he took a backup of his iPad now and restored it, the restrictions settings would be restored and he would be back to where he started).

iOS restrictions screen

One post on apple’s support forum mentioned that the settings could be edited from a backup, so I decided to search more on those lines. I came across a video from iDeviceBlogMovies which gave instructions for editing backups. The video refers to outdated iOS, so I decided to write my experience (updated to iOS 8)

You need not Jailbreak your device. This method works fine for all devices.

Remove restrictions Passcode on your iOS device

Built upon from the original youtube video

Step 1: Backup your device: Connect your device to iTunes and manually create a backup (Device > Summary)

iTunes Manual Backup

Step 2: Download iBackupBot: From the icopybot site download the iBackupbot program for either Windows or Mac OS based on your computer’s operating system. It is a trial version, but all features work fine.

Download links for iBackupBot

Step 3: Run iBackupBot: Run the iBackupBot application. You will see a list of available backups in the left portion of the application window.

iBackupBot List of Backups

Step 4: Take a backup of your backup: One can never be too careful these days, so right click on the latest backup and click on Duplicate. Select the target folder to store your backup and let the application complete the duplication process

iBackupBot - Duplicate a backup

Step 5: Navigate to System Files > Home Domain > Library > Preferences: Once you select preferences, the right window pane will be populated with a list of files. The actual list of files varies from device to device, but the file we are looking for is com.apple.springboard.plist.

iBackupbot - Backup structure

Step 6: Editing the com.apple.springboard.plist file: Double click the com.apple.springboard.plist file. Press cancel when it gives a message that you are using the trial version and you need to register. In the XML file, search for SBParentalControlsMCContentRestrictions. Below this <key>, you will have a <dict> and corresponding </dict> tags.

iBackupbot - Parental Controls XML

Press enter after the line with </dict> and add the following two lines

   <key>SBParentalControlsPIN</key>
   <string>1111</string>

What you have done is set the Restrictions Passcode to 1111.

Step 7: Save & Restore: Save the file (Ctrl+S works). Right click the com.apple.springboard.plist file and choose Restore selected item(s) to device. The application will rebuilt the backup and restore it to your device. The step takes a few minutes, so be patient.

iBackupbot - Restore selected item

Step 8: Finished: Your iPad will restart and you can go to the restrictions settings and enter the passcode 1111 to manage restrictions or disable the restrictions passcode.

PS: If things don’t got as intended, restore the duplicate of the backup (from iTunes/iBackupbot) and try again. It could be an error while editing the xml. Please make sure you have sufficient charge on your iDevice before attempting this.

Brute Force algorithm for numeric password protected archives

Long time ago, I stored some documents in a zip file and for good measure password protected it so that no one would be able to extract it. While sounding like a good plan at that time, I now have no clue as to what the password was. The only thing I am sure of is that it is a 6 digit numeric code.

The market is full of tools that let you open a zip file after you have forgotten the password. However, after my recent “won-the-battle-lost-the-war” attempt to convert a unix shell script for Android APK creation into windows, I was keen on writing a shell script that would do what I wanted.

What you need

  1. 7-zip (any version, even the portable one would do)
  2. Notepad (or any other text editor)

Let’s code

Find out the path to the 7zip compression program. If it is installed it should be in your Program Files directory. The executable for the portable version will be within the App/7-Zip directory (App/7-Zip64, if you are running on a 64-bit computer). Note that we need the command line version of 7zip and not the GUI version, though it appears that both would be in the same directory. For the sake of simplicity, copy the zip file to the same folder as your 7z.exe file. Fire up your text editor and enter the following code.

@echo off
rem Brute Force script to extract documents from password protected archive
 
for /l %%X in (100000, 1, 999999) do (
    echo "Processing.. " %%X
    7z e docs.zip -p%%X -o.\%%X
)

Code Explained

set enableDelayedExpansion allows variables to be expanded at runtime instead of at the time of parsing.
the for loop increments the variable X from 100000 to 999999 by 1 in each iteration. /l option indicates that the loop is with a list of numbers
echo is pretty much self explanatory, so onto 7z. The parameter e is for extract, -p is for inputting the password so -p%%X will get expanded as -p100000 for the first iteration
-o is used for specifying the output directory. Each instance of 7zip creates a zero byte file when the password is incorrect. To prevent the batch program from prompting for file overwrite everytime, I will extract to a new folder identified by the password. The folder that finally has the actual content will also be the password that I need (two solutions in one shot).

Iteration 2

As the script started and had created about 1000 folders, I cancelled it. Why? Because (a) 7zip was trying to extract all files (about 500 of them) in each iteration and (b) it would be as difficult to find the folder with the data as it was to manually try each password in 7zip. In the next iteration, I decided that I would try to extract only 1 file (let’s say notes.txt) and instead of putting it in a new directory for every loop, I would rename the file to the password being attempted.

At the end of the script, I can sort by size in the same folder and pick up the password and unzip all the required data.

@echo off
rem Brute Force script to extract documents from password protected archive
 
for /l %%X in (100000, 1, 999999) do (
    echo "Processing.. " %%X
    7z e docs.zip notes.txt -p%%X -o.\output
    cd output
    ren notes.txt %%X.txt
    cd ..
)

I redirected my extract: notes.txt to the output subfolder and renamed it to the current password being attempted. I had trouble getting rename to work from the parent folder, so the lines cd output and cd .. was required.

Continuous improvement

While the script was running, I checked the Windows task manager to see how much CPU intensive the script was. It stayed around 1% for the most and didn’t consume more than 1500K of memory. I decided to do some parallel processing. I created 10 batch scripts each running for 100K possibilities. Since 7zip would try to extract the file with the same name into the same output folder in each “thread”, you can either separate the output folders (as I did) or let each thread try and extract a different file from your zip. On hindsight, the second option would work better (assuming similar files being extracted) since you could sort on size in the output folder and the moment you have a non-zero file created, you can terminate all the batch processes.

@echo off
rem Brute Force script to extract documents from password protected archive
rem Covers numbers from 100000 to 199999
 
for /l %%X in (100000, 1, 199999) do (
    echo "Processing.. " %%X
    7z e docs.zip notes.txt -p%%X -o.\output1
    cd output1
    ren notes.txt %%X.txt
    cd ..
)

Second set

@echo off
rem Brute Force script to extract documents from password protected archive
rem Covers numbers from 200000 to 199999
 
for /l %%X in (200000, 1, 299999) do (
    echo "Processing.. " %%X
    7z e docs.zip notes.txt -p%%X -o.\output2
    cd output2
    ren notes.txt %%X.txt
    cd ..
)

and so on.

At the time of writing, the scripts have been running for around an hour. Though I don’t have my password, I feel good about having learnt a bit on shell scripting and 7zip.

Edit (on 2nd Dec)
Disks formatted in NTFS allow for virtually unlimited number of files in a directory (4,294,967,295). FAT32 has a restriction of 65,534. Hence if you were running the above script on a drive formatted in the older way, you will face issues. Again, who wants to look at a folder that has 99999 files and find one that has data. I refined my script to delete the file if it was empty else rename the file and wait for the user to Terminate the batch program

@echo off
rem Brute Force script to extract documents from password protected archive
rem Covers numbers from 100000 to 199999
 
set file="notes.txt"
set minbytesize=0
 
for /l %%X in (100000, 1, 199999) do (
    echo "Processing.. " %%X
    7z e docs.zip notes.txt -p%%X -o.\output1
    cd output1
	for %%A in (%file%) do set size=%%~zA
	if %size% GTR %minbytesize% (
		echo Not empty file
		ren notes.txt %%X.txt
		set /p continue=Ctrl+C to terminate
	) else (
		del notes.txt
	)
    cd ..
)

This gave me a strange error. I suspect that a for within a for messes things up. So I created a separate batch file to do the filecheck and copied it into the output1 folder. Here are the two scripts

@echo off
rem Brute Force script to extract documents from password protected archive
rem Covers numbers from 100000 to 199999
 
set file="notes.txt"
set minbytesize=0
 
for /l %%X in (100000, 1, 199999) do (
    echo "Processing.. " %%X
    7z e docs.zip notes.txt -p%%X -o.\output1
    cd output1
    call filesize.bat
    cd ..
)

Here is filesize.bat. You need to place it in the outputn directory.

@echo off
rem Check filesize and delete file if empty
set file="notes.txt"
set minbytesize=0
for %%A in (%file%) do set size=%%~zA
if %size% GTR %minbytesize% (
   echo Not empty file
   ren notes.txt %%X.txt
   set /p continue=Ctrl+C to terminate
) else (
   del notes.txt
)

Updated code (more stable) at GitHub. Copy/Fork, it is all up to you.

PS: If you are curious, I managed to crack the password and retrieved my data.

Eztrieve – Useful Tip on Signed Numbers & Packed Decimals

One of my colleagues was working on an EZT to identify records that had negative values in a packed decimal field and report it in a displayable format in an extract file. He moved the field to a working storage [code]WS-BALANCE[code] which was defined as WS-BALANCE W 9 P. The program logic was checking for the value of INPUT-BALANCE as lesser than zero prior to extraction. No data was reported (though File-Aid was able to show multiple records with a -ve value in the file).

We began debugging the program. At the first glance I couldn’t find anything in error with his program. One thing was that I had always defined my packed decimals with a 0 at the end. He decided to give it a shot. The variable was defined as INPUT-BALANCE 100 9 P 0 and he checked for INPUT-BALANCE’s value again. This time the progam extracted records and he had his output. His troubles were not over though, the negative amounts appeared strange in the output file. For example, the number 426 showed up as 42O. The root cause turned out to be in the file definition. The output file layout had a zero to indicate number of decimalsOPDUE-H-BAL 38 17 N 0. This was interpreted as having to store sign and messed up the data. Removing this, fixed the extract.

Two lessons we learnt here were:

  1. Define numeric working storage variables with zero length decimal place so that they can store sign
  2. Do not define numeric output record layout records with zero length decimal places if you plan to use the data as-is for numeric value (such as when being extracted for another system)

What do you think?

I have recovered most of the posts that I wanted to (some tutorials & wallpapers are pending) and the site now has a new shiny look. The theme is OneEngine by Engine Themes. I had long wanted a one page theme with focus on what I did rather than just a list of latest blog posts and this fit the bill. As I began playing around with the theme, I was able to customize it to perfectly suit my needs. Some of the changes I have made are:

  • Fixed Portfolio section to order most recent items first
  • Fixed some Social Network icons in the footer. A small copy-paste error had put the same font Awesome classes for multiple sites
  • Updated the “clients” block to link content to my works elsewhere on the web

I plan to introduce a block with popular downloads. Let’s see how that works (I am using WP Filebase plugin). The one downside of this theme is that it is a bandwidth hogger. A test on pingdom showed that the whole page load took > 7s and downloaded 5.4 MB of data. That’s an area of concern. Maybe I will need to setup something on cloudflare. However, let me focus on the task at hand now; Get back to a blogging schedule and deliver content that you would like.

Share your feedback in the comments below.

Angular Shadows UI Iconset

I have been a bit busy with recovery of the blog. You will not see any changes here on thecodeisclear.in, but I am redoing the entire website on my local machine (that’s about 40+ months of content). I hope to relaunch the site next week, coinciding with the Indian festival of Deepavali; A bang of sorts.

In the meanwhile here is an Iconset of 14 icons, 4 sizes of each in PNG format. It uses the Angle gradient format in Photoshop, which I have overlooked in the past. The icons scale well and retain clarity even at 64×64 dimensions. Here’s the preview and download

Preview

Angular Shadow UI Icons

Download

Iconset Angular Shadows UI
Iconset Angular Shadows UI
Angular-Shadows-UI-Iconset.zip
578.6 KiB
18 Downloads
Details

Popular UI recreation – Part 2

Following up with my previous post of recreating popular websites in Photoshop as a means for beginners (and intermediate users), I quickly put together the 5by.com website.

I liked the many icons and it was fun creating them in Photoshop. You can download the complete PSD from my repo in GitHub.

5by.com User Interface

The main trunk of this project is Luke’s Repo

PS: The UI has been recreated for educational purposes. No copyright infringement is intended.

Editing Proxy settings via script

Based on where you are using your laptop, you may have to activate or de-activate Proxy settings. For example, when working from my corporate network, I can use the internet only via the company proxy. It is quite cumbersome to go through the entire multi-step process to change the settings each time. With a bit of internet research, I was able to automate the process through VB Scripting. Turns out system proxy settings are available in the registry. By referencing the settings, you can update entries such as enabling proxy, entering server details or even setup automatic configuration scripts.

Proxy Path to the Internet

The registry entry for enabling proxy is HKCU\Software\Microsoft\Windows\CurrentVersion\Internet Settings\ProxyEnable. This has two states 0 – to disable and 1 to enable. The proxy server settings is in HKCU\Software\Microsoft\Windows\CurrentVersion\Internet Settings\ProxyServer. Here is the code to toggle the proxy settings

Option Explicit 
Dim WSHShell, strSetting
Set WSHShell = WScript.CreateObject("WScript.Shell")
 
' Determine current proxy setting to toggle it
strSetting = wshshell.regread("HKCU\Software\Microsoft\Windows\CurrentVersion\Internet Settings\ProxyEnable")
 
If strSetting = 1 Then 
   NoProxy
Else 
   Proxy
End If
 
'Subroutine to Toggle Proxy Setting to ON
Sub Proxy 
   WSHShell.regwrite "HKCU\Software\Microsoft\Windows\CurrentVersion\Internet Settings\ProxyEnable", 1, "REG_DWORD"
   WSHShell.regwrite "HKCU\Software\Microsoft\Windows\CurrentVersion\Internet Settings\ProxyServer","192.168.26.116:80","REG_SZ"
   Wscript.Echo "Proxy Enabled"
End Sub
 
'Subroutine to Toggle Proxy Setting to OFF
Sub NoProxy 
   WSHShell.regwrite "HKCU\Software\Microsoft\Windows\CurrentVersion\Internet Settings\ProxyEnable", 0, "REG_DWORD"
   WSHShell.regwrite "HKCU\Software\Microsoft\Windows\CurrentVersion\Internet Settings\ProxyServer","","REG_SZ"
   Wscript.Echo "Proxy Disabled"
End Sub

More Options

If your network admins have a automatic configuration script, you can add it by the following

WSHShell.regwrite "HKCU\Software\Microsoft\Windows\CurrentVersion\Internet Settings\AutoConfigURL","http://intranet/www.pac","REG_SZ"

If you still want to tweak the script to automatically determine where you are (not geolocation) and set proxy accordingly, you can play around the settings a bit more

ipQuery = "SELECT IPAddress FROM Win32_NetworkAdapterConfiguration WHERE IPEnabled=TRUE" 
 
impLevel = "winmgmts:{impersonationLevel=impersonate}" 
 
Set IPConfigSet = GetObject(impLevel).ExecQuery(ipQuery) 
 
For each IPConfig in IPConfigSet 
    If Not IsNull(IPConfig.IPAddress) Then 
        For i=LBound(IPConfig.IPAddress) to UBound(IPConfig.IPAddress) 
           If Not Instr(IPConfig.IPAddress(i), ":") > 0 Then 
               If Left(IPConfig.IPAddress(i), 3) = "192" Then
                  proxySrv = "192.168.100.25:80"
               If Left(IPConfig.IPAddress(i), 2) = "10" Then
                  proxySrv = "10.100.25.63:8080"
           End If 
        Next 
    End If 
Next

You can then use the field proxySrv to update the registry Proxy entry. The options are endless, hope you found this article useful. Add your comments and thoughts below.

Credits: Various stackoverflow & tek-tips.com answers which served as the starting point to the code

Old meets New: REXX code on GitHub

After a while, I now have access to a mainframe and though my work doesn’t need me to login every day, I am still drawn to play around with REXX. I have been meaning to create a database of custom functions written in REXX that can simply be added to your code and speed up coding.
For example, I have needed to replace one string with another in more than one REXX program. I created my first REXX function wordrpl (Word Replace).
It would be a good idea to build a database of REXX functions and keep it in a place where others can access, and better, contribute. This brings me to my recent experiences with GitHub.

GitHub is a free host for developers that allows one to share code with anyone. It uses the Git revision control system and allows users to “fork” existing projects, build more into it and merge it back so that the original repository now has more content. I have been using GitHub to host a couple of my own projects and also contribute to some really cool repositories like Popular-UIs, Metro-bootstrap. I do realize that putting REXX code in a Git repository, far away from the green screen and the EBCDIC environment is going to entail additional effort. However, when you really think about it, most developers still use the internet for finding solutions to their mainframe problems. Why not Git?

The repository created is rexx-fns. Working with GitHub is a breeze. Sign-up (FREE), fork the rexx-fns repository. You can edit code directly though the web interface or using a desktop application (GitHub has their own application) make as many changes as you want. Commit to the changes and submit a request for merging to the original code repository. It is as simple as that.

Git Flow - Fork, Code and Pull

So far, I have created 3 functions and loaded them.

  • WordRpl: Function to replace text within a string
  • LuhnsCal: Calculate the check digit for a number using the Luhn’s algorithm
  • LuhnsChk: Check if a number is valid by running the Luhn’s algorithm

In the pipeline are functions related to date & time validation. Head over to GitHub and add to this growing repo.

PS: I must ask that you do not place any copyrighted code in the repository. I have no way to validate it and I will take your word when you submit a pull request. If you are in doubt, ask your peers/managers. They will help you. Err on the side of caution, please.

Making the best use of mainframe CPU

Recently, it was identified that long running jobs in our shop were being pulled up and explanation sought from the programmers. In most cases, jobs should not take hours to execute and if your job is running for a long time, it is probably a good time to re-engineer it.

Some jobs would timeout, based on the shop settings for execution time. Don’t blindly add TIME=NOLIMIT or TIME=1440 to let your job run forever. In some cases, it is better to look for more skillful ways to execute your program. Here are some easy tips.

    • Using the right Message Class (MSGCLASS): The amount of CPU available to a job and the time it gets to execute is determined by the MSGCLASS. Ask around (or run some tests on your own) to find the MSGCLASS that gets the best CPU (in terms of priority). This will in turn, make your jobs complete faster.
    • Better use of the TIME parameter: Based on experience, determine how long the job will take to execute. Prevent your job from executing longer than that by coding the TIME=x parameter, where x is the maximum number of minutes the job should execute
//AUSERJOB JOB 'ACCT NAME',
//             MSGLEVEL=(1,1),
//             CLASS=S,
//             TIME=20
  • Parallel job execution: Most of the adhoc jobs that we execute at our shop are extracts or reports. Given the large volume of data, production data is divided into multiple files (called segments). Rather than sequentially opening & closing the files one after the other, recode your program to process one file at a time and run the jobs parallely. You will need to write a SORT to merge the output together, though it is not a tough task.
  • The right program for the right job: COBOL is definitely not the best option to sort data and deliver output. Internal COBOL sorts are notorious for their slow speed and in many cases, end up calling the system installed SORT. See if you can move such SORT processes to separate steps and do only the number crunching within COBOL. (SORT if extremely powerful, in some cases, you could eliminate COBOL completely). Look for similar methods to replace the program with better options
  • Split steps from jobs into multiple jobs: This is not always a good idea since each job will not start until it finds initiators and on a heavily overloaded mainframe, queuing new jobs could place it at the back of the request line. You can do this if you are concerned about the time a single job takes (like it was in my case). Use INTRDR to queue the next job to the spool.
//* ADD THESE LINES AFTER THE LAST STEP IN YOUR JOB
//NEXTJOB EXEC  PGM=IEBGENER
//*
//* SYSUT1 references a PDS Member containing the JCL of the next JOB
//* SYSUT2 references SYSOUT that references the Internal Reader
//* What happens is that you are copying the JOB to Internal Reader which 
//* will parse the input and submit it as a JOB
//*
//SYSUT1   DD  DSN=YOUR.JOBS.PDS(PART02),
//         DISP=SHR
//SYSUT2   DD  SYSOUT=(,INTRDR)
//*
//SYSPRINT DD  SYSOUT=*
//SYSIN    DD  DUMMY