Query AD using T-SQL

So I’m working on a small interface that needs to check certain computers in our enterprise for file changes, and fortunately these specific computers are kept in a group nested inside our AD structure. My thought was, if I can use a SQL query to go get that list of machines, put the results in a table, then have the interface read from that table it would always have a current list of these machines, with no maintenance required for adding and removing these PCs. Here’s what I came up with:

First, the database needs to have ad hoc queries enabled. This is done in the Surface Area Configuration for Features section of Surface Area Configuration on the affected DB server. You’ll need this in order to use commands such as OPENROWSET and OPENDATASOURCE. In this case, we’re going to use OPENROWSET. Set this first, then use the following query:

SELECT distinguishedName
FROM 
OPENROWSET('ADsDSOObject','adsdatasource'; 'DOMAINusername';'password', 'SELECT distinguishedName FROM ''LDAP://server'' WHERE memberOf=''cn=groupname,ou=OU_Container_Group_Lives_In,dc=server,dc=com'' AND objectCategory=''computer''')

Obviously you’ll want to substitute the DOMAIN\username and password, as well as the LDAP://server and the memberOf sections for your network’s info, but this structure will work. Feel free to substitute distinguishedName for another property if you want different results.

Eric

Eric Oszakiewski is a professional software developer based in Scottsdale, AZ with over 35 years of IT experience, and 19 years Native American Gaming experience. He is currently working as a Sr .Net/SharePoint Developer for General Motors, and also as a consultant.

More Posts - Website

Follow Me:
TwitterFacebookLinkedInGoogle PlusYouTube

No-Cost training on Channel 9

MSDN’s Channel 9 is offering no cost training from their learning center. Learn at your own pace with these on-demand videos. Here are a few links to some of their content:

Microsoft Office 2010 Workshop
Windows Server 2008 R2 Developer Training
SharePoint 2010 Developer
Visual Studio 2010 and .Net Framework 4 Training Course
Windows 7 Developer Training

Also, here are a few links to some no-cost Exchange Server 2010 E-Learning courses:
Clinic 6899: Exploring Features of Exchange Server 2010 (2 hours)
Clinic 6900: Introduction to Exchange Server 2010 (one hour)
Clinic 6901: Exchange Server 2010 in an Enterprise (one hour)

Eric

Eric Oszakiewski is a professional software developer based in Scottsdale, AZ with over 35 years of IT experience, and 19 years Native American Gaming experience. He is currently working as a Sr .Net/SharePoint Developer for General Motors, and also as a consultant.

More Posts - Website

Follow Me:
TwitterFacebookLinkedInGoogle PlusYouTube

Excellent article on getting AD info using C#

I’m always getting, updating, and validating against AD info at my company, and today I needed to pull several different schema attributes in one method, and kept running into more and more trouble. Code got bigger and bigger, and I thought there had to be a way to simplify this. I then ran across a CodeProject article definitely worth mentioning. The author, Rajasekhara Sambangi does an excellent job of demonstrating a simplified way of pulling just about anything from AD by passing in the SearchResult object and the property name you’re searching for into a basic method, and the result is the value of the key! The link to the article is here.

Eric

Eric Oszakiewski is a professional software developer based in Scottsdale, AZ with over 35 years of IT experience, and 19 years Native American Gaming experience. He is currently working as a Sr .Net/SharePoint Developer for General Motors, and also as a consultant.

More Posts - Website

Follow Me:
TwitterFacebookLinkedInGoogle PlusYouTube

Returning the Nth weekday in SQL…and in InfoPath

Ok, after much research I found a forum that had a posting that demonstrated how to get the Nth chosen weekday of a month using T-SQL. Here’s the code:

DECLARE @date datetime
DECLARE @weekday int
DECLARE @day datetime
DECLARE @number int
DECLARE @WhichOne int

SELECT @WhichOne = 2 -- Set this to the week you want to grab
SELECT @number = 1
SELECT @weekday = 0
SELECT @date = getDate() -- Change to dateadd(month, 1, getDate()) for next month, etc.

WHILE @weekday  4 -- Change this value to be the day of the week you're looking for
BEGIN
    SELECT @day = (CAST(STR(MONTH(@date)) + '/' + STR(@number) + '/'+ STR(YEAR(@date)) AS DATETIME))
    SELECT @weekday = DATEPART(weekday, @day)
    SELECT @number = @number + 1
END

SELECT DATEADD(d, (@WhichOne - 1) * 7, @day)

Many MANY thanks to Tara for this posting! The link to the forum post is here.

Now, how did I get InfoPath to see this? In the InfoPath form, I added a DatePicker control. Next I had to create a Data Source, point to a server and database on our network somewhere (doesn’t matter where). Click “Edit SQL” and add this code to that box. Click through the wizard and right click on the control, choosing Properties. Click the Function design button next to Default Value, Click Insert Field or Group, and under Data Source, choose your newly created data source. Expand out the tree until you see your result set field and choose it. Don’t forget to export your data connection to a Data Connection library on your SharePoint server and site, or the form won’t work.

Eric

Eric Oszakiewski is a professional software developer based in Scottsdale, AZ with over 35 years of IT experience, and 19 years Native American Gaming experience. He is currently working as a Sr .Net/SharePoint Developer for General Motors, and also as a consultant.

More Posts - Website

Follow Me:
TwitterFacebookLinkedInGoogle PlusYouTube

Calculating Nth weekday of next month in Excel

I won’t normally post Excel stuff on here, but while researching an XSLT/Javascript function I was determined to get it right in Excel first, so I understood it better, and definitely wanted to document it so I could pull it back up later!

In this example, I need to know the second Wednesday of next month, because that’s when server maintenance is every month. But what day is it? Here’s the Excel formula:

=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1+7*2)-WEEKDAY(DATE(YEAR(TODAY(),MONTH(TODAY())+1,8-4))

Notice the DAY argument of the first DATE function in the formula says “1+7*2”. The “2” represents the week of the month, so if you wanted the third week change it to “3”, etc.

Next, notice the DAY argument of the second DATE function in the formula says “8-4”. The “4” represents the day of the week, where 1 is Sunday, 2 is Monday, etc. So if you wanted Fridays only, change it to 6.

This worked in Excel, now to figure out how to do this in JavaScript and/or XSLT. Ultimately I need it in XSLT for InfoPath, and the generic formula functions in InfoPath don’t come close to this. Once I figure it out I’ll post it here.

Eric

Eric Oszakiewski is a professional software developer based in Scottsdale, AZ with over 35 years of IT experience, and 19 years Native American Gaming experience. He is currently working as a Sr .Net/SharePoint Developer for General Motors, and also as a consultant.

More Posts - Website

Follow Me:
TwitterFacebookLinkedInGoogle PlusYouTube