Gerard's codebase

This is a personal repository of code snippets and information.

Over the years I have generated lots of little sub programs and stored away acres of useful code snippets. The problem is always to locate them.

Even more time wasting is forgetting how to do simple things when you havnt developed in an environment for a few years (or even a few months)

My new years resolution is to start putting them up in a common place as I produce them. (thanks google)

They are handy for me and, with a bit of a clean up and documentation, they might be handy for others if they wander in here.

Gerard 2008

Wednesday, September 5, 2018

SSRS - Report Run History




GetADUsers.vbs


Option Explicit

Dim objConnection, objCommand, objRootDSE, strDNSDomain, strQuery
Dim objRecordSet, strDN, objGroup
Dim sEmpID, aManager, sManager
Dim objDB
dim strODBC, oRS

'this constant is used to define ODBC Connection
strODBC  = "Data Source=CMS;Initial Catalog=CMS;Persist Security Info=True;User ID=cms;Password=cms"

' Use ADO to search Active Directory.
Set objConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objConnection

' Search.
strQuery = ";(&(objectCategory=person)(objectClass=user)(name=*));displayName,Manager,employeeID,employeeNumber;subtree"
objCommand.CommandText = strQuery
objCommand.Properties("Page Size") = 100
objCommand.Properties("Timeout") = 30
objCommand.Properties("Cache Results") = False
objCommand.Properties("Sort On") = "sAMAccountName"

Set objRecordSet = objCommand.Execute
If objRecordSet.EOF Then
  Wscript.Echo "No users found"
  objConnection.Close
  Set objRootDSE = Nothing
  Set objConnection = Nothing
  Set objCommand = Nothing
  Set objRecordSet = Nothing
  Wscript.Quit
End If

set objDB = Nothing
set oRS = Nothing

'Connect to database then run the SQL to generate load
Set objDB = DBConnect()

'Wscript.Echo "Before loop"

Do Until objRecordSet.EOF
    'On Error Resume Next

    err.Clear
    sEmpID = objRecordSet.Fields("employeeID")
    aManager = ""
    sManager = ""

    If err.number = 0 Then
        If sEmpID <> "" Then

      on error resume next 'nulls causing problems here
            aManager = split(objRecordSet.Fields("Manager"), ",", -1)

            If len(aManager(0)) > 3 Then
                sManager = right(aManager(0), len(aManager(0)) -3)
            End If

      on error goto 0
      'the replace on displayName is to get rid of the comma in irish names like Louise O'Hanlon which kill the SQL
      WScript.Echo "EXEC SET_ADUser '" & Replace(objRecordSet.Fields("displayName"),"'","") & "'," & objRecordSet.Fields("employeeID") & ",'" & sManager & "','" & objRecordSet.Fields("employeeNumber") & "'"
            'Set oRS = objDB.Execute("EXEC SET_ADUser '" & Replace(objRecordSet.Fields("displayName"),"'","") & "'," & objRecordSet.Fields("employeeID") & ",'" & sManager & "','" & objRecordSet.Fields("employeeNumber") & "'")
            '   oRS.MoveFirst 'this forces load of total recordset


        End If
    End If

    objRecordSet.MoveNext
Loop


Wscript.Echo "After loop"

' Clean up.
objConnection.Close
Set objRootDSE = Nothing
Set objGroup = Nothing
Set objConnection = Nothing
Set objCommand = Nothing
Set objRecordSet = Nothing


'This function sets up DB Connection using specified DSN
  Function DBConnect
  Set objDB = CreateObject("ADODB.Connection")
  objDB.Open strODBC 'change this for each server'
  Set DBConnect = objDB
End Function
SSRS - Report Run History


GetADUsers.vbs


Option Explicit

Dim objConnection, objCommand, objRootDSE, strDNSDomain, strQuery
Dim objRecordSet, strDN, objGroup
Dim sEmpID, aManager, sManager
Dim objDB
dim strODBC, oRS

'this constant is used to define ODBC Connection
strODBC  = "Data Source=CMS;Initial Catalog=CMS;Persist Security Info=True;User ID=cms;Password=cms"

' Use ADO to search Active Directory.
Set objConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objConnection

' Search.
strQuery = ";(&(objectCategory=person)(objectClass=user)(name=*));displayName,Manager,employeeID,employeeNumber;subtree"
objCommand.CommandText = strQuery
objCommand.Properties("Page Size") = 100
objCommand.Properties("Timeout") = 30
objCommand.Properties("Cache Results") = False
objCommand.Properties("Sort On") = "sAMAccountName"

Set objRecordSet = objCommand.Execute
If objRecordSet.EOF Then
  Wscript.Echo "No users found"
  objConnection.Close
  Set objRootDSE = Nothing
  Set objConnection = Nothing
  Set objCommand = Nothing
  Set objRecordSet = Nothing
  Wscript.Quit
End If

