With more machines being used off campus for possibly extended periods of time due to the response to COVID-19 we may need to change Windows licensing from KMS to MAK (our KMS is not available outside of our network). In order to identify machines that are close to having their license expire I configured a custom inventory rule to collect the Windows activation status:
ShellCommandTextReturn(cscript.exe c:\windows\system32\slmgr.vbs /dli)
Using this data I created a report to identify computers that are off campus and are activated using our KMS. The number of days remaining on their activation is extracted using a regular expression.
SELECT MACHINE.ID, MACHINE.NAME, MACHINE.LAST_INVENTORY,
cast(replace(regexp_substr(STR_FIELD_VALUE, '[0-9]+ day'), ' day', '') as signed integer) as 'Days',
replace(regexp_substr(STR_FIELD_VALUE, 'License Status: ([A-z]+)'), 'License Status: ', '') as 'License Status',
case when STR_FIELD_VALUE like '%License Status: Notification%' then
substring_index(STR_FIELD_VALUE, '<br/>', -4)
else ''
end as 'Message',
MACHINE.IP,
STR_FIELD_VALUE
FROM ORG1.MACHINE_CUSTOM_INVENTORY
JOIN MACHINE on MACHINE.ID = MACHINE_CUSTOM_INVENTORY.ID
WHERE SOFTWARE_ID = 32218
and STR_FIELD_VALUE like '%VOLUME_KMS%'
and MACHINE.IP not like '172.%'
ORDER BY Days ASC
Note that the software ID for my custom inventory rule is 32218, if you want to replicate this report in your environment you will need to replace that value in the above query with the ID for your custom inventory rule. I am also identifying computers that are off campus based on their IP address. Machines on campus are assigned addresses in the 172.0.0.0 address space. While it is possible a machine could be off campus with a similarly configured network, it is rather uncommon.
While a report is useful, a smart label makes this data actionable. This query will apply a smart label to machines with the license expiring in the next 30 days:
SELECT MACHINE.NAME AS SYSTEM_NAME, SYSTEM_DESCRIPTION, MACHINE.IP, MACHINE.MAC, MACHINE.ID as TOPIC_ID,
cast(replace(regexp_substr(STR_FIELD_VALUE, '[0-9]+ day'), ' day', '') as signed integer) as 'Days' FROM ORG1.MACHINE_CUSTOM_INVENTORY JOIN MACHINE on MACHINE.ID = MACHINE_CUSTOM_INVENTORY.ID WHERE SOFTWARE_ID = 32218 and STR_FIELD_VALUE like '%VOLUME_KMS%' HAVING Days < 30
Once this label is applied to machines a script that changes the activation to MAK could be configured to run on a schedule that would prevent the license from expiring.
Comments