Analyzing ADFS IIS Logs

If you are using Active Directory Federation and you want to see what users are logging in when to what external service, you can analyze the ADFS server IIS logs. It is pretty straightforward since it is just IIS.

First, get to your ADFS box, get to the IIS log directory, usually something like “C:WindowsSystem32LogFilesW3SVC1” and grab those logs.

Install LogParser on your machine.

Now, you can write sql type queries against your logs. For ADFS logs, we don’t care so much about many of the columns, but primarily username and date, maybe the URI for filtering, maybe the referrer or the user agent to see what browsers your users are using, but to get say, unique logins per day for a given service, we just need the date, username and URI.

Remember the date is probably UTC so you need to use a function to convert, or leave as is if you want, and everything is pretty much all relative depending on how accurate you want things to be. hint: TO_TIMESTAMP(date, time) AS utc-timestamp, TO_LOCALTIME(utc-timestamp) AS local-timestamp

Now, here is the LogParser query:

logparser "SELECT DISTINCT cs-username, date INTO FROM WHERE cs-username NULL and cs-uri-query LIKE '%your service%'"

Note in the statement the output path and your log path, change to what yours are. Also, the LIKE statement. For example, to query for Microsoft Dynamics CRM Online, I used

LIKE ‘%dynamicscrm%’

Run that query, then open the .csv you exported to. Format the data as a table, pivot it by user, pivot by date. Get the unique number of days using a date diff, analyze logins per day, logins per user. Tie to Active Directory (using Power Query) to add some dimension attributes like title or department and very quickly you can analyze what users, departments etc are using your service.

Geeky/Programming Product Reviews

Tool of the Day: Sysinternals ProcMon

Funny how you might not EVER use a given tool, and some days you might end up using it twice. Sysinternals Procmon was that tool today.

It is the successor from old utils from Sysinternals – Filemon and Regmon.

What does it do? It monitors all processes and services and watches what they are doing on your system. File, Registry, etc, etc. Open/Close, Read/Write, what user, status, etc. You can filter and pause and find out pretty much anything going on in windows.

So early in the day, running into a website issue, not loading in IIS. No idea what is going on. Fire up Procmon and filter to the website directory on disk. Lo and behold, the site is trying to impersonate a user, and that user doesn’t have permissions. The site still didn’t work, and if I would have dug a bit more with Procmon, would have found that the user impersonating on the site also needed rights to the ASP.NET Temporary files, but after seeing the impersonation and the site still not working, I guessed it didn’t have rights to the temp folder.

Things like the scenario above I have seen people waste a support call with Microsoft with.

Second thing today. Trying to install a extension to SSRS. The installer isn’t even seeing that SSRS is installed, yet it clearly is and functioning correctly on the box. Some how the installer must be reading something or looking somewhere and not finding something. Procmon to the rescue. Fire it up, watch msiexec.exe. Seeing registry reads, it finds the SSRS instance names, then looks to a registry area with that instance name and tries to find more details. Was failing on finding the details because there was no reg keys in the second location (for whatever reason). But there was info in the first location, the same info it was looking for. I exported it out, changed the reg path of the keys, and imported. Re-ran setup and it found the instance this time and I could install the extension.

Without Procmon would have been flying blind or just guessing randomly on what to do. Could have been hours on tech support with a company, or again, a support call with Microsoft.

Procmon saved the day. Check it out and try to use where applicable in your day to day troubleshooting.


SQL Server 2005 Reporting Services – Fix Slow Loading On First Report Load

Setting up a new instance of SQL Server Reporting Services (SSRS), and getting things configured. What I noticed is that the first time I hit the SSRS site, it would take forever to load, then it would be fine as I used it, and then later after no use, I could come back and it would be slow again till it loaded once, then fast.

My first thing to think of was the App Pool settings in IIS, so I checked them..

The first setting on the performance tab of the app pool, “Shutdown worker processes after being idle for (time in minutes)” is checked by default, and set to 20 minutes. Sounds about right, my SSRS instance would get slow after some time of non-use (20+ minutes)

Uncheck that, and you are good to go. Of course if you have tons of usage, then there is no need for changing this, but on a site you might be building up slowly, you would see the effects of this right away.

Blogging Geeky/Programming

WordPress on IIS 6.0, Windows 2003 – Some Tweaks (URL Rewrite and SMTP)

OK so I lied. Everything wasn’t working after I set up my WordPress blog on IIS 6.0 on Windows 2003. Two things were broke: SMTP (email from the blog) and URL Rewrites/Permalinks.

SMTP worked through IIS just fine, I could telnet and send email out, but from WordPress, it was getting a 501 error. From what I could tell, it was the way that WordPress was formatting the outgoing “mail from” with <From Name> . IIS was bombing on the <> in the string (I am pretty sure). I tried some tweaking on WordPress, some plugins for SMTP, couldn’t get them to work. I tried MailEnable, I couldn’t get that to work either. I did fine hMailServer and that does work after configuring it. Like IIS, I have it just set to local, and port 25 is blocked anyway so outside relays won’t work.

The other thing that wasn’t working was my permalinks. By default WordPress doesn’t use permalinks with a rewrite, it just uses ?p=<post id>, but I have mine changed to year/month/day/title. In Linux/Apache, there is mod_rewrite for rewriting URLs, but in IIS 6.0 there isn’t anything (IIS 7 has more functionality). I could have gotten a rewrite DLL and wrote rules, but I found a sweet plugin that does it for me, especially for WordPress permalinks –

Other than that, everything seems to be working fine. On other news, I have been looking into other blogging engines, comparing/contrasting, etc. Not sure where I will go with that though.


Server Move: Hosting My Own Site(s)

