I think the easiest way to explain what this blog entry is about is to just give a real life situation I had. I am using Microsoft’s LogParser (what a great tool) to pull all Windows event logs from different server into one SQL Server database. LogParser does this quite nicely. A side note: I noticed that by default –formatMsg is set to ON which causes end of line characters to go away. This can be annoying when you want to display formatted message from the Windows Event log. I highly recommend setting –formatMsg to OFF if you want the Message column in the Windows EventLog to be formatted as you see it in the built in Event Viewer. With that said, I am not going to go into how to do this because it is not important to this blog entry.
What is important is that you can assume you have a string and you want to use regular expressions to do a search and replace on keywords. One of the big problems is that if you create an actual delegate method for the MatchEvaluator to call it can only have one parameter and that is the one it is expect. This is an issue if you need to pass additional information such as the css style (in my case) to this method. An alternate way of doing this in .Net 2.0 or later is to take advantage of Anonymous methods (See http://msdn.microsoft.com/en-us/library/0yw3tz5k(VS.80).aspx for more information). Anonymous methods allow you to cross traditional scope boundaries of variables as use the variables of the method that has defined the Anonymous method.
public string HighlightKeyWord(string bodyOfText, string regex, string cssStyle)
{
try
{
bodyOfText = Regex.Replace(bodyOfText, regex,
new MatchEvaluator(
delegate(Match m) { return string.Format("<span class=\"{0}\">{1}</span>", cssStyle, m.Value); }
)
);
} // end try
catch (ArgumentException ex){ // Syntax error in the regular expression }
return bodyOfText;
}
Notice in this code cssStyle variable is referenced in the anonymous method even though it is defined in the parent method.
Here is an example of how to use this code:
public string HighlightUserName(string text)
{
return HighlightKeyWord(text, "mydomain\\\\\\w+", "highlightUserName");
}
In this example of usage, I assume that there is a css class called highlightUserName. It would change the text such that all places that have mydomain\myusername in the text would be changed to <span class=”highlightUserName”>mydomain\myusername</span> and thus show as formatted text in the browser.
Friday, May 9, 2008
Passing Additional Parameters to MatchEvaluator
Tuesday, April 22, 2008
Paste Unformatted Text in Word
If you copy and paste formatted text from different sources into MS Word you may have noticed that it usually will paste it in the original format. This is nice in some cases, but in most cases I find that I want the text to be unformatted (so that it uses the format of the paragraph that I am pasting it into in MS Word). To paste unformatted text all you have to do is go to the Edit Paste Special… menu item and when the dialog appears, select the “Unformatted Text” and click the OK button.
While this may be an easy solution it is a complete waste of time in comparison to just typing Control-V. If you do this a lot, you want to be able to paste unformatted text with a single key stroke. Here is how to do this.
In MS Word, go to Tools menu Macro Record New Macro…
In the dialog that appears enter a name for your macro. It can’t contain spaces, and really isn’t important other than it should describe what you are doing with it. In this case, I named my macro, PasteUnformattedText.
After entering the macro name, click the Keyboard button. This will open the Customize Keyboard dialog. In the Customize Keyboard window, type the key stroke you want to use to invoke the macro. I set mine to Control-Shift-V. Be sure to click the Assign button, then click the Close button.
You will then see a little floating window / palette. Use it to stop recording. At this point you have a macro that is invoked by a key stroke of your choice, but it doesn’t do anything.
Now let’s make the macro actually do something. Go to Tools menu Macro Macros… to bring up the Macros dialog.
Select your macro, and click the Edit button. This will open the MS Visual Basic code editor. You will see a method that is called whatever you called your macro. If there is any text (besides the ones with an apostrophe before them – these are comments) in the method body, delete it.
Your editor should look something like this…
Sub PasteUnformattedText()
‘
‘ PasteUnformattedText Macro
‘ Macro recorded 4/22/2008 by usb00528
End Sub
Add one line to this so that it looks like the following…
Sub PasteUnformattedText()
‘
‘ PasteUnformattedText Macro
‘ Macro recorded 4/22/2008 by usb00528
Selection.PasteAndFormat (wdFormatPlainText)
End Sub
Save and close the window. No give it a test by copying some formatted text, and pasting it using your keystroke.
Side Note
You don’t have to follow these exact steps to get this done. You can create a new macro, and then later go to Tools Customize… and then select the Macros item from the Categories list. Then select your macro, and click the Keyboard… button to get to the Customize Keyboard dialog you used as described above.
The bottom line is, you need to create a macro some how, put the one line of code in it, and assign a keystroke, or a toolbar item if you prefer.
Thursday, April 3, 2008
SharePoint Usage by List Type
SharePoint uses MS SQL Server and thus we can directly query this database to do some reporting. Below is a query that counts the number of entries for each type of list (Announcements, Contacts, Discussion Boards, Document Library, Events, Generic List, Issue List, Liks List, Image Library, InfoPath Form Library, Survey, Task List, Other). For example, if we look at the the sample results below there are 6932 documents in all the document libraries in all sites within SharePoint.
Select
case tp_servertemplate
when 104 then 'Announcement'
when 105 then 'Contacts'
when 108 then 'Discussion Boards'
when 101 then 'Document Library'
when 106 then 'Events'
when 100 then 'Generic List'
when 1100 then 'Issue List'
when 103 then 'Links List'
when 109 then 'Image Library'
when 115 then 'InfoPath Form Library'
when 102 then 'Survey'
when 107 then 'Task List'
else 'Other' end 'ListType',
sum(tp_itemcount) as EntryCount
from lists inner join webs ON lists.tp_webid = webs.Id
Where tp_servertemplate IN (104,105,108,101,
106,100,1100,103,109,115,102,107,120)
and tp_itemcount > 2 -- if there are only three then it is likely the sample data or a test record
group by tp_servertemplate
order by 2 desc
Example Results:
Type # of Entries
Document Library 6932
Generic List 400
Events 356
Issue List 328
Task List 305
Announcement 292
Links List 281
Discussion Boards 276
Survey 193
Image Library 147
Contacts 128
Other 18
InfoPath Form Library 3
While that is an interesting indicator of how much the feature is being used, it doesn’t really a good picture of how many sites are using the features. In other words, all 6932 documents could be on one site and no other site is using document libraries. Below is a query that gives the number of sites that are using each type of list.
select
count(distinct(webs.fullurl)) 'NumberOfSitesThatUseType',
case tp_servertemplate
when 104 then 'Announcement'
when 105 then 'Contacts'
When 108 then 'Discussion Boards'
when 101 then 'Document Library'
when 106 then 'Events'
when 100 then 'Generic List'
when 1100 then 'Issue List'
when 103 then 'Links List'
when 109 then 'Image Library'
when 115 then 'InfoPath Form Library'
when 102 then 'Survey'
when 107 then 'Task List'
else 'Other' end as Type
from lists inner join webs ON lists.tp_webid = webs.Id
Where tp_servertemplate IN (104,105,108,101,
106,100,1100,103,109,115,102,107,120)
--and tp_itemcount > 2
and FullUrl like 'sites/%'
group by tp_servertemplate
order by 'NumberOfSitesThatUseType' desc
Example Results:
Sites Type
135 Document Library
31 Links List
30 Announcement
27 Issue List
21 Task List
20 Discussion Boards
18 Events
13 Survey
10 Contacts
7 Image Library
7 Generic List
1 Other
List Sites
To get a list of all top level sites I recommend the following query:
select * from webs where FullUrl like 'sites/%'
To get a list of all sites (includes sites created below sites) I recommend the following query.
NOTE: This will also include sub sites like Meeting Work spaces, etc.
select * from webs w join sites s on (w.siteid = s.id) where w.FullUrl like 'sites/%'
Find sites that are not using a type of list
Sometimes you want to find all the sites that are NOT using a particular type of list. First thing you need to know is that each list type is stored as a number, not a human friendly string. Here are the mappings you will need to understand what the numbers mean. You will notice the above queries translate them using a case statement.
Note: the tp_servertemplate field can have the following values:
- 104 = Announcement
- 105 = Contacts List
- 108 = Discussion Boards
- 101 = Document Library
- 106 = Events
- 100 = Generic List
- 1100 = Issue List
- 103 = Links List
- 109 = Image Library
- 115 = InfoPath Form Library
- 102 = Survey List
- 107 = Task List
You will need to know these number for the below query. Just change the number in the query to the type of list you want to use.
select
webs.fullurl as [Site Relative Url],
webs.Title As [Site Title],
lists.tp_title As Title,
tp_description As Description,
tp_itemcount As [Total Item]
from lists inner join webs ON lists.tp_webid = webs.Id
Where tp_servertemplate = 105 -- Contact List
order by tp_itemcount desc
Wednesday, April 2, 2008
Starting and stopping Windows Service from the command line
In this article I will show how to restart (stop then start) a Windows Service from the command line. While it is true you can do all this from the user interface using the mouse, sometimes it is useful to be able to script restarting of services if a service is hung or down for some reason. An example of this is Apache Tomcat (assuming you installed it as a Windows Service and not to run from the command line to start with. I am going to use Apache Tomcat Windows Service as an example, but this article should apply to any Windows Service.
The below assumes you have already started a command prompt by going to Start Menu | Run… and typed cmd and then enter.
List Services
First the first thing you may want to know how to do is get a list of all started Windows services.
C:\>net start
These Windows services are started:
Apache Tomcat
Application Layer Gateway Service
Automatic Updates
Background Intelligent Transfer Service
COM+ Event System
Cryptographic Services
….
Stop a Service
Running the command below will stop the Apache Tomcat service. It is important to note the use of double-quotes here. You need them so that Apache Tomcat is treated as one parameter instead of two. The same goes for any other service with a space in its name. Also, notice the name use with the start command must match what is shown in the list above.
C:\>net stop “Apache Tomcat”
The Apache Tomcat service was stopped successfully.
Start a Service
Running the command below will start the Apache Tomcat service. The same basic rules apply as when you stopped the service.
C:\>net start “Apache Tomcat”
The Apache Tomcat service is starting.
The Apache Tomcat service was started successfully.
Restart a Service
Running the command below will restart (stop and immediately start) the Apache Tomcat service. The same basic rules apply as when you stopped the service.
C:\>net stop “Apache Tomcat”
The Apache Tomcat service was stopped successfully.
C:\>net start “Apache Tomcat”
The Apache Tomcat service is starting.
The Apache Tomcat service was started successfully.
Friday, March 21, 2008
Converting to a different timezone using C#
Thursday, March 20, 2008
Lessons learned with Web Services in Eclipse 6
<mappings>
<mapping>
<property name="datePropertyName" nillable="true"/>
<property name="anotherDatePropertyName" nillable="true"/>
</mapping>
</mappings>