SSRS – Get Extensive Web Analytics Reports from SharePoint

August 31, 2014

Well SharePoint offers OOB some basic Site Web Analytics reports.

If you go to the Site Settings ….



All of the data is stored in the WSS_Logging database.


When you run this Qry you get all information needed to build your own statistics reports.


[Sourcecode language=”SQL”]
SELECT RequestUsage.RowId ,RequestUsage.LogTime ,RequestUsage.UserLogin,
RequestUsage.CorrelationId ,RequestUsage.SiteId ,RequestUsage.SiteUrl,
RequestUsage.WebId ,RequestUsage.WebUrl ,RequestUsage.DocumentPath,
RequestUsage.ContentTypeId ,RequestUsage.QueryString,RequestUsage.BytesConsumed,
RequestUsage.HttpStatus ,RequestUsage.ReferrerUrl,
RequestUsage.ReferrerQueryString ,RequestUsage.Browser,
RequestUsage.UserAgent ,RequestUsage.UserAddress,
RequestUsage.RequestCount ,RequestUsage.QueryCount,
RequestUsage.QueryDurationSum ,RequestUsage.ServiceCallCount,
RequestUsage.ServiceCallDurationSum ,RequestUsage.OperationCount,
RequestUsage.Duration ,RequestUsage.RequestType ,RequestUsage.Title,

FROM RequestUsage

WHERE RequestUsage.SiteUrl NOT LIKE N”

AND RequestUsage.RequestType IN (N’GET’, N’POST’)
AND (RequestUsage.DocumentPath NOT LIKE N’%/_layout%’

OR RequestUsage.DocumentPath LIKE N’%/_layouts/upload%’
OR RequestUsage.DocumentPath LIKE N’%/_layouts/download%’)

AND RequestUsage.UserLogin NOT LIKE N’%spfarmacc’
AND RequestUsage.UserLogin NOT LIKE N’%syst%’
AND RequestUsage.UserLogin NOT LIKE N’%admin%’
AND RequestUsage.DocumentPath NOT LIKE N’%.asmx’
AND RequestUsage.DocumentPath NOT LIKE N’%.dll’
AND RequestUsage.DocumentPath NOT LIKE N’%_vti_%’
AND RequestUsage.DocumentPath NOT LIKE N’%siteassets%’
AND RequestUsage.DocumentPath NOT LIKE N’%default%’
AND RequestUsage.DocumentPath NOT LIKE N’%.axd’
AND RequestUsage.DocumentPath NOT LIKE N’%.js’

Order by documentpath






Enjoy !

Windows – Monitor the IIS logon Sessions and more …

August 29, 2014

I find it very hard to monitor who is logged on the which session in Windows.

Using the Event Viewer you get these Logon types possible:


Logon Type Description
2 Interactive (logon at keyboard and screen of system) Windows 2000 records Terminal Services logon as this type rather than Type 10.
3 Network (i.e. connection to shared folder on this computer from elsewhere on network or IIS logon – Never logged by 528 on W2k and forward. See event 540)
4 Batch (i.e. scheduled task)
5 Service (Service startup)
6 Unlock (i.e. unnattended workstation with password protected screen saver)
7 NetworkCleartext (Logon with credentials sent in the clear text. Most often indicates a logon to IIS with “basic authentication”) See this article for more information.
8 NewCredentials
10 RemoteInteractive (Terminal Services, Remote Desktop or Remote Assistance)
11 CachedInteractive (logon with cached domain credentials such as when logging on to a laptop when away from the network)

And even more difficult to monitor it for IIS using SharePoint.

Solution is given by the famous MS SysInternal Team.

They created the tool LogOnSessions.exe –p


Exporting this to a text a log file using the > “C:\Temp\LogonSessions.log” parameter give you the data to be used for later treatment.

Or use Powershell Winking smile


$Cmd ="C:\Apps\MS SysInternal\logonsessions.exe"

$cmdOutput = & $Cmd 2>&1

# echo "--------"
# echo $cmdOutput
# $cmdOutput | Tee-Object -Variable scriptOutput | % { "processing Output : $_ " }
# echo "--------"
echo ""
echo "Number of Objects $($scriptOutput.Count) Too many let's filter :)"
echo ""

