Copy Diagnostic Monitor logs as Excel table, not as vertical tags

Bud

ArcGIS Pro 3.1.1

In the Diagnostic Monitor —> Log tab:

I can right-click the logs —> Copy selected:

When I paste the copied values into Excel, the data looks like this:

The log values are squished into a single column as <tags>.

It would be better if the data was formatted as a table, similar to how it looks in the Diagnostic Monitor Log tab:

That would facilitate tabular analysis of the log data.

Could the Diagnostic Monitor logs be enhanced so that the data gets copied as a table, not as tags?

 

 

Bud

As a last resort, the following workaround might be useful. It's pretty involved; I wouldn't want to do it very often.

1. Copy the logs as described in the original idea. Paste into Excel.

2. Add a header to the logs column: LOG_TAGS.

3. Select all values. Create an Excel table from the data (CTRL+T).

Option #1 - Use a SQL query in a database (go to step #4 below).

Option #2 - Use Power Query in Excel (go to the next comment in this post).

4. Create an additional Excel table column: ID. Populate with sequential numbers.
Reason for field: I figure it might be unwise to rely on the OBJECTID field as a sequential ID field.

5. The Excel table should look like this:

6. Save the Excel spreadsheet as Logs.xlsx.

7. In ArcGIS Pro, create a new mobile geodatabase called LOGS.geodatabase.

8. Use the Excel to Table GP tool to import the spreadsheet to the mobile GDB as a table.

1. Use these parameters:
   
2. Click Run.
3. The table has been created.
   
4. Rename the table from main_LOGS to LOGS.  Reason: there's a bug.
5. 

9. Run the following SQL query using a SQL client like DBeaver.

WITH cte AS (  --Related Oracle post: https://stackoverflow.com/questions/76024662/fill-in-rows-per-group-for-pivoting
    select sum(case when log_tags like '<Event%' then 1 else 0 end) over (order by id) group_id,  --https://stackoverflow.com/a/76021834/5576771
           case
             when substr(log_tags,1,13) = '<Event time="'                      then 'col_1'
             when substr(log_tags,1, 9) = 'Database:'                          then 'col_2'
             when substr(log_tags,1,10) = '      SQL:'                         then 'col_3'
             when substr(log_tags,1,16) = '      Number of '                   then 'col_4'
             when substr(log_tags,1, 8) = '</Event>'                           then 'col_5'
             else 'col_6' --Sometimes the values in cols 2-4 are different from what I'm querying for above. Col_6 is a catch-all for any unexpected values.
                          --However, col_6 will only grab the first unexpected value it comes across; all others will be ignored. I haven't found a better way to do it.
           end as type,    
           log_tags  --substr(log_tags,1,100)
      from logs
     where log_tags is not null)
--     
  select group_id,
         max(case when  type  = 'col_1' then      log_tags  end) col_1,
         max(case when  type  = 'col_2' then      log_tags  end) col_2,
         max(case when  type  = 'col_3' then trim(log_tags) end) col_3,
         max(case when  type  = 'col_4' then trim(log_tags) end) col_4,
         max(case when  type  = 'col_5' then      log_tags  end) col_5,
         max(case when  type  = 'col_6' then trim(log_tags) end) col_6
    from cte
group by group_id
order by group_id

1. Note: I tried using the SQL above to create a database view in the mobile geodatabase. However, ArcGIS Pro didn't display the data in the attribute table correctly. Mobile GDB database view — CASE expression value shown incorrectly in attribute table. So I abandoned that idea.
2. If you do try to create a database view, be aware that SQL comments are buggy in mobile GDBs when using the Create Database View GP tool. Example: SQL comment in WITH clause in mobile GDB database view. So you might need to remove the comments or create the view directly from a SQL client like DBeaver.

9. The resultset of the SQL query in DBeaver looks like this:

That's the desired result.

We can use that data to do tabular analysis on the logs when troubleshooting issues. We'd likely need to parse values from the columns to make the data useful.

---

Notes:

 

A. Be aware that not all logs/rows are structured the same way. Some logs have SQL queries in them like the ones shown in the above screenshots.

But other logs are different:

That makes it difficult to query the tag data using SQL. Cases/columns 2-4 work well most of the time. But not all of the time. That's where case/column 6 comes in. But it has limitations too.

case
  when substr(log_tags,1,13) = '<Event time="'                      then 'col_1'
  when substr(log_tags,1, 9) = 'Database:'                          then 'col_2'
  when substr(log_tags,1,10) = '      SQL:'                         then 'col_3'
  when substr(log_tags,1,34) = '      Number of features returned:' then 'col_4'
  when substr(log_tags,1, 8) = '</Event>'                           then 'col_5'
  else 'col_6' --Sometimes the values in cols 2-4 are different from what I'm querying for above. Col_6 is a catch-all for any unexpected values.
               --However, col_6 will only grab the first unexpected value it comes across; all others will be ignored. I haven't found a better way to do it.
end as type, 

 

B. I tried skipping the Excel step and just pasting the log data from the Diagnostic Monitor right into an empty mobile geodatabase table. However, that seemed buggy. Sometimes the data would paste as expected. Other times, it seemed to omit or rearrange some rows. I didn't spend much time trying to find the root cause. I just played it safe and used Excel as an intermediate step instead.  

 

C. The SQL query uses generic SQL. I've only tested it in SQLite (mobile GDB) and Oracle. But it should work in other DBs too.