VBScript Array

So, there I am, thinking about how great it would be to…well, you can already see my first mistake, right? I’ll have to check my list of duties and responsibilities here, but I am certain that there is no line item that says “think”. Unless you count that last one that says “other duties as assigned”, but that would assume people here would want to have me think about things. The last thought I remember having was about two months ago, when I thought to myself “how bad can one shot of Jager be?”

Anyway, back to my thought. As some of you may know, I am falling in love with SCOM. Right now I am still trying to court SCOM, and I am doing my best to overlook some any and all faults that I find, no matter how small (or large). I try to look at it like an arranged marriage, and hope that SCOM and I will live happily ever after.

A few weeks back I had an idea that I would want to create a report that would list the sys admins for any particular instance. That way, anyone needing to gather some info for audit purposes would be able to run the report without having to send a request to our team. Now, how to build such a report? Well, I figured there must be a way to use SCOM to gather the data and populate a table in a dedicated repository. So, I set out to create a way to get the syslogins from each instance using VBScript. Sound easy, right?

I thought so, but I kept getting tripped up on the sid column. See, the sid is a varbinary column. And VBScript cannot just take that column and reinsert. Why? Well, VBScript sees the varbinary as an array of bytes. Therefore, you need to treat the sid with special gloves. I spent far too long trying to get this to work. I could not find anything online, which led me to believe that the answer is probably very obvious. Unless, of course, I was the first person in the world trying to move a varbinary column using VBScript, which I found to be unlikely. So, after some time, I finally came across the answer.

First, I build a recordset by doing a select against the syslogins table:

strSQLQuery = “SELECT @@servername as instance,sid,status,createdate,updatedate,accdate,totcpu,totio,spacelimit,timelimit,” & _”resultlimit,name,dbname,password,language,denylogin,hasaccess,isntname,isntgroup,isntuser,sysadmin,securityadmin,” & _”serveradmin,setupadmin,processadmin,diskadmin,dbcreator,bulkadmin,loginname, getdate() as timestamp FROM master.dbo.syslogins”Set rsSyslogins = cn.execute(strSQLQuery)

You will note that I am getting the distinct instance name using @@servername, and I am using getdate() in order to capture the time of the select. This info is important for me later on as I want to build a report based upon the name of the instance. The getdate() will allow for me to effectively archive the data every so many days as necessary.

Next, I needed to create a parameter object in order to capture the sid in its natural state:

set sid = CreateObject(“ADODB.Parameter”)
sid.Type = 204
sid.Size = 85
sid.Direction = 1
sid.Value = varSid
cmd.Parameters.Append sid

Next, I need to build a command text to execute. This part was the key for me, and it took a while for me to understand how to utilize this command text. The trick was using the ‘?’ placeholder for the parameter. Now, this is very basic VBScript syntax, but for whatever reason I could not see the final solution although it was in front of me the whole time. So, I build my command text, then execute:

cmd.CommandText=”INSERT INTO ” & strTargetDB & “.dbo.” & strTargetTable & ” (instance,sid,status,createdate,updatedate,accdate,totcpu,totio,spacelimit,timelimit,resultlimit,name,dbname,password,language,denylogin,hasaccess,isntname,isntgroup,isntuser,sysadmin,securityadmin,serveradmin,setupadmin,processadmin,diskadmin,dbcreator,bulkadmin,loginname, timestamp) ” & _ “VALUES(‘” & strInstance & “‘, ?, ” & strStatus & “, ‘” & strCreatedate & “‘, ‘” & strUpdatedate & “‘, ‘” & strAccdate & “‘, ” & strTotcpu & “, ” & strTotio & “, ” & strSpacelimit & “, ” & strTimelimit & “, ” & strResultlimit & “, ‘” & strName & “‘, ‘” & strDbname & “‘, ‘” & varPwd & “‘, ‘” & strLanguage & “‘, ” & strDenylogin & “, ” & strHasaccess & “, ” & strIsntname & “, ” & strIsntgroup & “, ” & strIsntuser & “, ” & strSysadmin & “, ” & strSecurityadmin & “, ” & strServeradmin & “, ” & strSetupadmin & “, ” & strProcessadmin & “, ” & strDiskadmin & “, ” & strDbcreator & “, ” & strBulkadmin & “, ‘” & strLoginname & “‘, ‘” & strTimestamp & “‘)”

cmd.Execute

And it worked great! Until it tried to do a second row, and then it failed with a general error that could mean one of six things. Do you know those types of errors that Microsoft gives you? I bet you do, and I was stuck facing one of them. Eventually (i.e., after much too much time was spent), I suddenly found myself thinking of a possible solution.

See, I was trying to pass an array. And I did so for the first row, then I got an error. At some point it struck me that what I needed to do was to clear out the parameter. Chances are, on the first pass I was inserting a one dimensional array. And that was easy. But the second pass was actually appending to the first array, making it two dimensional, and thus I was getting an error because I could not insert a two dimensional array into a varbinary column in my table. Sure enough, I added this one line:

cmd.Parameters.Delete 0

That cleaned out the parameter object, and the code worked perfectly. I am now in the process of taking the loginnames, passing it to a sub, and getting the details from sp_helplogins and putting that into a table for the sysusers. And it will all be run from SCOM, once a day, or so I hope.

The end result should give me a nice collection of login and user information. We already gather this info, but I am trying to migrate a lot of our stuff into SCOM. As an added bonus we will be able to create a report that can filter and display all current sys admins for an instance (or anything else that is desired).

So…that is why you have not heard from me in a few weeks. While this was quite frustrating, it was also very rewarding as I was forced to brush up on my VBScript, figure out a way to incorporate SCOM into our information collection, and finally felt the satisfaction at the end having solved the issue with the varbinary column.

The real question is: how will I feel tomorrow if someone points out a much easi
er way to get the same info?

Miserable first, most likely…but my knowledge of arrays in VBScript will be with me forever.