Friday, April 19, 2013

SCCM Client Verbose Logging

Though SCCM logs are pretty comprehensive but it's some how short of the additional informaiton that Techies need to get more insight into a error.

The procedure below will enable you to just do that.

Apply the changes to the machine which you need the verbose logging.

Enabling Debug and Verbose Logging

To enable debug logging after installation, create the following registry key:


To enable verbose logging after installation, change the following value to 0. You will need to right click on the @Global key and change permissions to allow the current user to change the data in the key.



HKLM\Software\Wow6432Node\Microsoft\CCM .


Thursday, April 4, 2013

Symantec Endpoint Management Server Reporting

I was looking for a query that will enable us to query the Symantec Endpoint Management Server's Database for the SEP client's information for Virus Definitions, Last Scan Time, Last Virus Detected time, Current infection state of the SEP Client but was unable to find any.
Hence I have put together a little SQL query that is to be reference against the SEM5 database which will do the job.

, dateadd(second, max(L1.CReation_time)/1000 + 8*60*60, '19700101') ' MAchine Creation Time'
--,max(L1.CReation_time) 'Machine Creation Time'
,dateadd(second, max(L1.LAST_UPDATE_TIME)/1000 + 8*60*60, '19700101') 'Last Machine Status Update Time'
 , Case when  L1.[INFECTED] = 1 then 'Yes'
when L1.[INFECTED] = 0 then 'No'
End as 'System Infected'
 ,dateadd(second, max(L1.[LAST_SCAN_TIME])/1000 + 8*60*60, '19700101') 'Last Scan Time'
      ,dateadd(second, max(L1.[LAST_VIRUS_TIME])/1000 + 8*60*60, '19700101') 'Last Virus Detected'
      ,max(L1.version) 'Current Definition Version'
      ,max(L1.patterndate) 'Current Pattern Date'

SELECT distinct CID.Computer_NAME
, avengine_onoff
  FROM [sem5_DB].[dbo].[SEM_Agent]
  left join dbo.SEM_COMPUTER CID on SEM_AGENT.computer_ID = CID.Computer_ID
left join dbo.PATTERN PAT on SEM_Agent.pattern_IDX = PAT.pattern_IDX
where CID.deleted = 0
group by L1.Computer_name,L1.infected,L1.LAST_UPDATE_TIME

Thursday, October 4, 2012

How to un-protect Excel 2007 spreadsheets without knowing the password

When Microsoft introduced Excel 2007, they introduced new file types – we all know them by now: xlsx, xlsm, xltx, etc. These file types are often referred to as Open XML. That’s because the new file types are essentially packages that contain XML files. If you take an xlsx file and change the extension to zip, you’ll be able to see all the xml documents that make up your Excel file.

The new Open XML file types come with lots of benefits. One of the major benefits is that you can change the content and properties of an Excel 2007 file simply by manipulating the XML documents that make it up.

Well, while playing with the Open XML files, I discovered that you can remove spreadsheet protection simply by applying a simple edit to the xml within the Excel file.

Having said that, people do protect their excel worksheet for a variety of reasons, So what can we do if we need to amend something on this worksheet?
I decide that I want to un-protect this sheet, but I don’t know the password. Because this is Excel 2007, teh spreadsheet protection can be removed from within the XML.
The procedures below will show you have to go about doing this.

Step 1: Make a backup of your file in case you really monkey it up.
Step 2: Change the file extension to from filename.xlsx to
Step 3: Extract the contents of the zip file.
Step 4: Go to the extracted files and navigate to the xml for the target sheet (found in the ‘xl\worksheets’ directory)

Step 5: Open the target sheet’s xml document using an XML editor (You can use notepad and search for the field "sheetProtection") 
Step 6: Find the ‘sheetProtection’ tag and remove the entire line.

Step 7: Save the edited xml document and replace the old xml document found in the original zip file.
Step 8: Change the extension back to xlsx.
Step 9: Viola! The sheet is no longer protected :)