Link between SOFTWARE and SAM_VIEW_DISCOVERED_SOFTWARE tables
Hi,
I need to find out if discovered software is installed or not and if there is a link between the 2 tables.
The problem with KACE is that on the discovered software (SAM_VIEW_DISCOVERED_SOFTWARE) table it contains EDITION and LICENSE_TYPE fields while on the installed software (SOFTWARE) table it does not.
Is there maybe a JT (join table) that links SOFTWARE.ID to SAM_VIEW_DISCOVERED_SOFTWARE.ID?
I need to know if installed software needs to be licensed or not for our MicroSoft SQL Server/Express count.
Then KACE also made a mistake in their classification of SQL server 2014 Express where they indicated that it's supposed to be Licensed in the SAM_VIEW_DISCOVERED_SOFTWARE table! lol NOT!
Then first prize would be if I can then also have a JT/link from SOFTWARE to the NTSERVICE table where I can see if the sqlservr.exe is running or not so that I can contact the owners to have the ones that's been stopped uninstalled.
Tnx
Henk Breytenbach
0 Comments
[ + ] Show comments
Answers (3)
Please log in to answer
Posted by:
h2opolo25
9 years ago
select M.NAME, M.IP, SMS.NAME, SMS.EDITION, N.* from SAM_VIEW_MACHINE_DISCOVERED_SOFTWARE SMSleft join MACHINE M on M.ID = SMS.MACHINE_IDleft join MACHINE_NTSERVICE_JT MN on MN.MACHINE_ID = M.IDleft join NTSERVICE N on N.ID = MN.NTSERVICE_IDwhere N.DISPLAY_NAME like '%SQL Server%'and N.STATUS = 'SERVICE_RUNNING'
You can do something like this. You have the table that shows discovered software for a specific machine then you use the Machine table and the JT to NTService to get your info.
Play around with the data to figure out how you want to see things.
SMS.NAME = Software name form the SAM View
N.NAME or N.DISPLAY_NAME can be used to filter through the NTSERVICE tasks
SMS.LICENSE_TYPE can be used to show only the license types you want (you'd have to exclude the Express 2014 mistake with a NOT statement if you want)
Not sure why you want the Software table in there but if you really want to add it there's a MACHINE_SOFTWARE_JT you can use.
Comments:
-
Hi,
Tnx but this is data related and not joining of tables.
In the SAM_VIEW_DISCOVERED_SOFTWARE.ID field I get a value of APP4772 for 'SQL Server 2008 R2 Standard' but that software id does not exist in the SOFTWARE.ID field.
The SAM tables contains discovered software and the MACHINE_SOFTWARE_JT contains installed programs but they each have their own set of software codes! How stupid! lol
The NTSERVICE table tells me whether sqlservr.exe is running or not, but I cannot get the software id from the table to determine whether it's a licensed version or freeware. I can only join to MACHINE from it.
I need to report to MicroSoft how many installed SQL instances we have so that we can check if our licensing is in order.
I want to write a script that I don't have to maintain every time a new version of sql server is released.
KACE doesn't have and ERD that I know of nor can their support people help me.
This is most probably the worst packages available on the market and it's worrying that Dell bought it and does nothing to fix it.... - Henk Breytenbach 9 years ago -
The ideal would be to get the following for SOFTWARE, MACHINE_SOFTWARE_JT, MACHINE
select distinct A.ID, A.NAME, A.PRODUCT_NAME, A.MAJOR_VERSION, A.EDITION, A.MACHINE_ID, A.LICENSE_TYPE, B.USER, B.NAME, B.CS_MODEL, B.BIOS_SERIAL_NUMBER
from SAM_VIEW_MACHINE_DISCOVERED_SOFTWARE A
left join ORG1.MACHINE B on A.MACHINE_ID = B.ID
where A.license_type = 'Licensed'
and A.product_name = 'sql server'; - Henk Breytenbach 9 years ago
Posted by:
chucksteel
9 years ago
I don't have a complete answer but I did find the following relationships:
ORG1.SAM_VIEW_DISCOVERED_SOFTWARE.ID = KBSYS.SAM_APPLICATION.ID
ORG1.SAM_MACHINE_JT.MACHINE_ID = ORG1.MACHINE.ID
ORG1.SAM_VIEW_DISCOVERED_SOFTWARE.ID = ORG1.SAM_MACHINE_JT.SAM_CATALOG_ID
It looks like you don't really need the KBSYS.SAM_APPLICATION table for this report, so putting those together can get you this report:
SELECT DS.*, M.NAME FROM ORG1.SAM_VIEW_DISCOVERED_SOFTWARE DS
JOIN SAM_MACHINE_JT JT on JT.SAM_CATALOG_ID = DS.ID
JOIN MACHINE M on M.ID = JT.MACHINE_ID
WHERE DS.NAME like "%SQL Server%"
This doesn't get you exactly what you want because it includes the client tools, not just the server. Product Name seems to be a better field to look at:
SELECT DS.*, M.NAME FROM ORG1.SAM_VIEW_DISCOVERED_SOFTWARE DS
JOIN SAM_MACHINE_JT JT on JT.SAM_CATALOG_ID = DS.ID
JOIN MACHINE M on M.ID = JT.MACHINE_ID
WHERE DS.PRODUCT_NAME = "SQL Server"
Remember that the SAM tables are for the software catalog which stores suites and multiple versions of software and the SOFTWARE table is for individual explicit versions, so it is difficult to cross from one to the other.
Comments:
-
Hi,
This doesn't help me at all as I need the join with the SOFTWARE table.
If I look at a specific machine in KACE there are discovered and installed software lists that are way different.
SAM shows discovered and MACHINE_SOFTWARE shows installed, but the problem is that it does not contain all the SAM fields eg. License Type.
I don't want to pay license fees on a product that's not installed.
I know that SAM_VIEW_DISCOVERED_SOFTWARE has a field called INSTALLED_ON that gives the number of 'installations', but it's not physical installations. The software is in a directory/external drive connected to that machine and not necessarily installed.
If you go to add/remove programs they don't always appear there.
In short installed software is a subset of discovered software, but there is no link in KACE between the 2 and they also don't have consistent classifications. - Henk Breytenbach 9 years ago
Posted by:
CraigT
9 years ago
The SAM_VIEW_* tables are resource hogs as MySQL/MariaDB does not use them well. If you need to join to machine use SAM_MACHINE_JT and CATALOG.SAM_CATALOG for doing it like this.
select MACHINE.NAME, SAM_MACHINE_JT.*, SC.*
from MACHINE
INNER JOIN SAM_MACHINE_JT ON
MACHINE.ID = SAM_MACHINE_JT.MACHINE_ID
AND MAX_VERSION = 1
INNER JOIN CATALOG.SAM_CATALOG SC ON
SC.ID = SAM_MACHINE_JT.SAM_CATALOG_ID;
Now that just lists all the stuff on a machine matched up to the software catalog and the max version at that. The K1 identifies all executables on a box so it can show multiple versions installed. Now if you wanted to get fancy and join back against running processes and only for SQL Server it would look something like the following.
select MACHINE.NAME, SAM_MACHINE_JT.*, SC.*
from MACHINE
INNER JOIN SAM_MACHINE_JT ON
MACHINE.ID = SAM_MACHINE_JT.MACHINE_ID
AND MAX_VERSION = 1
INNER JOIN CATALOG.SAM_CATALOG SC ON
SC.ID = SAM_MACHINE_JT.SAM_CATALOG_ID
inner join MACHINE_NTSERVICE_JT ON
MACHINE.ID = MACHINE_NTSERVICE_JT.MACHINE_ID
INNER JOIN NTSERVICE ON
NTSERVICE.ID = MACHINE_NTSERVICE_JT.NTSERVICE_ID
WHERE SC.NAME LIKE '%SQL SERVER%' AND
NTSERVICE.DISPLAY_NAME LIKE '%SQL SERVER';
Comments:
-
Tnx, but you guys are all missing the point. I don't want to use the SAM tables as they are giving discovered software and not installed software. - Henk Breytenbach 9 years ago
