Customize MySQL Reports

[mqh7]

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?

[jafuller]

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]

Have a start with these pages:

https://yourJSS:8443/apiFrontPage.rest 

https://yourJSS:8443/apiIntro.rest 

[donmontalvo · Accepted solution]

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>

[mqh7]

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]

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]

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

[donmontalvo]

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