SQL Code - Data Dictionary & restrict notification for just windows devices
IT Ninja Community-
Unfortunately I don't have a SQL guru on staff yet, but I have two questions:
1) Is there a data dictionary somewhere that explains the variable names? If I have that, I might be able to self resolve some of my reporting issues.
2) I am using a KACE training SQL code to check for the absence of our anti-virus application (Kaspersky). But, by default, this grabs all devices, including Mac and Server OS. Anyone care to help me with the variables I need to add to limit this search to just windows workstations (laptop and desktop)?
select
*,
UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_TIME,
UNIX_TIMESTAMP(MACHINE.LAST_SYNC) as LAST_SYNC_SECONDS
from
ORG1.MACHINE
LEFT JOIN
KBSYS.KUID_ORGANIZATION ON KUID_ORGANIZATION.KUID = MACHINE.KUID
LEFT JOIN
KBSYS.SMMP_CONNECTION ON SMMP_CONNECTION.KUID = MACHINE.KUID
AND KUID_ORGANIZATION.ORGANIZATION_ID = 1
where
(((((1 not in (select
1
from
ORG1.SOFTWARE,
ORG1.MACHINE_SOFTWARE_JT
where
MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID
and MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID
and SOFTWARE.DISPLAY_NAME like '%Kaspersky%')))
AND (1 not in (select
1
from
ORG1.SOFTWARE,
ORG1.MACHINE_SOFTWARE_JT
where
MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID
and MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID
and SOFTWARE.DISPLAY_NAME like '%Kaspersky%')))
AND (1 not in (select
1
from
ORG1.SOFTWARE,
ORG1.MACHINE_SOFTWARE_JT
where
MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID
and MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID
and SOFTWARE.DISPLAY_NAME like '%Kaspersky%'))))
0 Comments
[ + ] Show comments
Answers (2)
Answer Summary:
Please log in to answer
Posted by:
chucksteel
7 years ago
Top Answer
There isn't a data dictionary available, but if you are using a tool like MySQL Workbench to look at the database you should find that they are laid out well. I understand that you are trying to learn how to write the code, but you should be able to get the results for your current query using an advanced search.
Posted by:
JasonEgg
7 years ago
You can create a smart label from an advanced search. If you're looking for Windows computers without any program with 'Kaspersky' in the title, here's what you do:
The 'name' above is under the heading "Operating System" (yes, it is a bit misleading).
