Script to create a regkey breadcrumb that I can capture in a label in KACE
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
Answers (2)
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
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.
Comments:
-
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! - awingren 11 years ago-
Looks like you may just be missing one parenthesis at the end of your statement. - BHC-Austin 11 years ago
-
wow! O_O Thank you...
I feel a little sheepish! LOL - awingren 11 years ago