$cmdOutput | Tee-Object -Variable scriptOutput | % { $_} | `

    Where-object {$_ -like '*UPN*' `
    -and $_ -notlike  "*Administrator*" `
    -and $_ -notlike  "*spFarm*" `
    -and $_ -notlike "*SRV*"} `
        |  Sort-Object -descending | Get-Unique

Output looks like this giving you only the remote users Login Accounts  (UPN)Smile


Enjoy !

Windows – Install TTF Barcode Fonts

August 28, 2014

I had to install some barcode fonts to be used in Word and Excel for Label printing.

After installing it will look like this.


Some of the fonts are free to download from then net, like (Barcode 39 TTF), others are not.

Installing is normally a piece of cake. Right click the font file and choose Install.


But in some PC’s the file association has changed because of other applications being installed.

So there is no Install Sad smile


To find the associated application on the machine, use assoc.exe


Indeed you see it here :



To find what was the original file association, have a look here.

You see it was associated with Windows Fonts Viewer.

Being here : C:\Windows\System32\Fontview.exe

Solution :

Let fix it using AutoIT


FileExtAssoc("ttf", "Fontview.exe")

func FileExtAssoc($sExt, $sApplication)
    RunWait(@COMSPEC & " /c ASSOC ." & $sExt & "=ExTest", "", @SW_HIDE)
    RunWait(@COMSPEC & " /c FTYPE ExTest=" & $sApplication , "", @SW_HIDE)
    MsgBox(0,"File Extension Application Association",'"' & $sExt & '"is now asscoiated with "' & $sApplication & '"',3)

Or using the GUI way like this.




Enjoy !

SharePoint – Web Services Data integration (SOAP)

August 27, 2014

In my previous post I explained you how to consume a Web Service using Autoit and Powershell.

Now we can see how to access the data in SharePoint.

First we need to connect to the Web Service Provider.

1. Open SP Designer and go to Data Sources. And add a new SOAP Service Connection.


Fill in the Web Service URL


2. Next create a new Web Part Page

First go to Site Pages and create a new page 


Let’t go for a Web Part Page


3. Open it to insert the new SOAP Connection.


Once the page is open.

4. Go the Insert menu and Data View option


And browse down to the new connection in this case USZip Web Service.


5. Insert the connection in the Web Part Zone


6. Save it and look at the result.


That’s it as simple as that Winking smile


No let’s see that we can filter it based on a Query String.

Open the Page you created again in FD, and create :


1. First create a Parameter first called “CITY”  and a Query String variable called “CITY”.


Default value is an option.

2. Next create a Filter.

Open the Data Source Again and remove the Default value “New York” again


Click on the FILTER menu item


it should look like this in the end.


Save the page and test it using the Query string Parameter “City”

/sites/sandbox/SitePages/Zip%20Soap%20Test.aspx?City=New York

Enjoy !

AutoIT – Visual Studio Light

August 24, 2014

Sometime you find some nice code on the internet like VB.Net or C#.

But it is of no use if it is not compiled Sad smile

As explained in this post you can compile you own .NET COM objects on the fly.

But if you want to do it using a GUI interface without installing the full blown Visual Studio. You can use this GUI Wrapper – Visual Studio Light


The first option is for compiling GUI Applications EXE’s.

The second one is to create DLL’s (Libraries) which you can use later on in a .Net application.

The third one is for compiling Console Applications EXE’s.

You can download it here

PS :  This wrapper is for VB.Net but can easily be changed to use C# replace the vbc.exe by csc.exe

If you run it you get an EXE or DLL



This is the code :

imports System.Windows.Forms

' The name of the module must match the name of the code item
' created in the hosting application via CreateItem.
module Script
  sub Main()
    MessageBox.Show("hi From DotNET !!")
  end sub
end module

How to run .Net Assemblies in Powershell see here

How to run .Net Assemblies form Memory in Powershell see here

Enjoy !

AutoIT – How to compile your own .NET COM Object

August 24, 2014

Wouldn’t it be nice to compile your own .NET COM DLL and use it on AutoIT.

Well we don’t need much to do this. Everything is available on your system.

Let’s see :

First we need of course some VB.Net code or C# that will exposes some methods or properties.

Imports System.Collections.Generic
Imports System.Text
Imports System.Runtime.InteropServices

Namespace myDotNetLibrary
  <classinterface   (CLASSINTERFACETYPE.AUTODUAL)> _
  Public Class myDotNetClass
    Private myProperty As String

    Public Sub New()
    End Sub

    Public Function myDotNetMethod(input As String) As String
      Return "Hello " & input
    End Function

    Public Property myDotNetProperty() As String
        Return myProperty
      End Get
      Set(ByVal value As String)
        myProperty = value
      End Set
    End Property

  End Class
End Namespace

Next we need a .Net compiler ? Ha but that is default available on each system that has a .Net Framework installed Smile

Go and have a look here : C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\vbc.exe

The vbc.exe is the compiler for the .Net 2.0 framework. But you will find as well the compiler for .NET 3.0 and 4.0 if you have that framework installed.

So now we need to compile the code to a DLL and register it.


; Framework 2.0
;$vbc = "C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\vbc.exe" ;  ; check the path of your version
;$RegAsm = "C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\RegAsm.exe" ; check the path of your version
; Framework 4.0
$vbc = "C:\WINDOWS\Microsoft.NET\Framework\v4.0.30319\vbc.exe" ;  ; check the path of your version
$RegAsm = "C:\WINDOWS\Microsoft.NET\Framework\v4.0.30319\RegAsm.exe" ; check the path of your version

RunWait($vbc & " /target:library hello.vb", @ScriptDir, @SW_HIDE) ; compile the .net DLL
RunWait($RegAsm & " /codebase hello.dll", @ScriptDir, @SW_HIDE) ; register the .net DLL

$obj = ObjCreate("myDotNetLibrary.myDotNetClass")
$obj.myDotNetProperty = " ... from DotNet to the AutoIt World !"

MsgBox(0,"My Own DotNet Object " , $obj.myDotNetMethod($obj.myDotNetProperty) & @CRLF)

RunWait($RegAsm & " /unregister hello.dll", @ScriptDir, @SW_HIDE) ; unregister the .net DLL

Ones you run the code it will compile the code to a DLL and register it in the GAC

Or if you want don’t want to register the COM Assembly to the GAC, you can use this approach 😉



And run it as a COM object, where AU3 will access it’s methods and properties.


You can see if the COM object registered correctly here :


For a C# compiler you need to look for the csc.exe instead of the vbc.exe


PowerShell – read Excel the fast way

August 24, 2014

In Powershell there are 2 ways of reading excel.

Fast and slow.

The Slow way is using the Excel COM interface.

$ExcelWB = new-object -comobject excel.application

$ExcelWB.Visible = $True
$Workbook = $ExcelWB.Workbooks.Open("C:\_\Apps\_PowerShell\test.xls") 
$Worksheet = $Workbook.Worksheets.Item(1)

The Fast way is using the OLEDB approach

$strFileName = "C:\_\Apps\_PowerShell\test.xls"
$strSheetName = 'Sheet1$'

$strProvider = "Provider=Microsoft.Jet.OLEDB.4.0"
$strDataSource = "Data Source = $strFileName"
$strExtend = "Extended Properties=Excel 8.0"

$strQuery = "Select * from [$strSheetName]"

$objConn = New-Object System.Data.OleDb.OleDbConnection("$strProvider;$strDataSource;$strExtend")
$sqlCommand = New-Object System.Data.OleDb.OleDbCommand($strQuery)

$sqlCommand.Connection = $objConn

$DataReader = $sqlCommand.ExecuteReader()


"Querying File $Excel ..."

 $Excel = $DataReader[0].Tostring() 
 for ($i = 0; $i -lt $DBCounter; $i++) {
  ($DataReader.GetName($i) + " " + $DataReader.GetValue($i))
  echo ' '   


So if you have a need for speed, you know which one to choose. Smile

PowerShell – Module for AutoIT

August 24, 2014

In the last Beta releases AutoIT is shipped with a .NET Assembly as well as a Powershell Module

You can find it here :


First import the module and run this example.


# Import the module manfiest
Import-Module (${Env:ProgramFiles(x86)} + "\AutoIt3\Beta\AutoItX\AutoItX3.psd1")



Send-AU3Key("I'm in notepad");

$winHandle = Get-AU3WinHandle("Untitled");

Sleep -Seconds 2


# Get the list of AutoItX cmdlets
Get-Command *AU3*

# Get detailed help for a particular cmdlet
Get-Help Get-AU3WinHandle

Getting help on the cmdlets and commands using the Get-Command and Get-Help will list you all needed information to get started.



Even if the functionality is limited at the moment, it is a good sign AU3 is moving towards PS. Smile

PowerShell – Export HTML content to CSV or Excel

August 22, 2014

Let’s say you are interested in downloading some HTML content from a website based on some filters.

PowerShell it the tool to use. It just takes a few lines Smile

let’s take this blog as an example.

I would like to get hold of all Title Posts of the category ‘PowerShell’

The URL for this is this

If you run it in a browser you get all the post content listed. But we need only the Title ?

Therefor we need to use the DOM (Document Object Model). In order to find what we need.

Here we go :

Open up the IE browser and press F12. This will open up the DOM explorer.

In the right corner fill in a search key word of a post Title


Next inspect the section needed.

Next we are going to need a DOM method to access the data, in this case.

getElementsByTagName = “h2”

Next I wanted to get the text of the Title.

So we can use the method


here is the coded plus some in between debugging information for you to get more information out of this example.


$URI =""
$HTML= Invoke-WebRequest -uri $URI

# echo $HTML

# $HTML | Get-Member

# $HTML.ParsedHtml | Get-Member

Check out these Members


$Ret = $HTML.ParsedHtml.getElementsByTagName("h2" #| Where { $_.className  }
echo $Ret.innerText

$Ret = $HTML.ParsedHtml.getElementsByTagName("h2" | Where { [int]$_.className.trim.length -eq 0  } | % { ([String]$_.sourceindex + " " + $_.innerText) }
$Ret | Select-Object @{Name='Name';Expression={$_}} | Export-Csv ($Env:USERPROFILE+"\Desktop\Test.csv") -NoTypeInformation

Invoke-item ($Env:USERPROFILE+"\Desktop\Test.csv")

echo $Ret

Next I got a bit too much information. As you can see the last items are not post but are the headers of the right widgets.


So we have to filter them out. See code

className.trim.length -eq 0


Finally we got what we wanted. And we can now export it to a CSV file if we want.


PowerShell – The ‘Microsoft.Jet.OLEDB.4.0’ provider is not registered on the local machine

August 22, 2014

If you get this error

Exception calling “Open” with “0” argument(s): “The ‘Microsoft.Jet.OLEDB.4.0’ provider is not registered on the local machine.”


Microsoft OLEDB Drivers work on 32 or 64 bit architecture, running Windows XP, Vista, 7 or Windows 8.

Some of the drivers are only 32 Bit compatible ! like this above one.

Additionally on some Windows 64 bit systems, it is possible that some of the Microsoft OLEDB DLLs have not been registered.

Solution No. 1

Check that you are running the 32 bit PowerShell Console or ISE. In the past with Powershell v1.0 you could start it form here :









But later on after upgrading to v3.0 it disappeared.There are still 2 hints where you can see if you are running x64 or x86.After starting the ISE


you will see it here in the Title Bar it says … x86image

The command window you see the location SysWOW64

You can start it from here :


The Solution No. 2.
The solution is to manually register those DLLs.
go to Start->Run and type cmd
this starts the Command Prompt

Go to a special folder
cd c:\windows\sysWOW64

Now you need to register the OLE DB 4.0 DLLs by typing these commands and pressing return after each. Might be only the first 2 will register, no problem.

regsvr32 Msjetoledb40.dll
regsvr32 Msjet40.dll
regsvr32 Mswstr10.dll
regsvr32 Msjter40.dll
regsvr32 Msjint40.dll


If errors appear.

Install AccessDatabaseEngine.exe from the MS site and try again.

x86 for MS Office x32 bit and

x64 for MS Office x64 bit


Hope this will help solving some frustrations.