I love making life easier. And let’s face it – some repetitive administrator tasks are prime targets for streamlining. Take the case of a boatload of inactive PaperCut users. Removing them’s too much trouble, but letting them take up valuable licenses is a big no-no. Here’s where scripting can really help.
What if we created a script that deletes users who meet certain conditions? Maybe it’s because they haven’t logged in or printed in 30 days; or they have a username starting with “guest-”; or they’re a student from a previous academic year. Any criteria in the PaperCut database can be used to select and delete a user (or anything else a server-command can do) automatically.
Below is a working PowerShell script that connects to SQL Server to get a list of inactive users, and then calls a server-command to delete each selected user. For this example, we’re saying that an inactive user is one who’s never logged in or printed (i.e. last_user_activity is null).
# Delete inactive users from PaperCut
# This version of the PowerShell script is written for SQL Server
# It's possible to use PowerShell for the other databases as well, but you need their connector
# Prereq: Install-module -Name SqlServer
# This module has the Invoke-Sqlcmd cmdlet needed to get data from the PaperCut database
# change directory to where server-command is
Set-Location "C:\Program Files\PaperCut MF\server\bin\win"
# the SQL Server instance
$server = "mySqlServerName\SQLEXPRESS"
# This is the SQL command to select users from PaperCut
# You could easily customize this to select users inactive for more than 30 days,
# or users that are guests or have a zero balance or whatever you need for the customer
$sqlcmd = "select user_name, full_name, last_user_activity
from papercut.dbo.tbl_user
where last_user_activity IS NULL and deleted_date IS NULL;"
# select a SQL Server DataSet from tbl_user
$ds = invoke-sqlcmd -query $sqlcmd -serverinstance $server -as dataset
# Now that we have a SQL dataset we can iterate over each user
# In this example we are invoking server-command to delete-existing-user,
# but you could do other stuff too like create a list of the users and email it to the admin
# It might also be nice to report if the user has a remaining balance
# Iterate over the dataset
if ($ds.Tables.Rows.count -gt 0) {
foreach ($Row in $ds.Tables[0].Rows) {
./server-command delete-existing-user $($Row['user_name']) | Out-File -filepath audit.log
write-host "Deleted user: " $($Row['user_name']), $($Row['full_name'])
}
} else {
write-host "No inactive users found."
}
This PowerShell script could be a starting point for a lot of other PaperCut admin tasks. Let us know about the cool things you’ve done with OS scripts! Contact us on solutions@papercut.com.
