Logparser

Recently I wrote about the enumerate command that I use. I was looking at it just now because I wanted to enumerate one particular check across the whole domain: I wanted to report on the events that show a user being enrolled into local Administrators on their workstation -- and irregular admins generally.

This is a big deal for me -- has been for a long time, it's a big deal for more and more sites, and it should be for everyone. Admin privilege is the difference between spyware installing in a profile (and even now, most of them don't attempt to do this) and installing dangerously and ineradicably as a rootkit. Admin privilege is what allows  users to harm their builds with downloaded software or messing around with the branding or mapping. But alas, it's also the easy solution to a lot of problems and desktop team members -- admins themselves -- are often tempted to pass it on to a user in trouble so they can get on to the next call.

The control for this is to find out when it happens and follow up very promptly, next day, with the admin concerned. But you need to know it's happened, and the only ways I know how to tell it's happened are a) a listing of the group membership on every machine -- which doesn't, crucially, tell you when it was done, or b) the 536 message in the event log. So it's the message we want, provided we can pick and decode the content out of the rather unhelpful format. To hold the desktop team to account, we want to look at the new messages each day, making a nice report of all the suspect events.

We already have a tool -- enumerate -- which will run a command against every machine. So now we need a command that will append relevant log events on to a report. "But" I hear you cry, "but what about your RSA Envision log SELM appliance? Isn't that ideally suited to this task?" Well yes, my dears, it certainly is, but you see, it's licenced per event source. I have enough licences for all the infrastructure and about half of production servers, but none at all for workstations. We need something at a better price point, like free.

Microsoft is a better source of free (as in beer) software than you might expect, and they have the tool for this job: Logparser; motto: "the world is your database." In outline, Logparser converts and presents logs of many sorts and some odder stuff like registry and filesystem contents as queryable lists. The queries can be simple or complex: I started with

SELECT
 Strings 
FROM
 \\mypc\security 
WHERE
 EventID=536 

But you need to work a little harder to get a script parameterised enough to be enumerated across all domain members and produce a good outcome. The beauty of Logparser is that it's mature enough to deliver -- it really is a proper log analysis tool. I expected to write auxiliary scripts to break out the data, decode SIDs, accumulate the report as a CSV, and keep track of the last log read on each machine, but in fact all this can be done in Logparser script language or command line options.

-- admin.sql
-- Logparser query.
-- Accumulate events where a user has been made a member of admins or power users
-- You might want to enumerate this across the entire domain 
-- (omit domain controllers which have different messages)
-- Command would be like 
-- logparser 
--  -o:TSV -oSeparator:space -headers:OFF -fileMode:0 
--  -iCheckPoint:MYPC.lpc 
--  file:admin.sql?oFile=2009-10-18_AdminChanges+sMachine=MYPC
-- The checkpoint file is named for the machine, and output is appended to "today's" file.

SELECT 
-- Generating "hand" CSV rather than the CSV output type -- more flexible to do it in SELECT and USING
 
 -- the ms from the :ll aren't populated but it stops Excel dropping the seconds
 TO_STRING(TimeGenerated, '\"yyyy-MM-dd hh:mm:ss:ll\",')AS Date, 
 strcat(ComputerName,',') AS Computer,
 Resolve_SID (SID) AS Admin,
 Action,
 Resolve_SID (SIDUser) AS User,   
 Group

USING 
-- Do the token parsing in USING: break the bits we want out of the -|%{SID}|... tokens in Strings
 Extract_Token(Strings,1,'|') AS SUr,  -- User SID
 Extract_Token(Strings,2,'|') AS GroupN, -- (Localised for free -- more friendly)
 Extract_Token(Strings,3,'|') AS GroupD, 
 Extract_Token(Strings,4,'|') AS SGp,  -- the Group SID 
 
 SUBSTR(SUr,2,SUB(STRLEN(SUr), 3)) AS SIDUser,  -- break raw User SID out of the %{SID}
 
 CASE EventID WHEN 636 THEN 'enrolled' WHEN 637 THEN 'removed' END AS Action, -- Friendly EventIDs
 
 -- Output like "into BUILTIN\Administrators"
 STRCAT(
  STRCAT(
   CASE EventID WHEN 636 THEN 'into ' WHEN 637 THEN 'from ' END, 
   GroupD),
  STRCAT( '\\', GroupN)) AS Group
 
INTO
-- Need the -fileMode:0 (append) on the command line to avoid overwriting with each machine.
-- For a log for each machine then the command line above would let you use %Machine% in the name.
 %oFile%.csv 

FROM 
-- FROM the machine security log  --  This is -i:EVT. 
-- Don't use the SID resolve option because you may want to limit to particular built-in groups, but 
-- and S-1-5-32-544 is easier than working out internationalised versions of "Administrators"
 \\%sMachine%\Security 

WHERE 
 ((EventID=636) or (EventID=637)) and       -- 636 enroll, 637 remove
 (SID<>'S-1-5-18') and           -- Ignore actions by local System
 (                -- Ignore boring groups
  ((SGp = '%{S-1-5-32-544}') or (SGp = '%{S-1-5-32-547}')) -- Only want Admins or P Users
 -- Optionally don't report Domain admin (check your SID) being made admin, because it happens in every log!
 -- and 
 -- (SIDUser <> 'S-1-5-21-4163168572-49618088-4072775208-512') 
 )

Remaining niggles are petty. some machines have corrupt SELs -- logparser fails at end of log, so it never writes a checkpoint so the entire file is processed every time. But this can be fixed by saving and emptying the offending log. And I suppose it would be nice if it enumerated the domain itself, but that doesn't trouble me.

Apparently V3 is due out. I cannot wait.