I think that I may have found this out.
SELECT DISTINCT A0.Computer_Idn,A0.Type,A0.DeviceName,A0.Description,A0.LoginName,A0.HWLastScanDate,A0.SWLastScanDate,A0.VALastScanDate,A0.MainBoardOEM_Name,A0.SystemOEM_Name,A0.DISPLAYNAME, A0.TYPE, A1.OSTYPE FROM Computer A0 (nolock) LEFT OUTER JOIN Operating_System A1 (nolock) ON A0.Computer_Idn = A1.Computer_Idn ORDER BY A0.DISPLAYNAME
The easiest way to locate the location of data that I've come up with over the years?
==> Write a query and then use the INSPECTOR.
So for instance -- you're after OS information so -- let's write a query to check "where OS NAME exists" (note that specifically OS info can vary between Windows & NIX & MAC potentially!)
Only query for the attribute you care about. Leave the default column sets (saves you clutter in a moment).
2 - Then use the INSPECTOR on the query you just generated (you'll see why).
3 - Now select the SQL statement that's generated from your query (displayed by default) and throw it into the SQL Query analyser for easier readability:
4 - And here we are in the SQL Query analyser. I've cleaned up the SQL for easier readability with a few sensibly placed carriage returns and 2 comments, that's about it .
(You essentially work your way from the bottom up!)
and - to make life easier - in a copy/paste-able form:
SELECT DISTINCT A0.DISPLAYNAME, A0.TYPE, A1.OSTYPE FROM Computer A0 (nolock) -- The "A1" shorthand resolves to the OPERATING_SYSTEM table in this join LEFT OUTER JOIN Operating_System A1 (nolock) ON A0.Computer_Idn = A1.Computer_Idn -- This 'WHERE'clause is actually what you care about. So you need to resolve the "A1" shorthand WHERE (A1.OSTYPE IS NOT NULL) ORDER BY A0.DISPLAYNAME
That's all there is to it
Many thanks for your post and explanation. It was very helpful and much appreciated.
Happy to be of some help.
Since you're dealing with 3rd party apps - you may find this useful too -- Getting Started with the MBSDK (Example Scripts Included) .
You can use either dynamically created column sets or "set" column sets and get data from the LDMS DB in the "LANDesk Query" format in XML format, which you may find useful for your purposes perhaps too .
Whilst there are A LOT of tables ... don't get intimidated by them. The actual architecture of the DB is quite clean & simple ... it's just big .