Advertisement
easternnl

SQL from PowerShell

Mar 14th, 2016
471
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. $SQLServer = "databaseserver" #use Server\Instance for named SQL instances!
  2. $SQLDBName = "databasename"
  3. $SqlQuery =
  4. "SELECT [UserName]
  5.     ,InteractiveDesktop = DATEDIFF(Second, [BrokeringDate],[InteractiveEndDate])       
  6.     ,GpoDuration = DATEDIFF(Second, [GpoStartDate], [GpoEndDate])
  7.     ,LogonDuration = DATEDIFF(Second, [LogOnStartDate], [LogOnEndDate])
  8.    ,[BrokeringDuration]
  9.    ,[AuthenticationDuration]            
  10.    , FORMAT([BrokeringDate] , 'dd-MM-yyyy') AS EasyDate
  11.    , FORMAT([BrokeringDate] , 'HH') AS EasyHour
  12.    ,SUBSTRING([Name],18,4) AS VLAN
  13. FROM [MonitorData].[Connection]
  14.    LEFT JOIN [MonitorData].[Session]
  15.    ON ([MonitorData].[Connection].[SessionKey] = [MonitorData].[Session].[SessionKey] )
  16.    LEFT JOIN [MonitorData].[Machine]
  17.    ON ([MonitorData].[Session].[MachineId] = [MonitorData].[Machine].[Id])
  18.    LEFT JOIN [MonitorData].[User]
  19.    ON ([MonitorData].[Session].[UserId] = [MonitorData].[User].[Id])
  20. WHERE [IsReconnect] = 0
  21.    AND [ConnectedViaIPAddress] Not Like '145.121.57.10%'
  22.    AND [BrokeringDate] IS NOT Null
  23.    AND [InteractiveEndDate] IS NOT Null
  24.    AND [UserName] like 'h.oost'        
  25. ORDER BY [UserName], [BrokeringDate], [InterActiveDesktop]
  26. "
  27.  
  28. $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
  29. $SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True"
  30.  
  31. $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
  32. $SqlCmd.CommandText = $SqlQuery
  33. $SqlCmd.Connection = $SqlConnection
  34.  
  35. $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
  36. $SqlAdapter.SelectCommand = $SqlCmd
  37.  
  38. $DataSet = New-Object System.Data.DataSet
  39. [void]$SqlAdapter.Fill($DataSet)
  40.  
  41. $SqlConnection.Close()
  42.  
  43. #clear
  44.  
  45. $DataSet.Tables[0] | ft
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement