/build/static/layout/Breadcrumb_cap_w.png

Miscellaneous Question


SQL Code - Data Dictionary & restrict notification for just windows devices

05/24/2017 656 views
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%'))))
Answer Summary:
0 Comments   [ + ] Show comments

Comments


Answer Chosen by the Author

1
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. 
Answered 05/25/2017 by: chucksteel
Red Belt

All Answers

0
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).
Answered 06/02/2017 by: JasonEgg
Red Belt

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 when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