Using Perl to access a Microsoft SMS Database

dru145 has asked for the wisdom of the Perl Monks concerning the following question:

Fellow Monks,

Our company uses SMS for pretty much everything desktop related. From a security standpoint it's essential to my job for searching for computer names, user names, and ip addresses. I finally received read only access to our SMS database after much fighting. I was planning on writing a CGI interface using DBI as the backend to connect and query the SMS SQL database, but then I seen this on Microsoft's site: Microsoft Propaganda basically saying they don't want any non-microsoft product interacting with their product so they are not going to publish the database schema. I looked at the database and it must have 300 or so tables. I've been able to figure out where some of the information is that I'm looking for, but I was wondering if anyone has tried something similar? Microsoft recommends using Excel (which is what my SMS admin wants me to use), but I feel it's primative to use a spreadsheet for querying a database as well as extremely slow.

Any help would be appreaciated. I searched this site and couldn't find anything and also searched CPAN for any related modules, but no luck

Thanks,
Dru
Another satisfied monk.

Comment on Using Perl to access a Microsoft SMS Database

Replies are listed 'Best First'.
Re: Using Perl to access a Microsoft SMS Database by JayBonci (Curate) on May 17, 2002 at 20:34 UTC
SMS? Yikes. I've only played with it a little bit, but I've never actually messed with the interfaces, especially with perl or anything like that. So, with that in mind, this is what I could find for you. Basically, if there isn't a good DBD driver for something Microsoft makes, always check COM objects and Win32::OLE. MSDN is your buddy: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sms/refsms10_1wdu.asp That lists the COM interfaces to the SMS console. That's as close an alternative that I could come up with. I don't know it if will work if you have read-only access to the database. The only DBI module that I see in CPAN that does SQL Server is DBIx::MSSQLReporter. <nt><nt> It's a heck of a task. You may also be able to get away with using Win32::OLE with Excel.Application. Again, I've never tried these items, but it's a place to start. Good luck --jb	[reply]
Re: Using Perl to access a Microsoft SMS Database by P0w3rK!d (Pilgrim) on May 17, 2002 at 20:52 UTC
Dru, Typical Microsoft response (Re: MS propaganda)... I would suggest trying to write up some APIs or access some preexisting COM objects (they must be out there, somewhere?). Then have your Perl code call and manipulate them. If you can "see" the database, then you should be able to construct what you need. I do not think the schema which change that drastically...especially for common information. Good Luck. -P0w3rK!d	[reply]