UPDATE:
New question, I thought I knew how to create the smart label, but I guess not exactly.  I want to create a smart label that contains all the machines that have a regkey with the date 30 days ago or more.  This will be tied to a script to delete the "ThreatDetected" regkey.  How do I create a Smart label that will capture PCs with a regkey date that is 30 days ago or longer?  I tried this and changed the regkey on my test machine to 2013/10/6 and this smart label didn't capture it:

select MACHINE.*, C.CLIENT_CONNECTED, UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_TIME,
                       UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_SECONDS
                  from ORG1.MACHINE
                  LEFT JOIN KBSYS.KUID_ORGANIZATION O ON O.KUID=MACHINE.KUID LEFT JOIN KBSYS.SMMP_CONNECTION C ON C.KUID = MACHINE.KUID AND O.ORGANIZATION_ID = 1
                 where (((  (1  in (select 1 from ORG1.LABEL, ORG1.MACHINE_LABEL_JT where MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID and MACHINE_LABEL_JT.LABEL_ID = LABEL.ID and LABEL.TYPE != 'hidden' and LABEL.NAME like '%Threat Detected Regkey Exists%')) ) AND (1  in (select 1 from ORG1.MACHINE_CUSTOM_INVENTORY where MACHINE.ID = MACHINE_CUSTOM_INVENTORY.ID and MACHINE_CUSTOM_INVENTORY.SOFTWARE_ID = 18068 and MACHINE_CUSTOM_INVENTORY.DATE_FIELD_VALUE > '30')) ))

Thank you!

-awingren

------------------------------------------------------------------------------------------------------------------------

I am re-writing our Service Desk action response for anti-virus alerts.  One of the first things I want them to do is to mark the machine as having a threat with a breadcrumb that has today's date in it that I can capture in KACE.  Once I have that in KACE I want a Smart label that includes all PCs with a date that is within the last 30 days.  This way, over time, the Service Desk can check to see if a particular machine has had a threat within the last 30 days, and if it has, reimage it.  I know how to create the custom inventory and the Smart label, but I'm not sure how to write the script to get today's date.

Here's my custom inventory rule:
RegistryValueReturn(HKLM\SOFTWARE\KEYNAME, ThreatDetected, DATE)

I'm not even sure if I should create a batch file, vbs script, or something else...

Any help would be greatly appreciated.
Thank you!

-awingren

Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Community Chosen Answer

3

As usual, I was over-engineering it!  Here's the very simple solution:

.bat is the way to go:
reg add HKLM\SOFTWARE\KEYNAME /v ThreatDetected /t REG_SZ /d "%date:~10,4%/%date:~4,2%/%date:~7,2%" /f

Hope it helps someone else!

-awingren

Answered 11/07/2013 by: awingren
Eighth Degree Black Belt

Please log in to comment

Answers

1

For your smart label, on the date comparison, try something like

MACHINE_CUSTOM_INVENTORY.DATE_FIELD_VALUE < DATE_SUB(NOW(), INTERVAL 30 DAY)

That will subtract 30 days from the current date, then compare your custom date field. The syntax is a little counter intuitive, but your basically looking for dates that are less than (i.e. BEFORE) 30 days ago.

Answered 11/08/2013 by: BHC-Austin
Fourth Degree Black Belt

  • Thanks, BHC-Austin. That makes sense, but I think I'm still missing something. I'm getting a syntax error when I change it to that:

    mysql error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 5] in EXECUTE(

    "select MACHINE.*, C.CLIENT_CONNECTED, UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_TIME, UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_SECONDS from ORG1.MACHINE LEFT JOIN KBSYS.KUID_ORGANIZATION O ON O.KUID=MACHINE.KUID LEFT JOIN KBSYS.SMMP_CONNECTION C ON C.KUID = MACHINE.KUID AND O.ORGANIZATION_ID = 1 where ((( (1 in (select 1 from ORG1.LABEL, ORG1.MACHINE_LABEL_JT where MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID and MACHINE_LABEL_JT.LABEL_ID = LABEL.ID and LABEL.TYPE != 'hidden' and LABEL.NAME like '%Threat Detected Regkey Exists%')) ) AND (1 in (select 1 from ORG1.MACHINE_CUSTOM_INVENTORY where MACHINE.ID = MACHINE_CUSTOM_INVENTORY.ID and MACHINE_CUSTOM_INVENTORY.SOFTWARE_ID = 18068 and MACHINE_CUSTOM_INVENTORY.DATE_FIELD_VALUE < DATE_SUB(NOW(), INTERVAL 30 DAY)) ))")

    I think it has me down the right path, though. I just need to figure out the proper order and number of parentheses, etc... MySQL is so confusing!
    • Looks like you may just be missing one parenthesis at the end of your statement.
      • wow! O_O Thank you...
        I feel a little sheepish! LOL
Please log in to comment
Answer this question or Comment on this question for clarity