Win MMC, adding tasks to the ADUC menu

Its not a common thing to do, customizing the MMC console Apps so they are less annoying when they first startup, or adding third party tools that take arguments from an MMC context menu.. as in perform this task on this machine or collection of machines. Here's how to do that.

... to be continued

Win Task, email the results

There are a couple of ways of sending email from Windows 2008r2 or Windows 2012r2.

Both depend on whats inherently available with the operating system.

Windows 2008r2 included an email function built-into WinTask, but it also imposed a limitation on email "From:" to be only from the username that executed the task, hence that account had to have a mailbox or it would generically error out befuddling the hapless administrator.

Windows 2012r2 removed the email function citing security concerns and the availablility of a email function in the powershell toolbox as the preferred method for emailing.

This demonstration will show how to use both, when triggered by the ending of another WinTask script.. which can be monitored by process "WinTask name" using a custom XML trigger for its criteria.

... to be continued

Windows, reg value interpretation and publishing

Sometimes you want to monitor a registry value and publish it without installing a heavy remote management infrastructure, heres how to do that.

For this demo the Teamviewer client/server is installed and the ID for connecting to it is made available to any machine that can retrieve a list of the local groups on that windows system.

Teamviewer is like RDP but works over port 80 and 443, its encrypted and allows a remote console without complex setup. It works across OSX, Windows and Linux so its rather unbiquitous.

The key piece of information to have is the ID or TVID.. which is a three digit triplet of numbers that act like an address or phone number to connect with a system that has already started and connected with the Internet connection directory service. Where possible Teamviewer uses local peer to peer connections, and fallsback to a remote proxy service to ensure connections are made.

The Teamviewer "Host" package is a Microsoft Software Installer package, a kind of atomic database installation package which makes distributing and installing signed packages easy. This [.msi] also accepts command line arguments to control the level of detail exposed to the end User upon installation.

Often you may want to have an end user install it or install it using a GPO, and then either automatically, or on-demand summon the current TeamViewer ID so that you can open a concurrent console with a system to debug or help out an end user.

@echo off

taskkill /IM TeamViewer.exe

FOR /F "skip=2 tokens=2*" %%i IN ('reg query "HKEY_LOCAL_MACHINE\SOFTWARE\TeamViewer\Version6" /V "ClientID"') do set /a "LAD=%%j"

net localgroup | find /i "%LAD%" > nul && goto exists

net localgroup %LAD% /ADD /COMMENT:"TeamViewer ID"

taskkill /IM TeamViewer.exe

:exists
exit /B 0

When Teamviewer installs it creates a unique TVID and stores it in the systems registery.

This can be fetched using the SQL "like" command line tool [ reg query ] but the value is in [ hex ].

reg query "HKEY_LOCAL_MACHINE\SOFTWARE\TeamViewer\Version6" /V "ClientID"

>    ClientID    REG_DWORD    0x1a3c3d6b

This then needs to be parsed and converted into decimal so a user can type it into a remote Teamviewer client to connect to the system with the TVID which is acting as the console server.

FOR /F "skip=2 tokens=2*" %%i IN ('reg query "HKEY_LOCAL_MACHINE\SOFTWARE\TeamViewer\Version6" /V "ClientID"') do ...

Which means

FOR /F

skip=2 - number of lines to skip at the beginning of the file

tokens=2* - tokens to be passed to FOR body, skipping the first token and only passing the second token and all others assigned to i,j,k ect.. then "do" something

Which converts the token 0x1a3c3d6b  into a decimal number

set /a "LAD=%%

>  440124987

Basically the "set /a" command [evaluates] the expression which is prefixed with a 0x string that indicates it is in [ hex ]  and the result is displayed in decimal form.
 
Once its in decimal form, its possible to create a [net local group] on the local windows system and annotate the local group with a description.

 net localgroup | find /i "%LAD%" > nul && goto exists

Searches the local group list from the result of "net localgroup" for an existing group by the name of the decimal TVID number, if it finds one it doesn't bother to create one (avoiding a collision error that would raise a script error) and exits the routine.

net localgroup %LAD% /ADD /COMMENT:"TeamViewer ID"

