'**********************************************************************                                      
'**  File: ACSEventCount.vbs                 
'**  Auth: Jesse.Harris
'**  Date: 9/28/07
'**  
'**  Test: cscript ACSEventCount.vbs <SQLServer1\Instance1>   Multiple servers can be added if seperated by a comma  (No Spaces) 
'**  
'**  Summary:  Script logs onto SQL Server holding AuditCollectionDB and gets the most current partition table, then queries that partition table to get event counts.  Events are then submited to OpsMgr07 
'**  
'**  Notes:  
'**      Reference MSDN RunTime Scripts via http://msdn2.microsoft.com/en-us/library/bb437498.aspx
'**      Reference udocumented use of PropertyBags here:  http://systemcenterforum.org/howto/ 

'To use this in a RULE
'1)  Create a Collection -> Probe Based -> Script (Performance) Rule  | Select appropriate management pack 
'2) Enter rule name |  Apply it to appropriate target (the OpsMgr07 RMS server uses the action account as the default action account for scripts)
'3) Select interval 20 minutes seemed to be appropriate
'4) Copy / Paste Script -> Edit Parameters 
'5) For Performance mapping use the following:
'Object = $Data/Property[@Name='oObject']$
'Counter = $Data/Property[@Name='oCounter']$
'Instance = $Data/Property[@Name='oInstance']$ 
'Value = $Data/Property[@Name='oValue']$


Dim oAPI
Dim oArgs

Set oAPI = CreateObject("MOM.ScriptAPI")
Set oArgs = WScript.Arguments

if oArgs.Count <> 1 Then
    ' If the script is called without the required arguments,create an event and then quit.   ref method here:  http://msdn2.microsoft.com/en-us/library/bb437630.aspx 
    Call oAPI.LogScriptEvent("ACSEventCount.vbs",10100,1,"ACSEventCount.vbs was called with the wrong number of arguments.  " & oArgs.Count)
    Wscript.echo "ACSEventCount.vbs",10100,1," ACSEventCount.vbs was called with the wrong number of arguments.  " & oArgs.Count
    Wscript.Quit -1
End If



sSQLServerInstances = split(oArgs(0),",")


sQueryPartition = "select PartitionID from dbo.dtPartition order by PartitionStartTime Desc"
sQueryTableCount = "select count(*) as myCount from ADTServer.dvall_" '& then we add the partition to the end
    

for i = 0 to ubound(sSQLServerInstances) 
    set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")

    sSQL = "Provider=SQLOLEDB;Data Source=" & sSQLServerInstances(i) & ";Integrated Security=SSPI;Application Name=OpsMgr07;Initial Catalog=OperationsManagerAC;" 
    cn.Open sSQL
    If cn.State <> 1 Then
        Call oAPI.LogScriptEvent("ACSEventCount.vbs",10101,2,"SQL Connection Error sSQL=" & sSQL & "  ADODB.Connection cn State: " & cn.State)
        wscript.echo "ACSEventCount.vbs",10101,2,"SQL Connection Error sSQL=" & sSQL & "  ADODB.Connection cn State: " & cn.State
    Else
        sQuery = "select top(1) PartitionID from dbo.dtPartition order by PartitionStartTime Desc"
        rs.Open sQuery,cn,1,1
        strPartitionID = rs("PartitionID")
        rs.Close
        sQuery = sQueryTableCount & strPartitionID 
        rs.Open sQuery,cn,1,1
        strACSEventCount = rs("myCount")
        rs.Close
        cn.Close
    end if
    
    wscript.echo sSQLServerInstances(i) & vbtab & strPartitionID & vbtab & strACSEventCount 
    Set oBag =    oAPI.CreateTypedPropertyBag(2)  ' Bag types include:  0 – Alert     1 – Event     2 – Performance     3 – StateData
    Call oAPI.LogScriptEvent("ACSEventCount.vbs",10102,4,"Query run: " & sSQLServerInstances(i) & vbtab & strPartitionID & vbtab & strACSEventCount) 
    Call oBag.AddValue("oObject","OpsMgr AC")
    Call oBag.AddValue("oCounter","Daily Event Count")
    Call oBag.AddValue("oInstance",sSQLServerInstances(i))
    Call oBag.AddValue("oValue",strACSEventCount)
    Call oAPI.AddItem(oBag)
    
    
next

Call oAPI.ReturnItems()