Wednesday, October 29, 2014

SCCM 2007 Patch Report Per Collection

Here is a slightly modified Patch Compliance report that allows for quick analysis for either a Security Bulletin or a KB Article number.  The default report in SCCM only allows you to focus on 1 OS at a time whereas this allows you to have a nice little report for a particular Security Bulletin or KB number.  (Because of the OR statement, you can also have both parameters filled to view both)

declare @collid varchar(20); set @collid = 'SMS0001'
declare @BulletinID varchar(20); set @BulletinID = 'MS12-048'
declare @ArticleID varchar(20); set @ArticleID = ''

declare @CI table(CI_ID int primary key)
if @ArticleID = '' set @ArticleID = NULL;
if @BulletinID = '' set @BulletinID = NULL;


insert @CI(CI_ID)
select ci.CI_ID
from v_UpdateCIs ci
where ci.IsHidden=0 
and (ci.ArticleID = @ArticleID or ci.BulletinID = @BulletinID)

select
Vendor=ven.CategoryInstanceName,
UpdateClassification=cls.CategoryInstanceName,
ArticleID,
BulletinID,
Title,
Approved=case when exists(select 1 from v_CITargetedCollections where CI_ID=ci.CI_ID and CollectionID=@CollID) then '*' else '' end,
Present=NumPresent,
Missing=NumMissing,
NotApplicable=NumNotApplicable,
Unknown=NumUnknown,
Total=NumTotal,
PCompliant=convert(numeric(5,2), isnull((NumPresent+NumNotApplicable)*100.0/nullif(NumTotal, 0), 100)),
PNotCompliant=convert(numeric(5,2), isnull((NumMissing)*100.0/nullif(NumTotal, 0), 0)),
PUnknown=convert(numeric(5,2), isnull((NumUnknown)*100.0/nullif(NumTotal, 0), 0)),
CollectionID=@CollID,
UniqueUpdateID=CI_UniqueID,
InformationURL=InfoURL
from @CI ci
left join v_UpdateSummaryPerCollection cs on cs.CI_ID=ci.CI_ID and cs.CollectionID=@CollID
left join v_UpdateInfo ui on ui.CI_ID=ci.CI_ID
left join v_CICategoryInfo_All ven on ven.CI_ID=ci.CI_ID and ven.CategoryTypeName='Company'
left join v_CICategoryInfo_All cls on cls.CI_ID=ci.CI_ID and cls.CategoryTypeName='UpdateClassification'
order by 1, 2, 3

Wednesday, October 8, 2014

Track Down Malicious Executables Using SCCM 2007

I have been chasing down some malicious code and found a very useful SCCM 2007 table.

declare @filename varchar(20)
set @filename = 'wiupdat.exe'

select 
sys.Name0 [Resource Name],
sys.AD_Site_Name0 [Resource Site Name],
sys.Operating_System_Name_and0 [Resource OS],
sys.Resource_Domain_OR_Workgr0 [Resource Domain],
sys.User_Name0 as [Resource Username],
app.LastUserName0 [App Username],
app.LastUsedTime0 [App Last Used Time],
app.TimeStamp [App Database Timestamp],
app.ExplorerFileName0 [App FileName],
app.OriginalFileName0 [App Original FileName],
app.CompanyName0 [App Company Name],
app.FileDescription0 [App FileDescription],
app.FileVersion0 [App File Version],
app.FolderPath0 [App Execution Path],
app.FilePropertiesHash0 [App Hash],
app.SoftwarePropertiesHash0 [App Software Properties Hash],
app.FileSize0 [App FileSize],
app.LaunchCount0 [App Launch Count],
app.ProductLanguage0 [App Product Language Code]
from v_GS_CCM_RECENTLY_USED_APPS app
left join v_R_System sys on sys.ResourceID = app.ResourceID
where ExplorerFileName0 = @filename
or OriginalFileName0 = @filename
order by TimeStamp

I've been using SCCM report "Software 07C - Recently used executables on a specific computer" to pull a list of recently run executables on a known infected workstation.  Make sure to display all executables by choosing 'N'.  Then look for the offending executable.  Plug in that executable name into the SQL query or build a report with a Prompt name of 'filename' and remove the top two lines in the SQL shown above.

A problem I see is that the Hash function used in SCCM 2007 does not seem to be MD5 or SHA1.  It is still useful assuming it creates a unique signature but we cannot use that information to find out information from https://www.virustotal.com 


Thursday, August 7, 2014

Find Duplicate Serial Numbers SCCM 2007

We are using SCCM to feed information into an asset management system (CMDB).  We keep trying to stress how SCCM is not a very good asset management tool, especially SCCM 2007.  I have heard the CM12 makes some improvements.

