SQL query - select computer_name from the log_actions table

[acdesigntech]

I have a need to gather computer names from the jamf database, based solely on information in the log_actions table, and my MySQL-fu is a tad rusty, to say the least. Any help from someone who knows way more about SQL would be SOOO much appreciated.

Basically I have to

select computer_name from computers,logs,log_actions where
...SQLMAGIC ... something along the lines of the action in log action = 'Installation failed. The installer reported: installer: Error the package path specified was invalid: '/Library/Application Support/JAMF/Downloads/Adobe CS4.pkg'.' ...SQL MAGIC...

yeah I have no idea how to write this query. I know what has to be done, but... yeah.

Thanks! Andrew

[agilliard]

SQL Magic lol... You could use a wild card search.

select computer_name from computers,logs,log_actions where log_action like '%Failed%'

Change out failed for what ever search term you want and it will find it in that field.

[acdesigntech]

Ah, if only it were that easy. I know enough to select entries from a single table, or even two tables that share a primary key, but this is where it gets interesting:

log_actions shares a primary key with logs, which shares a primary key with computers, which contains the computer name. I know I have to join tables somewhere, but alas my last DB course was about 10 years ago and I don't even know the right questions to ask, so google is of no help :(

[acdesigntech · Accepted solution]

interestingly enough, here's the query I was looking for:

select computer_name from
    jamfsoftware.computers, jamfsoftware.logs, jamfsoftware.log_actions
where
    computers.`computer_id` = logs.`computer_id` and
    logs.`log_id` = log_actions.`log_id` and
    log_actions.`action` = "Installation failed. The installer reported: installer: Error the package path specified was invalid: '/Library/Application Support/JAMF/Downloads/Adobe CS4.pkg'.";

My friend is an uber googler, and she pionted me to this site: http://support.sas.com/documentation/cdl/en/sqlproc/62086/HTML/default/viewer.htm#a001361784.htm

Where i proceeded to write the correct query.