Structured Queries

Getting data out of OCI is key in begin able to then do something with that data, such as automating a process. There are some great methods for getting access to certain types of data, such as OCI metadata, log data etc, and in each case the method is usually defined by what you want the data for.

In the console, Oracle have provided a simple search bar at the top of the screen which can be used to quickly pull back data about “things” in OCI:

If you want to search for something with the name “Admin” in it, just type that here, hit return and it will pull back any object which has that name in it anywhere.. including the instance name, tags etc. It’s quick and easy to use this method, but it’s not very sophisticated. Instead, you can use the advanced resource query instead, which can be found in the top right of the screen after performing a simple search:

This search gives you more control over what you’re looking for, and the interface provides you with some great examples to get you started:

As well as links for the syntax and supported resources, but fundamentally a query such as this, could be used to, for example, find block or boot volumes with the tag “TEST”, using the syntax:

query   bootvolume, volume resources where    definedTags.value = ‘TEST’

This returns 8 records in my test system (which will be important later). While this is all really helpful for ad-hoc queries in the console, we really want to use this programmatically. When testing my syntax I generally do it in 3 stages, as this gives me the best feedback when I get it wrong, and visually shows me the responses I will get when I get it right, making debugging your query a lot easier:

1. Generate the query in the console. Works great for seeing what you did right (or wrong), and you can tweak it quickly to get the results you want. Remember, excluding results you don’t want is as important as defining the ones you do.
2. Test this using OCI’s Cloud Shell, just to make sure you’ve formatted the query correct.
3. Finally, insert the code into your script so that you can use it to do what you want.

Converting the code above into something which can be executed on the command line is very simple. There is a basic query, and you then just play around with the clauses to get it to do what you want.

The basic syntax is this:

oci search resource structured-search –query-text “<QUERY>” –limit <NO. RECORDS> –output table –query “<FIELDS TO RETURN>”

Let’s build this up in stages. Using the query above we get a JSON formatted output, of the same 8 records as above (although I’m only showing a few of them here):

oci search resource structured-search –query-text “query bootvolume, volume resources where definedTags.value = ‘TEST’”

It’s better to put all the query-text on a single line, because that makes formatting the command line easier, but you can separate it out if you like. Note the specific use of double quotes for the whole query, but single quotes for the parameters.

This gives the following output in JSON format:

You could stop there and process these as a JSON dataset, but you’ve made your job more difficult if you return all the data, so you can limit what columns it returns and even sort the data as follows:

oci search resource structured-search –query-text “query bootvolume, volume resources where definedTags.value = ‘TEST’” –query “data.items[*] | sort_by(@,&\”time-created\”) [].{Name:\”display-name\”,Type:\”resource-type\”,ID:identifier,Created:\”time-created\”}”

This only pulls back 4 columns (OCID, name, time-created & type), and sorts by date/time created. In this case, note the use of \” for the query part of the syntax, because you need to tell it that it’s not the end of the query, but a ” within the query, and you can choose what you want the deployed value to be (so in my case “resource-type” is deployed as simply “Type”. You need to put “s round anything with a hyphen in it.

This gives me a much more readable output, with only the values I wanted:

Finally, because I prefer dealing with rows of data than processing JSON output, I can format this as a table:

oci search resource structured-search –query-text “query bootvolume, volume resources where definedTags.value = ‘TEST’” –query “data.items[*] | sort_by(@,&\”time-created\”) [].{Name:\”display-name\”,Type:\”resource-type\”,ID:identifier,Created:\”time-created\”}” –output table

Which gives me this:

From here I can read in each line of the output, doing something with each one as I need (ignoring the first 3 and last lines), and using the “ | “ delimiter between columns.

In addition to the standard querying, this feature allows you to get exactly the data you want, in a friendly, easy to use format saving some of your coding complexity by getting OCI to do things like sorting and filtering for you.