I am trying to use  KACE for our Microsoft compliance reporting.  I am starting with Microsoft Office.  When I looked at the number of installations within the software catalog it became apparent that the results were distorted when there was more than one version of Office installed on a given machine.

I am a SQL faker - in that I don't really know how to write SQL queries, but use the wizard to create my initial SQL statement, then paste it into a SQL editor with a 'designer' visual interface that I can use to edit with SQL without actually knowing how to edit SQL :-)

I was able to create a query using this method to show me the number of machines that have a match to more than one "MS Office" version within the Software catalog. 

Select
  Count(CATALOG.SAM_CATALOG.ID) As Count_ID,
  MACHINE.NAME
From
  CATALOG.SAM_CATALOG Left Join
  SAM_MACHINE_JT SMJ On CATALOG.SAM_CATALOG.ID = SMJ.SAM_CATALOG_ID Left Join
  MACHINE On MACHINE.ID = SMJ.MACHINE_ID
Where
  CATALOG.SAM_CATALOG.SAM_TYPE = 'TITLED_SUITE' And
  CATALOG.SAM_CATALOG.NAME Like '%Office%' And
  CATALOG.SAM_CATALOG.PUBLISHER Like '%Microsoft%' And
  MACHINE.ID Is Not Null
Group By
  MACHINE.NAME
Having
  Count(CATALOG.SAM_CATALOG.ID) > 1


I want to be able to use the information in the SAM Catalog for other reporting, but I can't find that table (or a view) within my SQL editor, or in the ODBC connection view.  I don't know what object CATALOG.SAM_CATALOG is pointing to.  (or if I am asking my question in a way that makes sense...)

Any thoughts?

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0
I would recommend looking at the database with a tool like MySQL Workbench. This will give you a list of the tables and you can browse the data to get a sense of where things are located.

That being said, the SAM_CATALOG table exists in the CATALOG database. There are multiple databases on the K1000 depending on your configuration. If you only have one organization then you should have the CATALOG, KBSYS and ORG1 databases. 

For software reporting I normally rely on the ORG1.SOFTWARE table. This table is version specific whereas the catalog information will group minor versions together (I believe).

Answered 08/20/2015 by: chucksteel
Red Belt

  • I do use a SQL tool to look at the database, and my question stems from not being able to see the SAM_CATALOG table there. My understanding as well is that the "software" and the "software catalog" information was different. I can use the software catalog to look for "Microsoft Office" but within the software table it only shows Excel, Outlook, Word etc separately. Will the same credentials work on the Catalog database as the Org1?
    • Yes, the credentials I use to connect to the K1 show me all three databases and I can browse them all.
Please log in to comment
Answer this question or Comment on this question for clarity