Well, I have been on HostMySite for a few years now, and I just wasn’t liking it. I didn’t have control, and couldn’t do everything I wanted to. Last week I signed up with ServerBeach and I have my own dedicated server. I am running Windows 2003, IIS, SQL, MySQL, PHP, etc.

I changed for now my DNS to GoDaddy from Active-Domain, since GoDaddy has SPF records (for Google talk federation, etc). I might change that up here in the future as well.

Transferring a domain takes way to long! Took like 4 days overall. Also, I first went with EasyCGI , a VPS solution, but the box couldn’t get to the Internet! I put in tickets, etc, they just wouldn’t fix it, so I canceled and went to ServerBeach. I am liking them so far, they had the server provisioned in about 4 hours.

One thing I can do now as well, is write a site for myself and import all the log files (Firewall, SMTP, All the Sites, FTP, etc) and have a nice little reporting solution.

ServerBeach is good, very professional. If you want to sign up, use referrer code BW27Q37B6D  (

To get PHP/MySQL/WordPress, I loosely followed these three tutorials

How to Install PHP on IIS 6.0
How to Install MySQL On IIS 6.0
How to Install WordPress on IIS 6.0

For my ASP.NET Site, it was just copying over files and configuring IIS a little. For my blog, I used this dbbackup plugin ( and then once I had my WordPress files over, and MySQL/PHP running, I connected to the instance of a DB I created and ran the SQL backup script, which created everything the way I needed it for WordPress.

For Mail, I use the built in SMTP in Windows 2003. I just had to turn on relaying, but yeah, spammers, so I just allowed the local server to send mail, and it works. Tested it using this ( and it worked.

Other than that, its pretty much basic server admin stuff. Firewall, FTP, etc. Everything is running smooth… for now 🙂

I will just have to make sure I have backups!


Business Intelligence Geeky/Programming SQLServerPedia Syndication

Reporting Services: Can't Uninstall – The setup failed to read IIsMimeMap table. The error code is -2147024893

Ran into this error tonight trying to uninstall SQL Server Reporting Services. Not sure if it is just Vista, or XP and other OS’s as well, but the fix is to stop IIS and then re-run the Uninstall.


PHP, IIS7, Vista, VS2005

Tonight, just for the heck of it, I decided to get PHP running on my machine. I am running Vista (not SP1 beta). I already have IIS installed, so that pre-req was taken care of.

First thing, I made sure I had everything turned on..

Start->Run->Programs and Features->Turn Windows Features on or off…


I made sure CGI and ISAPI was checked. I then downloaded and installed PHP (

When I went through the install, I changed the path from Program Files to Windows (read online that it would be better – rights – if in Program Files it doesn’t have the same rights as in Windows)

Then, I opened IIS7, and  went to “Handler Mappings” and clicked “Add Script Map”


It will prompt you when you hit ok to add to CGI stuff, hit Yes.

Then you can make a test PHP page in your wwwroot, put <?php  phpInfo();  ?> in there and it should work!

Now, as an added bonus, I wanted to use VS2005 IDE to develop PHP scripts, and found VS.PHP ( which lets you make PHP projects in VS2005. There is a 30 day trial, but then it is 99 dollars. I installed it and tried it , pretty awesome.

I don’t know how much PHP programming I will do, but every now and then it is nice to dabble into other technologies and code up something, or just keep up on the latest trends.

Technorati tags: , , , , , , , , , ,

Vista, IIS7, Classic ASP, and Microsoft Access – How to Get It Working!

First, make sure ASP Classic Is Installed on Vista, good instructions here

Make sure your app is set up as a virtual directory, etc, etc.

Then, you need to set permissions and run commands and change around settings just to get anywhere. Just for kicks I made the IUSR for the directory have modify rights, but I am not sure if that did anything, oh well, I am not changing it back.

I ran the default page, and got this

An error occurred on the server when processing the URL. Please contact the system administrator

Nice huh? Well, I went and turned off Friendly HTTP Errors in IE Internet Options, that didn’t give me anymore info, so, found this:

cscript %systemdrive%inetpubadminiscriptsadsutil.vbs set w3svc/AspScriptErrorSentToBrowser true     

That will send the actual errors to the browser, ok good.

Now, getting this:

Microsoft OLE DB Provider for ODBC Drivers error ‘80004005’

Found out you need to run 3 more commands to get this to work… ok

icacls %windir%serviceprofilesnetworkserviceAppDataLocalTemp /grant Users:(CI)(S,WD,AD,X)

icacls %windir%serviceprofilesnetworkserviceAppDataLocalTemp /grant “CREATOR OWNER”:(OI)(CI)(IO)(F)


%windir%system32inetsrvappcmd set config -section:applicationPools /[name=’DefaultAppPool’].processModel.loadUserProfile:false

After all that nonsense, the page/app actually worked! What a debacle…

Technorati tags: , , , , , , , , , ,

Installing IIS – Cannot find STAXMEM.DL_

Ahhh, IIS, gotta love it. Nothing can ever be easy.

So, as I am trying to install IIS on my Dell that I just reformatted, I get this:

Cannot find the file STAXMEM.DL_ in my C:i386 dir. hmm….

well, after a little looking, if you run this command

esentutl /p %windir%securitydatabasesecedit.sdb

If you then retry the install, it should work


IIS Integrated Authentication – Still Getting a Login Prompt

If you setup a intranet or internal site, and setup the security as Integrated Windows Authentication – and you still have Anonymous Access on as well, but are still getting a prompt, it is most likely you are using a domain name for your site (like – The way to avoid getting a windows login prompt when hitting the site is to add the site to your trusted sites in your local zone in your IE tools->options, security tab.