Analytics – Steve Novoselac's Blog

With the latest “Polar Vortex” or whatever that is happening, EVERYONE is talking about the weather. Everyone always talks about how it has been this cold many times, etc, etc. “It was colder in my day” – ok. Well prove it!

So I took a look at the NOAA data you can get here http://www.ncdc.noaa.gov/cdo-web/ and got an extract to CSV for my hometown of Chisholm, MN (actually the Hibbing/Chisholm airport since it has data from 1962 to today)

I downloaded the CSV, opened in Excel 2013 and imported into Power Query. I think did some formatting to get the date parts and a date field, and converted the “tenths of a degree of Celsius” to Fahrenheit. Then started analyzing.

I will have to refresh this after this cold spell, because it only has data to 1/1/2014 and these last few days have been cold, but, not the coldest.

Back when I was 16, in 1996, there was a stretch of days in January that were COLD. The data supports this. First I took all the days with a Low temp of UNDER -35 degrees F.

You can see, there are a bunch of days in Jan/Feb 1996 that were UNDER -35 Degrees F. So then I copied that pivot and expanded on that date range to see all the days.

Pretty dang cold from 1/19/1996 to 2/4/1996. Lowest day was -50 Degrees F. Average of -31 Degrees F. Of course these are “real” temps, it was even colder with wind chill. These last 3-4 days of -20 to -40 are cold, but not sure they are colder than in Jan 1996. We will see when it is all said and done.

If you can’t remember how cold it was, NOAA, Excel and Power Query can remind you. 🙂

I have the spreadsheet up on Skydrive. http://sdrv.ms/1gBwMPL

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.