/build/static/layout/Breadcrumb_cap_w.png

Getting Under the Hood - Looking Directly at the KACE SMA Database

At times you'd like to be able to look more directly at the underlying SQL database that stores all your K1 SMA data. The video at:

https://www.youtube.com/watch?v=LRWbSu_vxQE

is a quick overview of how to do that, although a bit dated in the Spring of 2020.

For those who would like a printed HOWTO, here ya go.

First, make sure the SMA has that access turned on. Log into your SMA, and look under "Settings" / "Security Settings" and make sure that "Enable database access" is ticked.

wY0IkRbpkaIAAAAASUVORK5CYII=


You'll also need to know the name of the database, and the user who can log into that database. You'll find this at "Settings" / "General Settings":

The default database name is "ORG1" and the default Username is "R1" and the default password is "box747" (although it won't be showing in the password field).


Now you need a copy of MYSQL Workbench. The video shows you how to download it from Oracle's website for Windows, but I'm using Debian, so it's a simple matter of "apt install mysql-workbench" (but I did get a couple of "this version is not compatible with your database"-type warnings, which I clicked through without harm).

Then start MYSQL Workbench.

Click on the plus to add a new connection; name it something like "KACE_Browser".

Fill in the server name (something like "k1000.mycompany.org"), and the username ("R1", probably).

Everything else on these setup screens can probably be left at their defaults.

Click on "OK" to finish setting up the connection.

BD0ydHMSjITUzaAwCCCCAAAIIRIxAiMejTucQCkwjZuzpCAIIIIAAAggggEAAApoA8pAFAQQQQAABBBBAAIEqFyAwrXJSCkQAAQQQQAABBBAIRIDANBA18iCAAAIIIIAAAghUuQCBaZWTUiACCCCAAAIIIIBAIAIEpoGokQcBBBBAAAEEEECgygUITKuclAIRQAABBBBAAAEEAhEgMA1EjTwIIIAAAggggAACVS7wf8ZlW8hcARD8AAAAAElFTkSuQmCC

Now that you have your connection made, click on it to open it. You'll be asked for the password (see above).

EpArX2wAAAABJRU5ErkJggg==


rl5eX0A9xLgAABAgQIECBAgAABAgQIJAo4hSdxanomQIAAAQIECBAgQIAAAQIEWgUEKK3cihEgQIAAAQIECBAgQIAAAQKJAgKUxKnpmQABAgQIECBAgAABAgQIEGgVEKC0citGgAABAgQIECBAgAABAgQIJAoIUBKnpmcCBAgQIECAAAECBAgQIECgVUCA0sqtGAECBAgQIECAAAECBAgQIJAoIEBJnJqeCRAgQIAAAQIECBAgQIAAgVYBAUort2IECBAgQIAAAQIECBAgQIBAooAAJXFqeiZAgAABAgQIECBAgAABAgRaBQQordyKESBAgAABAgQIECBAgAABAokCApTEqemZAAECBAgQIECAAAECBAgQaBUQoLRyK0aAAAECBAgQIECAAAECBAgkCghQEqemZwIECBAgQIAAAQIECBAgQKBVQIDSyq0YAQIECBAgQIAAAQIECBAgkCggQEmcmp4JECBAgAABAgQIECBAgACBVgEBSiu3YgQIECBAgAABAgQIECBAgECigAAlcWp6JkCAAAECBAgQIECAAAECBFoFBCit3IoRIECAAAECBAgQIECAAAECiQIClMSp6ZkAAQIECBAgQIAAAQIECBBoFRCgtHIrRoAAAQIECBAgQIAAAQIECCQKCFASp6ZnAgQIECBAgAABAgQIECBAoFVAgNLKrRgBAgQIECBAgAABAgQIECCQKCBASZyangkQIECAAAECBAgQIECAAIFWAQFKK7diBAgQIECAAAECBAgQIECAQKKAACVxanomQIAAAQIECBAgQIAAAQIEWgUEKK3cihEgQIAAAQIECBAgQIAAAQKJAgKUxKnpmQABAgQIECBAgAABAgQIEGgVEKC0citGgAABAgQIECBAgAABAgQIJAoIUBKnpmcCBAgQIECAAAECBAgQIECgVUCA0sqtGAECBAgQIECAAAECBAgQIJAoIEBJnJqeCRAgQIAAAQIECBAgQIAAgVYBAUort2IECBAgQIAAAQIECBAgQIBAooAAJXFqeiZAgAABAgQIECBAgAABAgRaBQQordyKESBAgAABAgQIECBAgAABAokCApTEqemZAAECBAgQIECAAAECBAgQaBUQoLRyK0aAAAECBAgQIECAAAECBAgkCghQEqemZwIECBAgQIAAAQIECBAgQKBVQIDSyq0YAQIECBAgQIAAAQIECBAgkCggQEmcmp4JECBAgAABAgQIECBAgACBVgEBSiu3YgQIECBAgAABAgQIECBAgECigAAlcWp6JkCAAAECBAgQIECAAAECBFoFBCit3IoRIECAAAECBAgQIECAAAECiQIClMSp6ZkAAQIECBAgQIAAAQIECBBoFRCgtHIrRoAAAQIECBAgQIAAAQIECCQKCFASp6ZnAgQIECBAgAABAgQIECBAoFVAgNLKrRgBAgQIECBAgAABAgQIECCQKCBASZyangkQIECAAAECBAgQIECAAIFWAQFKK7diBAgQIECAAAECBAgQIECAQKKAACVxanomQIAAAQIECBAgQIAAAQIEWgUEKK3cihEgQIAAAQIECBAgQIAAAQKJAgKUxKnpmQABAgQIECBAgAABAgQIEGgVEKC0citGgAABAgQIECBAgAABAgQIJAoIUBKnpmcCBAgQIECAAAECBAgQIECgVUCA0sqtGAECBAgQIECAAAECBAgQIJAoIEBJnJqeCRAgQIAAAQIECBAgQIAAgVYBAUort2IECBAgQIAAAQIECBAgQIBAooAAJXFqeiZAgAABAgQIECBAgAABAgRaBQQordyKESBAgAABAgQIECBAgAABAokCApTEqemZAAECBAgQIECAAAECBAgQaBUQoLRyK0aAAAECBAgQIECAAAECBAgkCghQEqemZwIECBAgQIAAAQIECBAgQKBVQIDSyq0YAQIECBAgQIAAAQIECBAgkCggQEmcmp4JECBAgAABAgQIECBAgACBVgEBSiu3YgQIECBAgAABAgQIECBAgECigAAlcWp6JkCAAAECBAgQIECAAAECBFoFBCit3IoRIECAAAECBAgQIECAAAECiQIClMSp6ZkAAQIECBAgQIAAAQIECBBoFRCgtHIrRoAAAQIECBAgQIAAAQIECCQKCFASp6ZnAgQIECBAgAABAgQIECBAoFVAgNLKrRgBAgQIECBAgAABAgQIECCQKCBASZyangkQIECAAAECBAgQIECAAIFWgX8BHcHgNddYXYwAAAAASUVORK5CYII=


And, you're in!

PkKEEIAABCAAAQhAAAIQaG4B5AnNLY7xIAABCEAAAhCAAAQgYPwCyBOMfx8hQghAAAIQgAAEIAABCDS3wP8DCGwlnkOM2mQAAAAASUVORK5CYII=

In the left-hand pane, you can slide the title of that pane over to "Schemas"; most of your time will be spent in "ORG1", so you can right-click on that and select "Set as default schema".


Now, to get your feet wet just a tiny bit, open "ORG1" in the left-hand pane, then "Tables", then "MACHINE".

Right-click on "MACHINE" and click on "Select Rows - Limit 1000". This should show you the first 1000 of your devices in your K1 database. You'll notice it created a new tab at the top of the Query pane.

lgCGGW4TFmruIGrxB5H16ohLVxNTMxOzHssuMQjSfMOzAe1AAAgAASAABIAAEPhKCPwPVQ2G1MGL7fAAAAAASUVORK5CYII=


To modify this query, from "SELECT * FROM ORG1.MACHINE;", add between the word "MACHINE" and the colon a limiting WHERE statement, like so:

SELECT * FROM ORG1.MACHINE WHERE NAME = "BOBSPC";

Then click on the little lightning bolt ("Execute the query...")

Try another modification to your query, like so:

SELECT * FROM ORG1.MACHINE WHERE NAME LIKE "BOB%";

This should show all devices with a name that beings with "BOB".

B+GzSU0ik8jOQAAAABJRU5ErkJggg==

From this point on, it's just a matter of figuring out what you're doing.

Hopefully this little HOWTO will be of value to someone.



Comments

  • Why is this spam? *I* found the information useful when I found it on a YouTube video, and thought it being in an "official" place in written form might be of use to others (and to myself, should I need to reference it in future months/years). *You* might not be interested in it, but I'm hard-pressed to defend any accusation of "spam" against this information, and would appreciate an explanation.

    And if it is spam, why not just inform me instead of banning me?

    UPDATE MONTHS LATER:
    I think I misunderstood the "This content is currently hidden from public view" item above my comment here; I'm thinking that's some other comment by some other commentor, and the accusation of spam is not against my article, but against that comment. It's hard tell tell though, with the format of this page. But it must be so, or Moncus below wouldn't have seen it, if it was hidden from public view. - kentwest 1 year ago
    • If I hadn't just worked through setting this up, this article would have been extremely helpful! - JZeigler 1 year ago
    • I agree, this is a great place to start with MySQL Workbench. I also believe that everything after "And, you're in!" should be added to this KB article:
      https://support.quest.com/kace-systems-management-appliance/kb/114938 - Moncus 1 year ago
This post is locked

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or