Customize MySQL Reports

mqh7
New Contributor II

I know Jamf/Casper uses MySQL on the backend for Casper. On our Casper Admin machine I don't see any GUI to access MySQL. I want to see the Tables/Views so I can write custom SQL statements. Where is this done?

1 ACCEPTED SOLUTION

donmontalvo
Esteemed Contributor II

Here's what I ran...

$ mysql -u jamfsoftware -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 11478
Server version: 5.0.92-log Source distribution

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema | 
| jamfsoftware       | 
| test               | 
+--------------------+
3 rows in set (0.00 sec)

mysql> use jamfsoftware;
Database changed
mysql> show tables;
+------------------------------------------------------+
| Tables_in_jamfsoftware                               |
+------------------------------------------------------+
| adobe_payloads                                       | 
| application_usage_logs                               | 
| applications                                         | 
| attachment_assignments                               | 
| attachments                                          | 
| autorun_additions                                    | 
| autorun_byhost_macs                                  | 
| autorun_data                                         | 
| autorun_exceptions                                   | 
| autorun_script_values                                | 
| autorun_users                                        | 
| blacklist                                            | 
| buildings                                            | 
| casper_suite_application_preferences                 | 
| certificate_authority_issued                         | 
| certificate_authority_settings                       | 
| computer_commands                                    | 
| computer_group_memberships                           | 
| computer_groups                                      | 
| computer_invitations                                 | 
| computer_user_pushtokens                             | 
| computers                                            | 
| configuration_memberships                            | 
| configurations                                       | 
| custom_reports                                       | 
| database_schema_overrides                            | 
| departments                                          | 
| directory_bindings                                   | 
| dock_items                                           | 
| enrollment_process_strings                           | 
| extension_attribute_popup_choices                    | 
| extension_attribute_values                           | 
| extension_attributes                                 | 
| file_servers                                         | 
| fonts                                                | 
| groups                                               | 
| hard_drive_partitions                                | 
| hard_drives                                          | 
| hardware_reports                                     | 
| hidden_files                                         | 
| iphone_applications                                  | 
| iphone_details                                       | 
| iphones                                              | 
| issued_certificates                                  | 
| jss_cache_sequence                                   | 
| jss_cluster_nodes                                    | 
| jss_context_updates                                  | 
| jss_settings                                         | 
| ldap_servers                                         | 
| licensed_software                                    | 
| licensed_software_definitions                        | 
| licensed_software_usage                              | 
| licensed_software_usage_counts                       | 
| location_history                                     | 
| locations                                            | 
| log_actions                                          | 
| log_errors                                           | 
| logs                                                 | 
| management_framework_preferences                     | 
| mcx_profile_assignments                              | 
| mcx_profile_network_segments                         | 
| mcx_profiles                                         | 
| mcx_settings                                         | 
| mcx_settings_assignments                             | 
| mobile_app_volume_purchase_codes                     | 
| mobile_device_app_assignments                        | 
| mobile_device_app_deployment_queue                   | 
| mobile_device_app_network_segments                   | 
| mobile_device_apps                                   | 
| mobile_device_configuration_profile_assignments      | 
| mobile_device_configuration_profile_network_segments | 
| mobile_device_configuration_profiles                 | 
| mobile_device_enrollment_profile_enrollment_history  | 
| mobile_device_enrollment_profiles                    | 
| mobile_device_group_memberships                      | 
| mobile_device_groups                                 | 
| mobile_device_installed_certificates                 | 
| mobile_device_installed_profiles                     | 
| mobile_device_installed_provisioning_profiles        | 
| mobile_device_management_commands                    | 
| mobile_device_management_framework_preferences       | 
| mobile_device_management_invitations                 | 
| netboot_servers                                      | 
| network_segments                                     | 
| operating_systems                                    | 
| os_installer_categories                              | 
| os_installer_items                                   | 
| os_x_configuration_profile_assignments               | 
| os_x_configuration_profile_network_segments          | 
| os_x_configuration_profile_update_queue              | 
| os_x_configuration_profiles                          | 
| os_x_installed_configuration_profiles                | 
| package_contents                                     | 
| package_objects                                      | 
| package_receipts                                     | 
| packages                                             | 
| partitions_for_imaging                               | 
| peripheral_type_field_choices                        | 
| peripheral_types                                     | 
| peripherals                                          | 
| plugins                                              | 
| policies                                             | 
| policy_accounts                                      | 
| policy_assignments                                   | 
| policy_directory_bindings                            | 
| policy_dock_items                                    | 
| policy_group_memberships                             | 
| policy_history                                       | 
| policy_network_segments                              | 
| policy_packages                                      | 
| policy_printers                                      | 
| policy_scripts                                       | 
| pre_stage_computer_names                             | 
| pre_stage_logs                                       | 
| pre_stage_mac_addresses                              | 
| pre_stage_network_segments                           | 
| pre_stages                                           | 
| printers                                             | 
| provisioning_profiles                                | 
| purchasing_information                               | 
| recon_custom_paths                                   | 
| recon_fields                                         | 
| recon_prefs                                          | 
| removable_mac_addresses                              | 
| reports                                              | 
| saved_search_criteria                                | 
| saved_search_display_fields                          | 
| saved_searches                                       | 
| scheduled_deletions                                  | 
| scripts                                              | 
| self_healing_trigger_files                           | 
| self_service_plugins                                 | 
| serial_number_script_assignments                     | 
| serial_number_scripts                                | 
| smart_computer_group_criteria                        | 
| smart_mobile_device_group_criteria                   | 
| software_licenses                                    | 
| software_update_servers                              | 
| ssh_accounts                                         | 
| task_assignments                                     | 
| tasks                                                | 
| unix_executables                                     | 
| unixapps                                             | 
| usage_logs                                           | 
| user_group_roles                                     | 
| user_groups                                          | 
| user_roles                                           | 
| users                                                | 
| whitelist                                            | 
+------------------------------------------------------+
149 rows in set (0.03 sec)