select
sys.ResourceID,
sys.Name0,
sys.Active0,
sys.Obsolete0,
bios.SerialNumber0
from v_R_System sys
left join v_GS_PC_BIOS bios on bios.ResourceID = sys.ResourceID
where bios.SerialNumber0 in (select SerialNumber0 from v_GS_PC_BIOS group by SerialNumber0 having COUNT(*) > 1)
and sys.Obsolete0 = 0
order by SerialNumber0

Thursday, June 19, 2014

Temporarlily Disable Domain Group Policies

Every once in a while I need to do an A-B test on how something behaves with a Group Policy.  Under XP, I had found a tool that worked pretty well but when trying to find it again, I ran into this article: http://superuser.com/questions/379908/how-to-clear-or-remove-domain-applied-group-policy-settings-after-leaving-the-do

Here is the script that I created based on that information.

REM Remove Domain Group Policies
REM
REM Reference: http://superuser.com/questions/379908/how-to-clear-or-remove-domain-applied-group-policy-settings-after-leaving-the-do
REM Backing up the registry pieces
reg export "HKLM\Software\Policies\Microsoft" %userprofile%\desktop\gp_backup1.reg /y
reg export "HKCU\Software\Policies\Microsoft" %userprofile%\desktop\gp_backup2.reg /y
reg export "HKCU\Software\Microsoft\Windows\CurrentVersion\Group Policy Objects" %userprofile%\desktop\gp_backup3.reg /y
reg export "HKCU\Software\Microsoft\Windows\CurrentVersion\Policies" %userprofile%\desktop\gp_backup4.reg /y


REM Delete all Domain Group Policies
REM can re-enable them with a gpupdate /force
REM or you can double-click on all registry backups
reg delete "HKLM\Software\Policies\Microsoft" /va /f
reg delete "HKCU\Software\Policies\Microsoft" /va /f
reg delete "HKCU\Software\Microsoft\Windows\CurrentVersion\Group Policy Objects" /va /f
reg delete "HKCU\Software\Microsoft\Windows\CurrentVersion\Policies" /va /f 

Friday, May 16, 2014

McAfee Query Method

McAfee EPO 4.5 and 4.6 cannot easily create a report that shows a random list of computers.  I often get asked to report on a list of servers or workstations that have no relationship to one another for audit purposes.

You can create a direct SQL query to the backend database but I found this strange method work using VIM on Windows.  Note: I have taught myself how to use VIM. It was not easy and has a huge learning curve.  The hardest step was understanding how to change mode to exit the application.  There are a lot of tutorials on the Internet but don't be surprised to feel very uncomfortable for a long time.

Add this to the end of your _vimrc file.  (:e $HOME/_vimrc).  I keep the same vimrc file uploaded to a network share and backed up.

<quote>
" Escape/unescape & ( ) McAfee query definition entities in range (default current line).
function! McAfeeEntities(line1, line2, action)
  let search = @/
  let range = 'silent ' . a:line1 . ',' . a:line2
  if a:action == 0  " must convert &amp; last
    execute range . 'sno/+/ /eg'
    execute range . 'sno/%22/"/eg'
    execute range . 'sno/%28/(/eg'
    execute range . 'sno/%29/)/eg'
  else              " must convert & first
    execute range . 'sno/ /+/eg'
    execute range . 'sno/"/%22/eg'
    execute range . 'sno/(/%28/eg'
    execute range . 'sno/)/%29/eg'
  endif
  nohl
  let @/ = search
endfunction
command! -range -nargs=1 MEntities call McAfeeEntities(<line1>, <line2>, <args>)
noremap <silent> \m :MEntities 0<CR>
noremap <silent> \M :MEntities 1<CR>
</quote>

Steps to modify a McAfee report.

  1. Create a dummy report that does whatever you need and have it filter by system name matching 1-3 computers.
  2. Export report into xml format
  3. Edit in vim
    1. Around line 8
    2. Press keystroke from the original mode: \m
      1. That rewrites the line substituting %28 for (
    3. Insert a return on the part starting with: ( eq EPOLeafNode.NodeName
    4. Insert a return near the end of the line: ) )</property>
    5. Delete the line with all the computernames
    6. Insert and paste list of computers
    7. Search and replace to the end of the last computer in this example 589 is the last computer
      1. :8,589s/^/( eq EPOLeafNode.NodeName "/g
      2. :8,589s/\n/" ) /g
    8. Insert at the beginning of line 8 and merge with line 7 with a delete
    9. merge the end of the line with  ) )</property>
    10. I found out that McAfee can handle the file without the correct %28 syntax.  If necessary you can use \M to reset the line to the way it was before.  Caution with the <property name="conditionURI"> tag, it will get the wrong output like this ++<property+name=%22conditionURI%22> and will have to be fixed.
  4. Import into McAfee
  5. If you don't import that list of computernames into the system as empty records then your report will only show found items follow the next step to fix that.
  6. Copy the list of computers into EPO using "New Systems" with the "

Maybe McAfee EPO will fix their report system in the next iteration.

Thursday, May 15, 2014

SCCM Client Health

