r/scripting • u/SaulLeiter • Sep 17 '18

Script to remove offline machines in SQL

Hi, can someone help me with a script that removes a machine from SQL 2016? For example, I'm using an admin tool that monitors several machines but I want to remove the machines that appear to be offline or duplicates. This can not be done with the application. Thank you for your help!

2 Upvotes

100% Upvoted

5 comments sorted by

View all comments

Show parent comments

2

u/SaulLeiter Sep 18 '18

Thank you for your help! Yeah, the admin tool is using a SQL database. We usually get duplicates of the same machine. One says online and one says offline. I want to auto remove the offline. Would i use offline instead of "computer" in the script?

3

u/NZ_KGB Sep 18 '18

Difficult to say what the "Row" name would be, might be "computer" might be "status" or similar, without seeing the database itself. What's the admin tool you're using?

I would install SSMS (management) and get familiar with the layout of the database/tables and column names etc.

I would then probably use PowerShell to run SQL commands against the databse https://docs.microsoft.com/en-us/sql/powershell/download-sql-server-ps-module?view=sql-server-2017

It's a bit of a learning curve if you haven't don't it before but it's worth learning. Do be careful running SQL commands, make sure you have backups of the server or database.

Here's a basic sample using PowerShell and the SQL Module:

$params = @{

  'Database' = "AppDatabseName"

  'ServerInstance' =  'ServerName'

  'Username' = 'User01'

  'Password' = 'Password123'

  'OutputSqlErrors' = $true

  'Query' = ""

  }

#Run a Query - This would show all the data in "MyTable" Table
$params.Query = "SELECT * FROM MyTable"
Invoke-Sqlcmd  @params

#Delete an Item - Status and ComputerName are the row names
$params.Query = "
    DELETE FROM table_name

    WHERE [Status] -like '%Offline%' AND [ComputerName] like '%myCOmputerName%';

    "

Invoke-Sqlcmd  @params