How can i list all services in Portal for ArcGIS connected to a datasource table

[ChristophRömer]

Hi,

how can i list all services in ArcGIS Enterprise connected to a datasource table.

We have a referenced standard geodatabase (SQL Server 2014) which is connected to a Portal for Arcgis. I want to check what services in Portal for ArcGIS (10.7.1) are referenced to a table in this database (full tables published or services out of joins in db query layers)?

Regards

Christoph

[DerrickWong]

Hi Christoph Römer‌

You would probably want to automate this process via a script:

Firstly, determine which services you are looking for that uses that database.

In ArcGIS Server Admin, navigate to:

https://server.FQDN.com:6443/arcgis/admin/services/FOLDER/SERVICE.MapServer/iteminfo/manifest/manifest.json?f=json&token…

This gives you details of database/FGDB connections and layers for each. e.g.

This will give you a list of services that point to the said database table.

Next, in Portal Admin, navigate to

https://server.FQDN.com:7443/portal/sharing/rest/content/users/PRIMARY_SITEADMIN_USER

Filter item list based on type, e.g. Feature Service:

For each item, locate the URL.

All portal items that have this URL (which you have determined via the first step) would be the items you would be interested in.

Hope that helps.

Regards,

Derrick

[ChristophRömer]

Thanks Derrick for your detailed solution,

a script would be fine, but a manually way would also help me for the moment.

My concrete problem is to extend a table in sql server with new columns and for this i want to stop all services to add the columns in pro and after that start the services again and configure the maps which are using the services.

Because different users might have published services out of that table i hope to see which services are connected with a table.

If i understand your solution right you explain the how to find the source to a service, so a solution from the other side.

And i had to write code searching through all services to find the database table names in the manifest.json files and list these files?

I have two problems in your explanation:

1. If the service is a published from a db-query layer in arcpro, the manifest.json shows under datasets not the tables used in the db-query but the alias-name of the db-query in the ArcPro-project

2. Listing the feature services in the portal admin i just can list the services published with the siteadmin/admin user etc. but i need to list the services from all users.?

Regards Christoph

@Esri developers: A tool to show dependencies and links between
datasources <-> feature services <-> maps <-> all kinds of apps <-> widgets (using feature services) 
would be great for portal admins.

[DerrickWong]

Hi Christoph Römer‌

RE: 1)

With a query layer, the SQL query that defines it is a static SQL statement that is executed inside the database every time the layer is displayed or used in the map. As such, it's probably why the tables used are not exposed; ArcGIS would only need to know where the database and the query is so that it can perform the query against the tables and views.

You would probably need to do a bit of manual back-tracing. i.e. locate the services which use such query layers, then inspect the aprjx file to inspect the query.

RE: 2)

You can replace siteadmin/admin user with any user:

e.g. 

/portal/sharing/rest/community/users/OnlyAViewer

Alternatively, you can access all your items via the arcgisportal directory:

or using ArcGIS Online Assistant:

ArcGIS Online Assistant 

Regards,

Derrick

---

If this answer was helpful please mark it as helpful. If this answer solved your question please mark it as the answer to help others who have the same question.

[ChristophRömer]

Thanks Derrick,

for understanding more about the places to see relations between the objects. I hoped to find an easier way. Maybe somewhere out there already had this question and wrote a solution. I will check if i get the time to build that code or not.

Regards Christoph

[ChristophRömer · Accepted solution]

I wrote some weeks ago some code in python to implement my task. The GUI isn't fancy (OnEnter doesn't work, ...), but it works for our purpose. So if someone wants to use it... One interesting thing about this: we moved two weeks ago from ArcGIS Enterprise 10.7.1 to 10.8.1 and since that time more services are listed with no datasource/tables? Did something changed in the Service Folder Structure on ArcGIS Server?

from arcgis.gis import GIS
import arcgis.gis.admin
import json
import pandas
import pandastable
from pandastable import Table, TableModel
from IPython.display import display
from tkinter import *

