SDE Connection report by user and computer

BlakeTerhune — Fri, 10 Oct 2014 17:45:11 GMT

Previously, I had created a Python script that used ArcSDESQLExecute to query the SDE.PROCESS_INFORMATION table to get detailed connection information. Something like

SELECT UPPER(NODENAME) AS COMPUTER, COUNT(NODENAME) AS CONNECTIONS
FROM SDE.PROCESS_INFORMATION
GROUP BY NODENAME
ORDER BY CONNECTIONS DESC, NODENAME

and

SELECT UPPER(NODENAME) AS COMPUTER, OWNER AS USERNAME, COUNT(OWNER) AS USERCONNS
FROM SDE.PROCESS_INFORMATION
GROUP BY NODENAME, OWNER
ORDER BY NODENAME, USERCONNS DESC, OWNER

Between the two, I was able to get the total number of connections for each computer, which users that computer was connecting with, and how many connections of each user were open.

Since then, I stumbled across ListUsers and thought maybe I should be using it instead of the SQL. On a mission to conquer ListUsers and get the same SDE connections "report" I came up with this:

import arcpy
from operator import itemgetter

#Get all connections and print the total
GPRO_Connections = arcpy.ListUsers("Database Connections/SDE@GPRO.sde")
print "{} total connections:".format(len(GPRO_Connections))

#Get and count unique computer connections and sort by number of connections
AllClients = [conn.ClientName for conn in GPRO_Connections] ##Use list comprehension to get all ClientName values
Clients = [(client, AllClients.count(client)) for client in set(AllClients)] ##Use list comprehension to get unique tuples of ClientName and number of connections

#Get and count unique user connections for each computer and print results
for c in sorted(Clients, key=itemgetter(1), reverse=True): ##Sort the unique computers by descending connections and print results
    print "{} ({})".format(c[0], c[1])
    ## Get and count unique user connections for the computer
    AllClientUsers = [user.Name for user in GPRO_Connections if user.ClientName == c[0]] ##Use list comprehension to get all users on the computer
    ClientUsers = [(usr, AllClientUsers.count(usr)) for usr in set(AllClientUsers)] ## Use list comprehension to get unique tuples of users on the computer and number of connections
    ## Sort the computer's users by descending connections and print results
    for u in sorted(set(ClientUsers), key=itemgetter(1), reverse=True):
        print "\t{} ({})".format(u[0], u[1])

# Cleanup
del GPRO_Connections
arcpy.ClearWorkspaceCache_management()

The ouput looks something like this

43 total connections:
CC1WA125 (9)
  SDE (4)
  JTX (2)
  GBATASKS (2)
  GBAVIEWER (1)
8GK69Y1 (3)
  GBAVIEWER (1)
  WARRENW (1)
  KURTHO (1)
8GZ59Y1 (3)
  GISVIEWER (1)
  RANDYE (1)
  GBATASKS (1)
1HTX8Y1 (3)
  GBAVIEWER (1)
  GISVIEWER (1)
  KURTHO (1)
8H269Y1 (2)
  JESSEF (1)
  GISVIEWER (1)
1HSZ8Y1 (2)
  GBAVIEWER (1)
  GISVIEWER (1)
1NGY8Y1 (2)
  GISVIEWER (1)
  SDE (1)
6IKSA69716 (2)
  WS (2)
D2P3JF1 (2)
  CHRISSI (1)
  GBATASKS (1)
1HWZ8Y1 (2)
  JASONT (1)
  GBATASKS (1)
1K1Z8Y1 (2)
  GBAVIEWER (1)
  KRISTYN (1)
1MRX8Y1 (2)
  GBAVIEWER (1)
  GISVIEWER (1)
HGL7XL1 (1)
  WS (1)
75L7XL1 (1)
  WS (1)
8JKSB95938 (1)
  WS (1)
6JKSA78574 (1)
  WS (1)
2M068Y1 (1)
  SDE (1)
VM1WA91 (1)
  GISVIEWER (1)
8GS49Y1 (1)
  GISVIEWER (1)
1K1X8Y1 (1)
  ERIKS (1)
46L27W1 (1)
  WS (1)

I've been learning Python from scratch over the past couple months and I'm pretty proud of how far I've come. However, I know there are a lot of nifty ways to do things and thought maybe the Python wizards here could offer some insight on the problem and my solution. At the very least, maybe someone else can get some use out of this code.

topic SDE Connection report by user and computer in Python Questions

SDE Connection report by user and computer