set objDB = Nothing
set oRS = Nothing

'Connect to database then run the SQL to generate load
Set objDB = DBConnect()

'Wscript.Echo "Before loop"

Do Until objRecordSet.EOF
    'On Error Resume Next

    err.Clear
    sEmpID = objRecordSet.Fields("employeeID")
    aManager = ""
    sManager = ""

    If err.number = 0 Then
        If sEmpID <> "" Then

      on error resume next 'nulls causing problems here
            aManager = split(objRecordSet.Fields("Manager"), ",", -1)

            If len(aManager(0)) > 3 Then
                sManager = right(aManager(0), len(aManager(0)) -3)
            End If

      on error goto 0
      'the replace on displayName is to get rid of the comma in irish names like Louise O'Hanlon which kill the SQL
      WScript.Echo "EXEC SET_ADUser '" & Replace(objRecordSet.Fields("displayName"),"'","") & "'," & objRecordSet.Fields("employeeID") & ",'" & sManager & "','" & objRecordSet.Fields("employeeNumber") & "'"
            'Set oRS = objDB.Execute("EXEC SET_ADUser '" & Replace(objRecordSet.Fields("displayName"),"'","") & "'," & objRecordSet.Fields("employeeID") & ",'" & sManager & "','" & objRecordSet.Fields("employeeNumber") & "'")
            '   oRS.MoveFirst 'this forces load of total recordset


        End If
    End If

    objRecordSet.MoveNext
Loop


Wscript.Echo "After loop"

' Clean up.
objConnection.Close
Set objRootDSE = Nothing
Set objGroup = Nothing
Set objConnection = Nothing
Set objCommand = Nothing
Set objRecordSet = Nothing


'This function sets up DB Connection using specified DSN
  Function DBConnect
  Set objDB = CreateObject("ADODB.Connection")
  objDB.Open strODBC 'change this for each server'
  Set DBConnect = objDB
End Function



GetADUsers.vbs


Option Explicit

Dim objConnection, objCommand, objRootDSE, strDNSDomain, strQuery
Dim objRecordSet, strDN, objGroup
Dim sEmpID, aManager, sManager
Dim objDB
dim strODBC, oRS

'this constant is used to define ODBC Connection
strODBC  = "Data Source=CMS;Initial Catalog=CMS;Persist Security Info=True;User ID=cms;Password=cms"

' Use ADO to search Active Directory.
Set objConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objConnection

' Search.
strQuery = ";(&(objectCategory=person)(objectClass=user)(name=*));displayName,Manager,employeeID,employeeNumber;subtree"
objCommand.CommandText = strQuery
objCommand.Properties("Page Size") = 100
objCommand.Properties("Timeout") = 30
objCommand.Properties("Cache Results") = False
objCommand.Properties("Sort On") = "sAMAccountName"

Set objRecordSet = objCommand.Execute
If objRecordSet.EOF Then
  Wscript.Echo "No users found"
  objConnection.Close
  Set objRootDSE = Nothing
  Set objConnection = Nothing
  Set objCommand = Nothing
  Set objRecordSet = Nothing
  Wscript.Quit
End If

set objDB = Nothing
set oRS = Nothing

'Connect to database then run the SQL to generate load
Set objDB = DBConnect()

'Wscript.Echo "Before loop"

Do Until objRecordSet.EOF
    'On Error Resume Next

    err.Clear
    sEmpID = objRecordSet.Fields("employeeID")
    aManager = ""
    sManager = ""

    If err.number = 0 Then
        If sEmpID <> "" Then

      on error resume next 'nulls causing problems here
            aManager = split(objRecordSet.Fields("Manager"), ",", -1)

            If len(aManager(0)) > 3 Then
                sManager = right(aManager(0), len(aManager(0)) -3)
            End If

      on error goto 0
      'the replace on displayName is to get rid of the comma in irish names like Louise O'Hanlon which kill the SQL
      WScript.Echo "EXEC SET_ADUser '" & Replace(objRecordSet.Fields("displayName"),"'","") & "'," & objRecordSet.Fields("employeeID") & ",'" & sManager & "','" & objRecordSet.Fields("employeeNumber") & "'"
            'Set oRS = objDB.Execute("EXEC SET_ADUser '" & Replace(objRecordSet.Fields("displayName"),"'","") & "'," & objRecordSet.Fields("employeeID") & ",'" & sManager & "','" & objRecordSet.Fields("employeeNumber") & "'")
            '   oRS.MoveFirst 'this forces load of total recordset


        End If
    End If

    objRecordSet.MoveNext
Loop


Wscript.Echo "After loop"

' Clean up.
objConnection.Close
Set objRootDSE = Nothing
Set objGroup = Nothing
Set objConnection = Nothing
Set objCommand = Nothing
Set objRecordSet = Nothing