This looked very helpful.

I am currently working on trying to come up with a process and methodology for SCCM Client Health. It seems to start with a good Asset Management strategy which is not what SCCM 2007, at least, offers. CM12 should improve some Asset Management but Asset Management seems to be a big data solution for a problem of tracking information from multiple sources. First you need to include Financial data from the purchase of equipment, then you need to track heartbeats from the objects from multiple sources. We are trying to use LDAP/ Active Directory lastLogonTimestamp, McAfee Last Communication, and SCCM Heartbeats.

I'm just now starting to play with WMIDiag from Microsoft. I'm wondering if it would make sense to implement WMIDiag.vbs into a DCM to give a success, error, or warning.

    0 = SUCCESS

    1 = ERROR

    2 = WARNING

    3 = Command Line Parameter errors

    4 = User Declined (Clicked the Cancel button when getting a consent prompt)



<quote>

Tracking resource usage of WMI

By default the core WMI service lives in the shared Network Services instance of scvhost.exe. This can make debugging or identifying resource issues a little challenging. As a general rule of thumb I run (and recommend to customers) that they keep WMI separated into its own instance of svchost.

On XP/Server 2003 this can be accomplished automatically via the following case sensitive command:

    RUNDLL32.EXE %Systemroot%\SYSTEM32\WBEM\WMISVC.DLL,MoveToAlone

For Vista and up this is done with

         winmgmt /standalonehost

WMI Troubleshooting Tips - System Center Configuration Manager Team Blog - Site Home - TechNet Blogs:

Wednesday, May 7, 2014

Using Netsh Commands Instead of Telnet to Test Firewall Connections

Every once in a while I need to validate that a firewall is either working or not working.  Prior to Win7 and Windows 2008 I would just use telnet to the name and port of the service I wanted to test.  Now I either need to create a change request to install on a server or give up.



I may have found a cool built-in tool to Windows 2008 that can accomplish the same result: netsh



I knew this was a very powerful tool that allows for network trace dumps without installing any 3rd party tools on a server.  I started looking for a way that netsh could be used to open a connection and report whether or not the connection was successful.



Next time try running this command:

netsh trace diagnose scenario=internetclient namedAttribute url=http://www.google.com
You can also add report=yes to have the tool automatically generate some configuration details into an ETL and CAB file for use with Microsoft Message Analyzer but the information didn't seem especially helpful other than it does some network dump (capture=yes)





Successful response:

c:\>netsh trace diagnose scenario=internetclient namedAttribute url=http://www.google.com
Diagnosing 'internetclient' ... done
Root causes found: 0


Failure response on a pingable device with http access blocked:

c:\>netsh trace diagnose scenario=internetclient namedAttribute url=http://www.google.com
Diagnosing 'internetclient' ... done
Root causes found: 1


Root cause #1
--------------
 website (www.google.com) is online but isn't responding to connection attempts.

The remote computer isn't responding to connections on port 80, possibly due to
firewall or security policy settings, or because it might be temporarily unavail
able. Windows couldn't find any problems with the firewall on your computer.
Repairs available: 1


    Repair #1
    ----------
    Contact the service provider or owner of the remote system for further assis
tance, or try again later


I did find one locked down server that failed with this error:

C:\>netsh trace diagnose scenario=internetclient namedAttribute url=http://www.google.com
Diagnosing 'internetclient' ... done
 Network Diagnostics failed (error=0x80070511).


Netsh Commands for Network Trace in Windows Server 2008 R2 and Windows 7:


P.S. Possibly much easier:
try this powershell command it work for me.
(new-object Net.Sockets.TcpClient).Connect("google.com", 80)

Thursday, January 9, 2014

Dealing with 1618 Errors in a Task Sequence

A co-worker came up with this brilliant email about a recent issue when trying to install McAfee 4.6 in an OSD task sequence:

<quote>
The error 1618 usually means that there is another installation “already in progress”.  Now this doesn’t always mean that there really is one in progress, but there may still be a “PendingFileRenameOperations”.  In my task sequence, I have a reboot occur after the installation of “McAfee EPO Agent 4.6” as well as a reboot after the install of “McAfee Virus Scan Enterprise 8.8.02004”.

A quick way to verify if indeed a reboot is needed in between would be to check for the existence of the following registry key after the “McAfee EPO Agent 4.6” is installed:

[HKLM\SYSTEM\CurrentControlSet\Control\SessionManager]
      PendingFileRenameOperations  (If this exists, then it is a good bet that you need to reboot the computer before trying to install anything else)

Also, you if desired you can copy the code below and paste it into a VBS.  This will help to determine if a reboot is required.  I have used it in the past for patches, but should work as well for packages.

=================  Copy Below  =================
Set objSysInfo = CreateObject("Microsoft.Update.SystemInfo")
Wscript.Echo "Reboot required: " & objSysInfo.RebootRequired

=================  Copy Above  =================
</quote>