But if such a local group does not exist, it creates a localgroup with the TVID as its name and changes the description for the local group to indicate what this group is for.. in a way it creates a [value] = [key] pair which can be indexed and searched for using a remote tool or simple command line script.


Listing local groups are then accessible using common command line tools, gui tools, or even remote mangement tools and powershell commands.

There are pros and cons to using [value]=[key] or [key]=[value] order when repurposing the local groups list as a generically accessible string array.

There are name space collison possibilities, ease of search string matching depending on the method used, eye ease of finding, or security issues (minor obscuring by obfuscating what its for.. the description isn't always displayed by default).. even organizational procedures regarding local group naming conventions.. but none of that distracts from the cool factor of "intepreting and publishing" an arbitrary reg value which could be accessed using generic tools, or logged in an event log or emailed.

Crontabs, Quick test for SSL websites

Sometimes you need to check the status or result of an SSL protected website after performing maintenance in an automated fashion, here's how to do that.

The typical crontab routine

MAILTO="john.willis@johnwillis.com, jwillis@johnwillis.com"

9 3 * * * /usr/bin/wget -O /dev/null https://www.johnwillis.com/index.cfm

Wget is a pretty flexible tool and if it is directed to [output] > [-O] the results of retrieving a website the details can be directed to devnull, while the alternate pipe results can be automatically emailed to the crontabs nominated recipient list.

--2015-03-24 03:09:01--  https://www.johnwillis.com/index.cfm 

Resolving www.johnwillis.com... 192.168.2.36 

Connecting to www.johnwillis.com|192.168.2.36|:443... connected.

HTTP request sent, awaiting response... 200 OK

Length: unspecified [text/html]

Saving to: `/dev/null'

     0K .......                                                59.1M=0s

2015-03-24 03:10:05 (59.1 MB/s) - `/dev/null' saved [8121]

 The really nice thing is the test is simple and quick to setup, not overly verbose, and does provide some measure of assurance that the connection was over an SSL protected connection (the certificate works) and was able to download the content to a file.

It could be refactored to perform additional testing or levels of detail, but essentially this would be sufficient for most quick glances at a mobile phone or early in the morning to be assured that a routine performed at night had not left a service in a nonfunctional state.

Crontabs, Nifty Notifications

When creating crontabs to exec a routine, its sometimes helpful to send the results as an wbem indication, snmptrap or email message often these are received on mobile device and a quick glance should indicate status, here's how to do that.

First a typical crontab routine


# crontab -e


58 7 * * * ( /wpa/test-auth-eduroam/wpa_supplicant/eapol_test -c /wpa/test-auth-eduroam/wpa_supplicant/peap-mschapv2.conf -a 192.168.2.180 -s "inwhowetrust" | grep ^SUCCESS > /dev/null ) && { echo "<p style='font-family:arial;color:green;font-size:20px;'> SUCCESS </p>" > test-auth-eduroam1.txt; } || { echo "<p style='font-family:arial;color:red;font-size:20px;' > FAILED </p>" > test-auth-eduroam1.txt; } ; mail -s "$(echo -e "eduroam - testing\nContent-Type: text/html")" john.willis@johnwillis.com < test-auth-eduroam1.txt
It follows the regular pattern of

[when/howoften]

[what/todo]

followed by a logical clause that creates a custom msg file based on the results of a grep for a condition in the results of the output of a command

[ 58 7 * * * ]
<<<<<<<< is the usual "when/howoften"


( /wpa/test-auth-eduroam/wpa_supplicant/eapol_test -c /wpa/test-auth-eduroam/wpa_supplicant/peap-mschapv2.conf -a 192.168.2.180 -s "inwhowetrust" | grep ^SUCCESS  > /dev/null )
<<<<<<<< is the usual "what/todo"

it "includes" a follow up task by piping the output of the command through a grep statement to search for a condition and consigns the rest to the bit bucket devnull

but the result of the grep statement sets a conditional status

which is used by the next piece

&& { echo "<p style='font-family:arial;color:green;font-size:20px;'>  SUCCESS  </p>" > test-auth-eduroam1.txt; } || { echo "<p style='font-family:arial;color:red;font-size:20px;' >  FAILED  </p>" > test-auth-eduroam1.txt; }

to "explicitly" echo an html formatted  msg based on the value of that "conditional status" from the grep statement

it is "either" going to be

>>>>>> "green formatted SUCCESS"

or

>>>>>> "red formatted FAILED"

the last piece merely "compounds" the number of things to do before this crontab is complete with a semicolon in normal bash shell fashion

mail -s "$(echo -e "eduroam - testing\nContent-Type: text/html")" john.willis@johnwillis.com < test-auth-eduroam1.txt

it sets a mail msg subject line and content header

directs the msg to a recipient

and inports the custom msg body from the content file filled with the desired formatted result of the grep test

the nice thing about this method is it demonstrates the level of sophistication achieveable using basic tools and the original crontab method in concert with what I like to call "horizontal programming" or "horizontal procedureal thinking"

there is no reason this could not be extended indefinately in the orthogonal dimension except possible command shell buffer limits.. and unfortunately that does seem to be the case with certain malware

WSUS, How to Email Reports

 
Report.sql > contents follow <

/* note- javascript ignores whitespace, transact sql does not.
This allows the following raiserror trick to work. It inserts a custom CSS header.
Transact will try to interpret the message according to pseudo-printf substitution,
%% is required rather than % to print a % character, $_(name) is required rather than $(name)
where the underscore is an ordinary space.
*/

RAISERROR ('
<script language="JavaScript" type="text/javascript" src="http://code.jquery.com/jquery-1.7.2.min.js"></script>
<style type="text/css" media="screen">table { width: 100%%; border: 1px solid #cef; text-align: left; } th { font-weight: bold; background-color: #acf; border-bottom: 1px solid #cef;} td,th {padding: 4px 5px;}.odd {background-color: #def;} .odd td{border-bottom:1px solid#cef;}></style>
<script language="JavaScript" type="text/javascript">$ (document).ready(function(){$ ("tr:odd").addClass("odd");});</script>
</script>
', 0, 1) WITH NOWAIT


/*
Approved security updates compliance report
Find computers within a specific target group that need security updates
that have been approved to this group (or a parent group) for at least N days
Optionally, only consider updates of a given MSRC severity rating
*/


SET NOCOUNT ON
DECLARE @TargetGroup nvarchar(30)
DECLARE @Days int

-- Configure these values as needed
-- Example: SELECT @TargetGroup = 'Unassigned Computers'
-- Example: Microsoft update is older than 1 days
SELECT @TargetGroup = 'SUS Reporting Group'
SELECT @Days = 1

-- Find the target group and all it's parent groups
DECLARE @groups AS TABLE (Id uniqueidentifier NOT NULL)
DECLARE @groupId uniqueidentifier
-- retrieve the singular @groupID that corresponds to the TargetGroup
SET @groupId = (
    SELECT ComputerTargetGroupId
    FROM PUBLIC_VIEWS.vComputerTargetGroup
    WHERE vComputerTargetGroup.Name = @TargetGroup
)
IF @groupId is NULL
    RAISERROR ('Invalid Target Group Name', 16, 1)
-- create a table @groups loaded with all the groupId's where the TargetGroup is a member
WHILE @groupId IS NOT NULL
BEGIN
    INSERT INTO @groups SELECT @groupId
    SET @groupId = (
        SELECT ParentTargetGroupId
        FROM PUBLIC_VIEWS.vComputerTargetGroup
        WHERE vComputerTargetGroup.ComputerTargetGroupId = @groupId
    )
END

-- Find all security updates which have been approved for install for at least
-- @Days to the specified target group (or one of it's parent groups)
--
-- create a table @updates loaded with UpdateId's where the TargetGroupId belongs to the @groups table
-- and the Action is 'Install'
-- and the MsrcSeverity is 'Not Null'
-- and the difference between the Microsoft creation data and Today is greater than @Days
--
DECLARE @updates AS TABLE (Id uniqueidentifier NOT NULL)
INSERT INTO @updates
SELECT vUpdate.UpdateId
FROM
    PUBLIC_VIEWS.vUpdate
    INNER JOIN PUBLIC_VIEWS.vUpdateApproval on vUpdateApproval.UpdateId = vUpdate.UpdateId
WHERE
    DATEDIFF (day, vUpdateApproval.CreationDate, GETUTCDATE()) > @Days
    AND vUpdate.MsrcSeverity is NOT NULL
    AND vUpdateApproval.Action = 'Install'
    AND vUpdateApproval.ComputerTargetGroupId IN (SELECT * FROM @groups)
    -- Can retrieve updates with important/critical MSRC ratings by replacing MsrcSeverity clause with this instead:
    -- AND vUpdate.MsrcSeverity in (’Critical’, ’Important’)
    -- values for MsrcSeverity include Unspecified, Moderate, Low, Critical and Important




IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp
SET NOCOUNT ON
SET ANSI_WARNINGS OFF


-- List of computers not in compliance for at least one updates
--
-- retrieve the computer name, kb article, and description
-- from several inner joins where
-- the ComputerTarget is an inner set of
-- a ComputerGroup which is an inner set of
-- an UpdateState which is an inner set of
-- an Update which is an inner set of
-- an Updates Group (from the previous query)
-- where the ComputerTarget has a State
-- and is in the 'definition' of 'Failed or Needed'
-- and ComputerTargetGroup Name is the original TargetGroup
--
--
SELECT

vComputerTargetGroup.Name AS Target_Group,
vComputerTarget.Name AS 'Computer_Name',
vComputerTarget.LastReportedStatusTime AS 'LastContactTime',
--CreationDate AS 'Microsoft_Released',
KnowledgebaseArticle AS 'KB_Article',
StateMap.Name AS 'Status',
SecurityBulletin AS 'Security_Bulletin',
MsrcSeverity AS 'Severity_Rating',
DefaultTitle AS Description


INTO #tmp

-- vComputerTarget.Name as 'Computer Name', vUpdate.KnowledgebaseArticle as 'KB Article', vUpdate.DefaultDescription as 'Update Title'
FROM PUBLIC_VIEWS.vComputerGroupMembership
    INNER JOIN PUBLIC_VIEWS.vComputerTarget on vComputerGroupMembership.ComputerTargetId = vComputerTarget.ComputerTargetId
    INNER JOIN PUBLIC_VIEWS.vComputerTargetGroup on vComputerGroupMembership.ComputerTargetGroupId = vComputerTargetGroup.ComputerTargetGroupId
    INNER JOIN PUBLIC_VIEWS.vUpdateInstallationInfoBasic on vUpdateInstallationInfoBasic.ComputerTargetId = vComputerTarget.ComputerTargetId
    INNER JOIN PUBLIC_VIEWS.fnUpdateInstallationStateMap() AS StateMap ON vUpdateInstallationInfoBasic.State = StateMap.Id
    INNER JOIN PUBLIC_VIEWS.vUpdate on vUpdate.UpdateId = vUpdateInstallationInfoBasic.UpdateId
    INNER JOIN @updates GROUPS on vUpdateInstallationInfoBasic.UpdateId = GROUPS.Id
WHERE vComputerTarget.ComputerTargetId = vUpdateInstallationInfoBasic.ComputerTargetId
    AND vUpdateInstallationInfoBasic.State in (2, 3, 5, 6)
    -- 0=Unknown, 1=NotApplicable, 2=NotInstalled, 3=Downloaded, 4=Installed, 5=Failed, 6=Installed Pending Reboot
    -- 2=NotInstalled, 3=Downloaded, 5=Failed, 6=Installed Pending Reboot (Definition of "Failed or Needed")
    AND vComputerTargetGroup.Name = @TargetGroup
-- would normally consolidate duplicate column values (but multiple Updates can have the same information)    
-- GROUP BY vComputerTarget.Name, vUpdate.KnowledgebaseArticle, vUpdate.DefaultDescription
-- ORDER BY 'Computer_Name' ASC, 'Microsoft_Released'
ORDER BY 'Computer_Name' ASC, 'Security_Bulletin'


EXECUTE SaveTableAsHTML @DBFetch = #tmp, @Header = 1, @CSS =
'table{font-family:"Lucida Sans Unicode", "Lucida Grande", Sans-Serif;font-size:12px;width:480px;text-align:left;border-collapse:collapse;margin:20px;}
 th{font-size:13px;font-weight:bold;background:#b9c9fe;border-top:4px solid #aabcfe;border-bottom:1px solid #fff;color:black;padding:8px;}
 td{background:#e8edff;border-bottom:1px solid #fff;color:#669;border-top:1px solid transparent;padding:8px;}
 tr:hover td{background:#d0dafd;color:#339;}'

RRD, Making Cacti Graph

Cacti is a modest PHP website app for creating and hosting pages of continuously updated graphs using SNMP, WBEM or Script data stored in RRD databases. Here's how to do that.

After logging into the main website:

A landing page presents a Tools menu down the Left side and a Modules or Apps menu at the top

Structurally a Graph begins with a registered Device. Registering it assigns it a Host Template, which assigns a default set of Data Queries and Graph Templates.

Begin by clicking "Devices" in the Tool menu then "Add":

Register a Device by minimally providing 4 pieces of information, then click "Create":

Ideally a "Host Template" will already exist for this Device, if one does not select "None" then create the device.

If there wasn't a Host Template for the Device, take a detour and create a new Host Template from existing Data Queries and Graph Templates and return to Edit the new "Device" and assign it the new Host Template and Save the change.

Create a new Host Template by clicking Host Templates in the Tool menu then "Add":

Create a new Host Template by providing a "friendly" name for it, then click "Create":

Ideally suitable "Data Queries" and "Graph Templates" will already exist and can be "Added", if they do not exist, make another set of detours to create them, then Edit this Host Template and choose to "Add" them to this Host Template and Save.

To create a new "Data Query"

Begin by clicking "Data Queries" in the Tool menu then "Add":

Then provide minimally 3 pieces of information, then click "Create":

The XML query file will have a  "Data Source Name" for each of the values returned by a "Data Query". SNMP (Indexed) Data Queries use the oid addresses in the XML query file to find a table and its fields and retrieve the values from the SNMP agent on a device.

A "Data Template" will describe how to store the "Data Source Name" values as "Data Source Items" in an RRD database as a "Data Source". 

Additional "Data Source Items" can be added by clicking "New" (the default "Data Source Name" will appear as 'ds' until it is overwritten or changed and the template is Saved). More than one "Data Source Name" will appear as a new Horizontal tab above the "Data Source Item" edit section.

Old "Data Source Items" can be modified by selecting their tab.

[warning] > once a "Data Template" has [Created] space for the "Data Source Items" in the RRD database files. The RRD files are not changeable. The "Data Template" can be modified but changes in the RRD files will not occur. The only way to redefine the RRD files (to remove or add more "Data Source Items") is to delete the Data Template and the RRD files and start over. This means any data stored in the RRD files will be lost.


To create a new "Data Template"

Begin by clicking "Data Templates" in the Tool menu then "Add":






Then provide minimally 3 pieces of information, then click "Create":

Continue by clicking "Add" in the "Data Source Items" section

A "Graph Template" will describe how to create a graph background and then graph items to put on to the background as layers on top of each other.

A "Graph Item" is added to a "Graph Template" by selecting a "Data Source" which is a compounded name formed by the "Data Template" name in which a "Data Source Name" is declared a "Data Source Item" then a hyphen and then the "Data Source Item" in parenthesis, i.e. DataTemplate - (DataSourceItem)


Begin by clicking "Graph Templates" in the Tool menu then "Add":





Then provide minimally 3 pieces of information, then click "Create":



Continue by clicking "Add" in the "Graph Items" section

Finally, [Associate] both "Data Queries" and "Graph Templates" to the new "Host" template

Then return to the "Devices" Tool menu, select the new "Device" and change its "Host Template" from [NONE] to [new Host Template] scroll to the bottom and click Save.


The new Data Queries and Graph Templates will added to the new Device

Click [Create Graphs for this Host] select a [Graph Template] and [Data Query] and check a box next to the [Data Source Item] to be graphed, then click "Create" to Save