Help Universe

Guide:

Data Transfer Servers & Cloud.

 

Prerequisites

  • Windows server with .Net 6
  • Domain user with WMI access to targeted hosts
  • Domain user with access to targeted MSSQL servers. VIEW SERVER STATE, VIEW DATABASE STATE, VIEW DEFINITION and CONTROL permissions are required
  • Anaconda/Python. Only required if an automated upload to the ITBI cloud service is in scope.

 

Test of WMI access

  • Run WMI request from Powershell against a remote host
    Get-WmiObject Win32_Service -Credential <domain>\<user> -ComputerName <remote host>

 

Installation process

  • Create the folder “D:\ITBICollector”
  • Copy “D:\ITBIv154\Solutions\MidRange\Collector\InstallationWorksheetMaster.ps1” to “D:\ITBICollector”
  • Edit and run D:\ITBICollector\InstallationWorksheetMaster.ps1
  • Copy “D:\ITBIv154\Solutions\MidRange\Collector\InstallationWorksheetWorker.ps1” to “D:\ITBICollector”
  • Edit and run D:\ITBICollector\InstallationWorksheetWorker.ps1
  • Edit “D:\ITBICollector\Master\config\WindowsCollector@<HostName>.json”
  • Edit “D:\ITBICollector\Master\config\MSSQLCollector@<HostName>.json”
  • Edit “D:\ITBICollector\Master\config\WindowsCollector@<HostName>.ConnectionProfile.conf”
  • Edit “D:\ITBICollector\Master\config\ MSSQLCollector@<HostName>.ConnectionProfile.conf
  • Enable upload to the ITBI cloud service, if possible. Edit D:\ITBICollector\Master\config\Collector.conf

 

Password can be encrypted. Run the following command to encrypt a secret:

& “D:\ITBIv154\ITBI.Collector\ITBI.Collector.exe” -config “D:\ITBICollector\Master\config\Collector.conf” -encrypt “<secret>”

Specify the user name in the format “user@domain” or “domain\\user”


Overview of servers

To produce an overview of servers, run the following query in Athena. Remember to edit the batch in line 8 to yesterday’s batch.

WITH HostStatus AS (

SELECT *

, CASE WHEN HostType = ‘Unknown’ AND ErrorMessage LIKE ‘%WinRM cannot process the request. The following error occurred while using Kerberos authentication: Cannot find the computer%’ THEN 1 END AS WindowsNotAssignedToDomain

, CASE WHEN HostType = ‘Unknown’ AND ErrorMessage LIKE ‘%Could not connect to %. (Remote Shell output: Permission denied (publickey).)%’ THEN 1 END AS SSHPermissionDenied

, CASE WHEN HostType = ‘Unknown’ AND  ErrorMessage LIKE ‘%The client cannot connect to the destination specified in the request. Verify that the service on the destination is running and is accepting requests. Consult the logs and documentation for the WS-Management service running on the destination, most commonly IIS or WinRM%’ OR ErrorMessage LIKE ‘%inRM cannot complete the operation. Verify that the specified computer name is valid, that the computer is accessible over the network, and that a firewall exception for the WinRM service is enabled and allows access from this computer%’ THEN 1 END AS WinRMServiceNotAvailable

, CASE WHEN PingErrorType = ‘SocketError.NoData’ THEN 1 END AS IpaddressNotFound

FROM “network_hosttype”

WHERE Batch >= 20230511

–AND Date > Timestamp’2023-05-02 09:50′

)

, HostStatusStatistics AS (

SELECT HostName

, COUNT(*) AS NoOfResults

, SUM(WindowsNotAssignedToDomain) AS WindowsNotAssignedToDomain

, SUM(WinRMServiceNotAvailable) AS WinRMServiceNotAvailable

, SUM(SSHPermissionDenied) AS SSHPermissionDenied

, SUM(IpaddressNotFound) AS IpaddressNotFound

, SUM(CASE WHEN AccessAccepted THEN 1 END) AS AccessAccepted

, SUM(CASE WHEN HostType = ‘Windows’ AND ErrorMessage LIKE ‘%Access is denied%’ THEN 1 END) AS WMIAccessDenied

, SUM(CASE WHEN HostType = ‘Windows’ THEN 1 END) AS IsWindows

, SUM(CASE WHEN HostType = ‘Linux’ THEN 1 END) AS IsLinux

FROM HostStatus

GROUP BY HostName

)

SELECT *

FROM HostStatusStatistics

WHERE 1 = 1

–WHERE Batch = 20230502

–AND AccessAccepted > 0

–AND IsLinux > 0

–AND AccessAccepted IS NULL

–AND IpaddressNotFound > 0

ORDER BY HostName

The result downloads as .csv-file.

The result copies to Excel.

The pivor sheet updates.

Contact support.

If you have any questions, difficulties, or suggestions please write to our support channel by filling out the form.

"*" indicates required fields

This field is for validation purposes and should be left unchanged.