class Window():

    def __init__(self, master):

        master.title("DataSources to Portal Featureservices")
        master.geometry("1600x876")
        master.configure(background='#F0F0F0', pady=8, padx=8)
        source_gis = None

        self.anmeldung_label = Label(master, justify=LEFT, text="Portal Login (maybe not necessary):")
        self.user_label = Label(master, justify=LEFT, text="Username:")
        self.user_tbox = Entry(master, width=30)
        self.pw_label = Label(master, justify=LEFT, text="Password:")
        self.pw_tbox = Entry(master, show='*', width=30)
        self.empty_label = Label(master, justify=LEFT, text="           ")
        self.empty_label2 = Label(master, justify=LEFT, text="                                                 ")
        self.empty_label3 = Label(master, justify=LEFT, text="                                                 ")
        self.empty_label4 = Label(master, justify=LEFT, text="                                                 ")
        self.empty_label5 = Label(master, justify=LEFT, text="                                                 ")

        self.tabname_label = Label(master, text="Search")
        rbwert = IntVar()
        self.dienstname_rb = Radiobutton(master, value=1, variable=rbwert, text="Servicename")
        self.tabname_rb = Radiobutton(master, value=2, variable=rbwert, text="Table")
        self.tabname_tbox = Entry(master, width=30)
        self.tableframe = Frame(master, width=1506, height=640, pady=4, padx=4, borderwidth=2, relief="groove")
        self.table = pt = Table(self.tableframe, width=1506, height=640, rows=1, cols=3, editable=False, enable_menus=False)
        emptylist = []
        emptylist.append(['', '', ''])
        col = ['FEATURESERVICE NAME', 'DATASOURCE', 'TABLES']
        result = pandas.DataFrame(emptylist, columns=col)
        pt.updateModel(TableModel(result))
        pt.autoResizeColumns()
        pt.show()
        rbwert.set(2)
        self.list_button = Button(master, text="List Tables", command=lambda: self.bt_list_tables(
            self.user_tbox.get(), self.pw_tbox.get(), self.tabname_tbox.get(), rbwert.get()))
        self.exit_button = Button(master, text="Close", command=fenster.quit)

        self.anmeldung_label.grid(row=1, sticky=W, pady=2)
        self.user_label.grid(row=2, sticky=W, pady=2)
        self.user_tbox.grid(row=2, column=1, sticky=W, pady=2, padx=2)
        self.pw_label.grid(row=3, sticky=W, pady=2)
        self.pw_tbox.grid(row=3, column=1, sticky=W, pady=2, padx=2)
        self.empty_label.grid(row=4, column=1, sticky=W, pady=2, padx=2)
        self.tabname_label.grid(row=6, sticky=W, pady=6, padx=2)
        self.dienstname_rb.grid(row=6, pady=6, padx=2)
        self.tabname_rb.grid(row=6, sticky=E, pady=6, padx=2)
        self.tabname_tbox.grid(row=6, column=1, sticky=W, pady=6, padx=2)
        self.list_button.grid(row=6, column=2, sticky=W, pady=6, padx=2)
        self.empty_label2.grid(row=6, column=3, sticky=W, pady=6, padx=2)
        self.empty_label3.grid(row=6, column=4, sticky=W, pady=6, padx=2)
        self.empty_label4.grid(row=6, column=5, sticky=W, pady=6, padx=2)
        self.empty_label4.grid(row=6, column=6, sticky=W, pady=6, padx=2)
        self.tableframe.grid(row=8, columnspan=7)
        self.table.grid()
        self.table.resizeColumn(0, 300)
        self.table.resizeColumn(1, 745)
        self.table.resizeColumn(2, 460)
        self.table.redraw()
        self.exit_button.grid(row=9, column=6, sticky=E, pady=8, padx=12)


    def login(self, username, pw):
        source_gis = GIS('PORTALNAME/WEBADAPTER', username, pw, verify_cert=False)
        source_gis.users.me
        display(source_gis)
        return source_gis


    def color_red(self, val):
        color = '#DC143C' if val == "!!! NO DATENSOURCE !!!" or val == "!!! NO TABLES !!!" else 'black'
        return 'color: %s' % color


    def bt_list_tables(self, username, pw, searchstring, rbvar):
        source_gis = self.login(username, pw)

        columns = ['FEATURESERVICE NAME', 'DATASOURCE', 'TABLES']
        resultlist = []
        gis_servers = source_gis.admin.servers.list()
        for server in gis_servers:
            serviceManager = server.services
            for folder in serviceManager.folders:
                if folder in ('Geocoding', 'Routing', 'System', 'Utilities'):
                    continue
                for service in server.services.list(folder):
                    #print(service.url + "/iteminfo/manifest/manifest.json")
                    json_string = service._service_manifest()
                    json_dict = json.loads(json_string)
                    if 'databases' in json_dict:
                        databases = json_dict['databases']
                        for database in databases:
                            databasename = ""
                            # DatabaseNames
                            if 'onPremiseConnectionString' in database:
                                databasename = database['onPremiseConnectionString']
                                if "SERVER=" in databasename:
                                    dbnameshort = ""
                                    entrylist = databasename.split(';')
                                    for entry in entrylist:
                                        if "INSTANCE=" in entry:
                                            dbnameshort += entry.replace("INSTANCE=", "") + "\\"
                                        if "DATABASE=" in entry:
                                            dbnameshort += entry.replace("DATABASE=", "")
                                    databasename = dbnameshort
                                else:
                                    databasename = databasename.replace("DATABASE=", "")
                            # Tables:
                            if 'datasets' in database:
                                datasets = database['datasets']
                                for table in datasets:
                                    # Search Tables with searchstring
                                    if (rbvar == 2 and searchstring.upper() in table['onServerName'].upper()):
                                        resultlist.append([service.properties.serviceName, databasename, table['onServerName']])
                                    # Search Services
                                    if (rbvar == 1 and searchstring.upper() in service.properties.serviceName.upper()):
                                        resultlist.append([service.properties.serviceName, databasename, table['onServerName']])
                            # Search Services with a searchstring, but the service has no tables assigned
                            elif (rbvar == 1 and searchstring.upper() in service.properties.serviceName.upper()):
                                resultlist.append([service.properties.serviceName, databasename, "!!! NO TABLES ASSIGNED !!!"])
                            # No searchstring but the service has no tables assigned
                            elif (searchstring==""):
                                resultlist.append([service.properties.serviceName, databasename, "!!! NO TABLES ASSIGNED !!!"])
                                # No searchstring, but the service has no Database/-source
                    elif (rbvar == 1 and searchstring.upper() in service.properties.serviceName.upper()):
                        resultlist.append([service.properties.serviceName, "!!! NO DATABASE/-SOURCE !!!", "!!! NO TABLES ASSIGNED !!!"])
                    # No searchstring, but the service has no Database/-source
                    elif (searchstring==""):
                        resultlist.append([service.properties.serviceName, "!!! NO DATABASE/-SOURCE !!!", "!!! NO TABLES ASSIGNED !!!"])
        result = None
        if resultlist:
            result = pandas.DataFrame(resultlist, columns=columns)
        else:
            templist = []
            templist.append(['', '', ''])
            result = pandas.DataFrame(templist, columns=columns)
        (result
         .style.applymap(self.color_red)
         .hide_index()
         .set_table_styles([dict(selector='th', props=[('text-align', 'left')])])
         .set_properties(**{'text-align': 'left'}))
        self.table.updateModel(TableModel(result))
        self.table.redraw()
        self.table.show()
        fenster.config(cursor="")




if __name__ == '__main__':
    fenster = Tk()
    app=Window(fenster)
    fenster.mainloop()

[DavidHoy · Accepted solution]

Hi Christoph Römer

Sorry I only just saw this post.

Another, probably easier option that requires no coding.

Try System Log Parser tool. https://www.arcgis.com/home/item.html?id=dacebd64a0a04c87b1c48905e2cfc70d#! 

This awesome utility queries your Hosting Server's logs and drills down to the underlying services to report many useful statistics and... most importantly for your question - reports for each service the data sources for the layers within the service.

There are a couple of excellent posts by Jacob Boyle‌ that do a great job of getting you started with this.

https://community.esri.com/community/implementing-arcgis/blog/2019/04/23/arcgis-server-tuning-and-op... 

https://community.esri.com/community/implementing-arcgis/blog/2019/04/23/system-log-parser-statistic... 

You'll learn some great things about your site.