mysql>
--
https://donmontalvo.com

View solution in original post

7 REPLIES 7

jafuller
Contributor

Sorry, but you'll have to install your own preferred MySQL GUI if you choose to. Are you using OS X or Windows or Linux as your server OS?

dpertschi
Valued Contributor

Have a start with these pages:

https://yourJSS:8443/apiFrontPage.rest 

https://yourJSS:8443/apiIntro.rest 

donmontalvo
Esteemed Contributor II

Here's what I ran...

$ mysql -u jamfsoftware -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 11478
Server version: 5.0.92-log Source distribution

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema | 
| jamfsoftware       | 
| test               | 
+--------------------+
3 rows in set (0.00 sec)

mysql> use jamfsoftware;
Database changed
mysql> show tables;
+------------------------------------------------------+
| Tables_in_jamfsoftware                               |
+------------------------------------------------------+
| adobe_payloads                                       | 
| application_usage_logs                               | 
| applications                                         | 
| attachment_assignments                               | 
| attachments                                          | 
| autorun_additions                                    | 
| autorun_byhost_macs                                  | 
| autorun_data                                         | 
| autorun_exceptions                                   | 
| autorun_script_values                                | 
| autorun_users                                        | 
| blacklist                                            | 
| buildings                                            | 
| casper_suite_application_preferences                 | 
| certificate_authority_issued                         | 
| certificate_authority_settings                       | 
| computer_commands                                    | 
| computer_group_memberships                           | 
| computer_groups                                      | 
| computer_invitations                                 | 
| computer_user_pushtokens                             | 
| computers                                            | 
| configuration_memberships                            | 
| configurations                                       | 
| custom_reports                                       | 
| database_schema_overrides                            | 
| departments                                          | 
| directory_bindings                                   | 
| dock_items                                           | 
| enrollment_process_strings                           | 
| extension_attribute_popup_choices                    | 
| extension_attribute_values                           | 
| extension_attributes                                 | 
| file_servers                                         | 
| fonts                                                | 
| groups                                               | 
| hard_drive_partitions                                | 
| hard_drives                                          | 
| hardware_reports                                     | 
| hidden_files                                         | 
| iphone_applications                                  | 
| iphone_details                                       | 
| iphones                                              | 
| issued_certificates                                  | 
| jss_cache_sequence                                   | 
| jss_cluster_nodes                                    | 
| jss_context_updates                                  | 
| jss_settings                                         | 
| ldap_servers                                         | 
| licensed_software                                    | 
| licensed_software_definitions                        | 
| licensed_software_usage                              | 
| licensed_software_usage_counts                       | 
| location_history                                     | 
| locations                                            | 
| log_actions                                          | 
| log_errors                                           | 
| logs                                                 | 
| management_framework_preferences                     | 
| mcx_profile_assignments                              | 
| mcx_profile_network_segments                         | 
| mcx_profiles                                         | 
| mcx_settings                                         | 
| mcx_settings_assignments                             | 
| mobile_app_volume_purchase_codes                     | 
| mobile_device_app_assignments                        | 
| mobile_device_app_deployment_queue                   | 
| mobile_device_app_network_segments                   | 
| mobile_device_apps                                   | 
| mobile_device_configuration_profile_assignments      | 
| mobile_device_configuration_profile_network_segments | 
| mobile_device_configuration_profiles                 | 
| mobile_device_enrollment_profile_enrollment_history  | 
| mobile_device_enrollment_profiles                    | 
| mobile_device_group_memberships                      | 
| mobile_device_groups                                 | 
| mobile_device_installed_certificates                 | 
| mobile_device_installed_profiles                     | 
| mobile_device_installed_provisioning_profiles        | 
| mobile_device_management_commands                    | 
| mobile_device_management_framework_preferences       | 
| mobile_device_management_invitations                 | 
| netboot_servers                                      | 
| network_segments                                     | 
| operating_systems                                    | 
| os_installer_categories                              | 
| os_installer_items                                   | 
| os_x_configuration_profile_assignments               | 
| os_x_configuration_profile_network_segments          | 
| os_x_configuration_profile_update_queue              | 
| os_x_configuration_profiles                          | 
| os_x_installed_configuration_profiles                | 
| package_contents                                     | 
| package_objects                                      | 
| package_receipts                                     | 
| packages                                             | 
| partitions_for_imaging                               | 
| peripheral_type_field_choices                        | 
| peripheral_types                                     | 
| peripherals                                          | 
| plugins                                              | 
| policies                                             | 
| policy_accounts                                      | 
| policy_assignments                                   | 
| policy_directory_bindings                            | 
| policy_dock_items                                    | 
| policy_group_memberships                             | 
| policy_history                                       | 
| policy_network_segments                              | 
| policy_packages                                      | 
| policy_printers                                      | 
| policy_scripts                                       | 
| pre_stage_computer_names                             | 
| pre_stage_logs                                       | 
| pre_stage_mac_addresses                              | 
| pre_stage_network_segments                           | 
| pre_stages                                           | 
| printers                                             | 
| provisioning_profiles                                | 
| purchasing_information                               | 
| recon_custom_paths                                   | 
| recon_fields                                         | 
| recon_prefs                                          | 
| removable_mac_addresses                              | 
| reports                                              | 
| saved_search_criteria                                | 
| saved_search_display_fields                          | 
| saved_searches                                       | 
| scheduled_deletions                                  | 
| scripts                                              | 
| self_healing_trigger_files                           | 
| self_service_plugins                                 | 
| serial_number_script_assignments                     | 
| serial_number_scripts                                | 
| smart_computer_group_criteria                        | 
| smart_mobile_device_group_criteria                   | 
| software_licenses                                    | 
| software_update_servers                              | 
| ssh_accounts                                         | 
| task_assignments                                     | 
| tasks                                                | 
| unix_executables                                     | 
| unixapps                                             | 
| usage_logs                                           | 
| user_group_roles                                     | 
| user_groups                                          | 
| user_roles                                           | 
| users                                                | 
| whitelist                                            | 
+------------------------------------------------------+
149 rows in set (0.03 sec)

mysql>
--
https://donmontalvo.com

mqh7
New Contributor II

donmontalvo, that is Awesome!! But.... I don't get a MySQL prompt. I get a ServerName~Admin$ prompt. So how do I get to the MYSQL Prompt? I've typed in mysql -u jamfsoftware -p and it does not work.

I come from Windows so bare with me :-)

jafuller
Contributor

At the prompt type: mysql
This will put you into the MySQL prompt. In most cases you may need to use "sudo mysql" instead to get to the MySQL prompt as the administrator or root user.

When you're done, type "exit" to leave the MySQL prompt and return to the normal terminal prompt.

bentoms
Esteemed Contributor
Esteemed Contributor

You may need to specify the location where MySQL is installed, rather than just calling "mysql"

donmontalvo
Esteemed Contributor II

JSS is on OS X box right now, so I just ssh'd over and ran mysql -u jamfsoftware -p and went from there. Once we migrate to windows we'll need to log on to the server and run from the MySQL command prompt.

Matt, you're a Windows guy, I presume JSS is running on Windows Server? If not I'm sure you're planning a migration now...LOL

--
https://donmontalvo.com