Python Automation – Locked out users

Repetitive tasks present a problem. Not only do you have to do the task, but you have to remember to do the task. Wouldn’t it be great if we could get our computer to do that repetitive task and alert us if we need to do something. With Python programming, you can do just that.

python-oig-website Last time, we looked at how to automate the OIG and GSA checks that are required for any medical companies. That program saves over 3 hours of time each month (minor, unless you are the one spending 3 hours running the checks) and just keeps saving more and more time as the number of employees increase. This time, we are looking at accounts, users, and passwords.

If you have an account online, chances are good that you have been locked out of that account. You may have entered the wrong password too many times or changed your password (and forgot what you changed it to…). Your caps lock may have been on or your computer keyboard could be malfunctioning. No matter what the issues was, the immediate problem is that your account is locked. How do you get it unlocked?

Many services have automated systems to unlock accounts. If you forget your iTunes password, you can go to iForgot.apple.com and have your password reset. For smaller systems or systems without automated account unlocking procedures, someone has to go in and enable your account. It is this instance that we are concerned with today.

Automating with Python

To automate a task, you must define the task. It helps if those tasks can be completed in a variety of ways, but you have to know what you are doing before you can get a computer to do it. Our task today is to:

Check for locked accounts
If there are any locked account, notify me via email

Simple, right. With the right tools, it’s not that bad. Luckily, the accounts are stored in a SQL database. The status of the accounts is reflected in the database. After we define the problem a little more, our procedure grows to:

Connect to the SQL database
Look at the user table to see if there are any records marked as disabled but not deleted (delflag = 0)
If any users are found, send an email with the names

Not much more complicated and more specific. Let’s look at the code:

#! python3
# web-portal.py
# **********************
# This program checks the web portal for locked users
# and emails the list if any are found

import pypyodbc
import smtplib

smtpServer = {your SMTP server}
smtpFrom = {your From address}
smtpTo = {your To address}
smtpBody = 'Subject: Locked accounts\n\n'

exceptions = []

# ***** Establish database connection with ODBC 
connection = pypyodbc.connect('DSN={Your database connection}') # from ODBC connection already setup
cursor = connection.cursor()

# ***** Define query
SQLCommand = ("select * from web_users where delflag=0 and disabled = 1 and fk_Web_usergroups > 0")

# ***** Execute query
cursor.execute (SQLCommand)

# ***** Get all results (could use fetchone to just get one row if query results are large)
results = cursor.fetchall()
if(len(results)>0): # Only output results if there are any results
	for row in results:
		exceptions.append('User: %s %s' % (row[9], row[10]))

	# ***** Adds a bit of explanation to the body of the email
	smtpBody += 'The following users are locked out:\n\n'		
	# ***** prints all of the rows in exceptions followed by a \n
	smtpBody += '\n'.join(str(x) for x in exceptions)
			
	# ***** Connect to SMTP server		
	smtpObj = smtplib.SMTP(smtpServer, 25)
	# ***** Sent email
	smtpObj.sendmail (smtpFrom, smtpTo, smtpBody)
	smtpObj.quit()
else:
	print("No exceptions found")

Let’s look at the code:

#! python3
# web-portal.py
# **********************
# This program checks the web portal for locked users
# and emails the list if any are found

import pypyodbc
import smtplib

The first line defines which version of Python we are programming in. After that, all of the lines that start with # are comments, meant to make the file easier to read in the future. Chances are good that any code will be forgotten by the time you need modify it next. Best to provide good descriptions for your future self or anyone else that will be enhancing your code.

After the comments, we import modules (bundles of code) that we can use even though the code is not included in the program (even if we didn’t write it). The two we are using today allows us to access an ODBC or database connection and the other allows us to send emails via SMTP.

smtpServer = {your SMTP server}
smtpFrom = {your From address}
smtpTo = {your To address}
smtpBody = 'Subject: Locked accounts\n\n'

exceptions = []

Here, we define the email addresses, the SMTP server, and part of the email (specifically, the subject). We then define an array that will hold the names of the locked accounts.

# ***** Establish database connection with ODBC 
connection = pypyodbc.connect('DSN={Your database connection}') # from ODBC connection already setup
cursor = connection.cursor()

This code establishes a connection to an existing ODBC connection that was setup previously. We won’t get into that now, just make sure that whatever you name the connection, you use the same name here. The next line sets up the ability to run commands on the SQL server (specifically, a query, that we will get to next).

# ***** Define query
SQLCommand = ("select * from web_users where delflag=0 and disabled = 1 and fk_Web_usergroups > 0")

# ***** Execute query
cursor.execute (SQLCommand)

# ***** Get all results (could use fetchone to just get one row if query results are large)
results = cursor.fetchall()

Here, we define the query (looking for all the results that are not deleted and are disabled), execute the query, and get all of the results crammed into one variable called results.

if(len(results)>0): # Only output results if there are any results
     ...
else:

If we have any results, we need to output the names and send an email. Otherwise, well, we’ll get to that in a bit. Let’s concentrate on what happens if we find some locked accounts.

for row in results:
     exceptions.append('User: %s %s' % (row[9], row[10]))

Once we have all of the database results, we need to get those results into a readable format. We loop through each row in the result and output the first and last names to the exceptions variable.

# ***** Adds a bit of explanation to the body of the email
smtpBody += 'The following users are locked out:\n\n'		
# ***** prints all of the rows in exceptions followed by a \n
smtpBody += '\n'.join(str(x) for x in exceptions)

Since no one wants to get an email that makes no sense, we add a little text that explains what the email is about. Specifically, we say that this list of names are all locked out of the system. After that, we add the names to the body of the email, one per line.

# ***** Connect to SMTP server		
smtpObj = smtplib.SMTP(smtpServer, 25)
# ***** Sent email
smtpObj.sendmail (smtpFrom, smtpTo, smtpBody)
smtpObj.quit()

We setup the connection to the email or SMTP server, send the email, and close the connection.

Now, let’s look at what happens when there are no locked accounts

else:
     print("No exceptions found")

We display a message that no exceptions were found and the program closes.

Summary

This was a short program but hopefully, a useful one. This task can be scheduled to run on a schedule that you choose to check for locked accounts. Fortunately, in my situation, that doesn’t happen often . Unfortunately, that means that without this program, the database has to be checked often. By automating this task with Python, we can free up our mind to focus on the important things, keeping us more efficient, happy, and more valuable.

Directives

To automate tasks with Python, consider the following:

Define each step in your task in a way that can be performed by a computer
Consider what you want to happen at the end (do you want to be notified? Perform a task?)
Be sure to add comments to make it easier to modify your file in the future

Automating with Python

Summary

Directives

Leave a Reply Cancel reply