'This function sets up DB Connection using specified DSN
  Function DBConnect
  Set objDB = CreateObject("ADODB.Connection")
  objDB.Open strODBC 'change this for each server'
  Set DBConnect = objDB
End Function



GetADUsers.vbs


Option Explicit

Dim objConnection, objCommand, objRootDSE, strDNSDomain, strQuery
Dim objRecordSet, strDN, objGroup
Dim sEmpID, aManager, sManager
Dim objDB
dim strODBC, oRS

'this constant is used to define ODBC Connection
strODBC  = "Data Source=CMS;Initial Catalog=CMS;Persist Security Info=True;User ID=cms;Password=cms"

' Use ADO to search Active Directory.
Set objConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objConnection

' Search.
strQuery = ";(&(objectCategory=person)(objectClass=user)(name=*));displayName,Manager,employeeID,employeeNumber;subtree"
objCommand.CommandText = strQuery
objCommand.Properties("Page Size") = 100
objCommand.Properties("Timeout") = 30
objCommand.Properties("Cache Results") = False
objCommand.Properties("Sort On") = "sAMAccountName"

Set objRecordSet = objCommand.Execute
If objRecordSet.EOF Then
  Wscript.Echo "No users found"
  objConnection.Close
  Set objRootDSE = Nothing
  Set objConnection = Nothing
  Set objCommand = Nothing
  Set objRecordSet = Nothing
  Wscript.Quit
End If

set objDB = Nothing
set oRS = Nothing

'Connect to database then run the SQL to generate load
Set objDB = DBConnect()

'Wscript.Echo "Before loop"

Do Until objRecordSet.EOF
    'On Error Resume Next

    err.Clear
    sEmpID = objRecordSet.Fields("employeeID")
    aManager = ""
    sManager = ""

    If err.number = 0 Then
        If sEmpID <> "" Then

      on error resume next 'nulls causing problems here
            aManager = split(objRecordSet.Fields("Manager"), ",", -1)

            If len(aManager(0)) > 3 Then
                sManager = right(aManager(0), len(aManager(0)) -3)
            End If

      on error goto 0
      'the replace on displayName is to get rid of the comma in irish names like Louise O'Hanlon which kill the SQL
      WScript.Echo "EXEC SET_ADUser '" & Replace(objRecordSet.Fields("displayName"),"'","") & "'," & objRecordSet.Fields("employeeID") & ",'" & sManager & "','" & objRecordSet.Fields("employeeNumber") & "'"
            'Set oRS = objDB.Execute("EXEC SET_ADUser '" & Replace(objRecordSet.Fields("displayName"),"'","") & "'," & objRecordSet.Fields("employeeID") & ",'" & sManager & "','" & objRecordSet.Fields("employeeNumber") & "'")
            '   oRS.MoveFirst 'this forces load of total recordset


        End If
    End If

    objRecordSet.MoveNext
Loop


Wscript.Echo "After loop"

' Clean up.
objConnection.Close
Set objRootDSE = Nothing
Set objGroup = Nothing
Set objConnection = Nothing
Set objCommand = Nothing
Set objRecordSet = Nothing


'This function sets up DB Connection using specified DSN
  Function DBConnect
  Set objDB = CreateObject("ADODB.Connection")
  objDB.Open strODBC 'change this for each server'
  Set DBConnect = objDB
End Function
SELECT    ReportName,[Path]
             ,Runs
             , DATEDIFF(dd, (select Min(timestart)  from ExecutionLogStorage),GETDATE())+1 AS DaysHistoryPresent
             ,CASE
                WHEN  DATEDIFF(dd, (select Min(timestart)  from ExecutionLogStorage),GETDATE())+1 = 0 OR Runs = 0 THEN 0
                ELSE  Runs/(DATEDIFF(dd, FirstDay,LastDay)+1)
             END AS ApproxRunsPerDay
             , Runs/(DATEDIFF(dd, FirstDay,LastDay)+1)*30  AS ApproxRunsPerMonth
       FROM
       (
          SELECT
             c.Name AS ReportName
             ,c.Path
             ,Count(1) AS Runs
             ,MIN(el.TimeStart) AS FirstDay
             ,MAX(el.TimeStart) AS LastDay
          FROM ExecutionLogStorage EL WITH(NOLOCK)
             LEFT OUTER JOIN Catalog C WITH(NOLOCK) ON (EL.ReportID = C.ItemID)
          WHERE   el.Source <> 6
          AND [Name] NOT IN ('Global', 'Default','dsRptFmt','dsRptFmt_Internal','Dummy')
          GROUP BY c.Name,c.Path
       ) AS a
       ORDER BY
       CASE
          WHEN DATEDIFF(dd, FirstDay,LastDay)+1 = 0.000 OR Runs = 0 THEN 0.000
          ELSE (Runs/ DATEDIFF(dd, (select Min(timestart)  from ExecutionLogStorage),GETDATE())+1)
       END DESC
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)