Developer's Closet A place where I can put my PHP, SQL, Perl, JavaScript, and VBScript code.

4Aug/140
Useful Scripts

PowerShell:

$ou = [adsi]"LDAP://OU=Marketing,OU=Departments,DC=Company,DC=Domain";
$user = $ou.psbase.get_children().find('CN=UserName');
$user.psbase.invokeSet("allowLogon",0);
$user.setinfo();

Bash:

#!/bin/bash
fname="/path/file"
tname="/new/file.tmp"
i="0"
DATE=$(date +%Y%m%d%H%M%S)
sudo cp "$fname" "$fname".$DATE

while IFS='' read -r line
do
if [ "$line" == " line item:" ] || [ $i -gt 0 -a $i -lt 4 ]
then
echo -e "#$line" | sudo tee -a $tname
i=$(($i + 1))
else
printf "%s\n" "$line" | sudo tee -a "$tname"
fi
done <"$fname"

sudo mv $tname $fname

Batch:

$SourcePath = 'C:\Path\';
$DestServer = 'ServerName';
$DestPath = '/path/';
$FileName = 'FileName';
$Output = @()
$cmd = @(
"y",
"lcd $SourcePath",
"cd $DestPath",
"mput $FileName",
"quit"
)

$Output = $cmd | & "C:\Program Files (x86)\Putty\psftp.exe" –v $DestServer 2>&1;
$Err = [String]($Output -like "*=>*");
If (($LastExitCode -ne 0) || (($Err.Contains("=>")) -eq $false)) {
throw "File Failed to Transfer! `n $($Output)";
}

Linux:

sudo mkdir /space;
echo "/dev/space /space ext4 defaults 0 0" | sudo tee -a /etc/fstab;
sudo mount /dev/space /space;
sudo df -h;
ls /dev/;

PowerShell:

Add-PSSnapin Quest.ActiveRoles.ADManagement;
connect-QADService -service domain;
set-QADuser UserName -TSRemoteControl 0;
$objCurrentPSProcess = [System.Diagnostics.Process]::GetCurrentProcess();
Stop-Process -Id $objCurrentPSProcess.ID;

 

7Jun/140
SQL Query for Recently Changed Objects

These may be useful to determine when objects have been changed.  We might infer from this info when was the last formal deployment of a database.

 

-- get most recently changed object of each type

SELECT  o.name

,SCHEMA_NAME(o.schema_id) AS 'SchemaName'

,COALESCE(OBJECT_NAME(o.parent_object_id), '') AS 'ParentName'

,o.type

,o.type_desc

,o.create_date

,o.modify_date

FROM    sys.objects o

JOIN(SELECT    type

,'LateDate' = MAX(modify_date)

FROM      sys.objects

WHERE     is_ms_shipped = 0

AND OBJECT_SCHEMA_NAME(object_id) <> 'tSQLt'

GROUP BY  type

) xo

ON o.type = xo.type

AND o.modify_date = xo.LateDate

ORDER BY o.modify_date DESC

,o.type

,o.name

 

-- get 20 most recently changed objects

SELECT TOP 20

o.name

,SCHEMA_NAME(o.schema_id) AS 'SchemaName'

,COALESCE(OBJECT_NAME(o.parent_object_id), '') AS 'ParentName'

,o.type

,o.type_desc

,o.create_date

,o.modify_date

FROM   sys.objects o

WHERE   o.is_ms_shipped = 0

AND o.parent_object_id = 0

--AND o.type = 'U'

ORDER BY o.modify_date DESC

 

-- get 20 most recently changed objects that are not tSQLt or tSQLt tests

SELECT TOP 20

SCHEMA_NAME(o.schema_id) AS 'SchemaName'

,o.name

,COALESCE(OBJECT_NAME(o.parent_object_id), '') AS 'ParentName'

,o.type

,o.type_desc

,o.create_date

,o.modify_date

FROM   sys.objects o

WHERE   o.is_ms_shipped = 0

AND o.parent_object_id = 0

--AND o.type = 'U'

AND OBJECT_SCHEMA_NAME(o.object_id) NOT LIKE 'Z%'

AND OBJECT_SCHEMA_NAME(o.object_id) <> 'tSQLt'

ORDER BY o.modify_date DESC

 

Filed under: SQL, Windows No Comments
30May/140
Fix an SQL Database Marked Suspect

If you have a database marked suspect, and do not have a backup to restore from, you can attempt to repair the database. This may lead to data loss and is most certainly not the best option (see: database backup).

Make sure there are no open connections to the database - stop all services that might attempt a connection. Run sp_who2 to check on open connections.

Run the commands one at a time and fix any errors as they appear:

EXEC sp_resetstatus 'DatabaseName';
ALTER DATABASE DatabaseName SET EMERGENCY;
DBCC CHECKDB ('DatabaseName');
ALTER DATABASE DatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DBCC CHECKDB ('DatabaseName', REPAIR_ALLOW_DATA_LOSS);
ALTER DATABASE DatabaseName SET MULTI_USER;

Restoring from a backup is the better way to fix this issue.

Filed under: SQL, Windows No Comments
22May/140
Purge Backup History from SQL msdb Database

I just about ran out of space on my Data partition and discovered that the msdb database was over 114 GB! In particular, the backupfile table had over 107,000,000 rows which took up over 70 GB. Other backup-related tables took up the rest of the space. This is a real problem. The normal command I would run to purge backup history did not work because it uses transactions and would fill up TLog. So I had to run the sp in a look one day at a time:
SET NOCOUNT ON
DECLARE @purge_date DATETIME
DECLARE @cutoff_date DATETIME

-- Set purge cutoff to n days back
set @cutoff_date = DATEADD(dd,DATEDIFF(dd,0,GETDATE())-180,0)

PRINT 'Start Date: ' +
CONVERT(VARCHAR(10),GETDATE(),121)
PRINT 'Purge backup history before Cutoff Date: ' +
CONVERT(VARCHAR(10),@cutoff_date,121)

WHILE 1 = 1
BEGIN

SET @purge_date = null

-- Find date of oldest backup set
SELECT @purge_date = DATEADD(dd,DATEDIFF(dd,0,MIN(backup_finish_date))+1,0) FROM msdb.dbo.backupset(NOLOCK)
WHERE backup_finish_date <= @cutoff_date

IF @purge_date is null or @purge_date > @cutoff_date
BEGIN
PRINT 'Purge backup history complete through: '+
CONVERT(VARCHAR(10),@cutoff_date ,121)
BREAK
END

PRINT CHAR(10)+CHAR(13) +
'Purging backup history before: ' +
CONVERT(VARCHAR(10),@purge_date,121) +CHAR(10)+CHAR(13)

SELECT [Backup Sets to be Deleted Count ] = COUNT(*) FROM msdb.dbo.backupset(NOLOCK)
WHERE backup_finish_date < @purge_date

EXEC msdb.dbo.sp_delete_backuphistory @purge_date

END

PRINT 'End Date: ' +
CONVERT(VARCHAR(10),GETDATE(),121)

 

Filed under: SQL, Windows No Comments
1Apr/140
Clear Orchestrator Logs from Runbooks by Folder Name

I found it useful to clear Orchestrator Runbook logs by Folder. This allowed me to clear smaller chunks of logs from Orchestrator where in the past I was clearing the entire database which was a very slow process. A small improvement, but it helps.

-- This script will remove logs from a folder in Orchestrator
-- enter a folder name into the folder variable

-- Create temporary table for sysprocesses
set QUOTED_IDENTIFIER Off
SET NOCOUNT ON

-- Variables
declare @strsql char(200),
  @ThePolicyId varchar(50),
  @USRCOUNT int,
  @PRINTUSRCOUNT VARCHAR (255), 
  @SqlScript varchar(255),
  @PolicyId varchar(50),
  @FolderName VARCHAR(100),
  @NumberOfDaysToKeep INT

-- Folder name
SET @FolderName = 'Folder Name'
SET @NumberOfDaysToKeep = 180

-- Query for UniqueId number of all Runbooks in a folder
use Orchestrator
set @strsql = 'select p.UniqueID from [dbo].[POLICIES] p 
INNER JOIN [dbo].[FOLDERS] f ON p.ParentID = f.UniqueID
WHERE f.[Name] = ''' + @FolderName + '''
AND p.Deleted = 0
ORDER BY f.name, p.name'

IF OBJECT_ID('tempdb..#toRun') IS NOT NULL DROP TABLE #toRun

create table #toRun
(
  PolicyId uniqueidentifier,
)

-- Insert into temp table
INSERT INTO #toRun EXEC(@strsql)

-- Comment out the query below to hide connections
--select * from #toRun

SELECT @USRCOUNT = (SELECT CONVERT(CHAR, COUNT(1)) FROM #toRun)
SET @PRINTUSRCOUNT = 'Attempting to run ' + CAST(@USRCOUNT AS VARCHAR(36)) + ' Runbooks' 
PRINT @PRINTUSRCOUNT 

-- Pause script for 1 seconds
WAITFOR DELAY '00:00:01' 

-- Begin cursor 
DECLARE CUSRRUN SCROLL CURSOR FOR 
SELECT PolicyId FROM #toRun 

OPEN CUSRRUN

FETCH FIRST FROM CUSRRUN INTO @PolicyId 
SELECT @SqlScript = 'DECLARE @Completed bit 
	SET @Completed = 0 
	WHILE @Completed = 0 EXEC sp_CustomLogCleanup 
	@Completed OUTPUT, 
	@FilterType=2,
	@YDays=''' + CAST(@NumberOfDaysToKeep AS VARCHAR) + ''',
	@PolicyID=''' + CAST(@PolicyId AS VARCHAR(36)) + ''''
PRINT 'Runbook: ' + CAST(@PolicyId AS VARCHAR(36))
PRINT @SqlScript 
EXEC(@SqlScript) 

WHILE @@FETCH_STATUS = 0 
BEGIN 
  FETCH NEXT FROM CUSRRUN into @PolicyId 
  WHILE @@FETCH_STATUS = 0 
  BEGIN 

    SELECT @SqlScript = 'DECLARE @Completed bit 
		SET @Completed = 0 
		WHILE @Completed = 0 EXEC sp_CustomLogCleanup 
		@Completed OUTPUT, 
		@FilterType=2,
		@YDays=''' + CAST(@NumberOfDaysToKeep AS VARCHAR) + ''',
		@PolicyID=''' + CAST(@PolicyId AS VARCHAR(36)) + ''''
    PRINT 'Runbook: ' + CAST(@PolicyId AS VARCHAR(36))
    PRINT @SqlScript 
    EXECUTE(@SqlScript) 

    FETCH NEXT FROM CUSRRUN INTO @PolicyId 

  END 
END 
CLOSE CUSRRUN 
DEALLOCATE CUSRRUN 

--End cursor
PRINT 'Finished running'
DROP TABLE #toRun

--select TOP 10 * from [dbo].[POLICIES] p 

--select TOP 10 f.Name,* from [dbo].[POLICIES] p 
--INNER JOIN [dbo].[FOLDERS] f ON p.ParentID = f.UniqueID
--WHERE f.[Name] = 'Cisco'
--AND p.Deleted = 0
--ORDER BY f.name, p.name

--select * from [dbo].[FOLDERS] f WHERE f.[Name] = 'Cisco' ORDER BY f.name

Notes:

When the sp_CustomLogCleanup stored procedure is executed, it identifies 200 policy instances to purge and returns either a 0 or a 1 upon completion. The stored procedure identifies the 200 policy instances that are to be purged by executing one of the three additional stored procedures based on the log purging option selected:

  • sp_GetLogEntriesForDelete_FilterByEntries
  • sp_GetLogEntriesForDelete_FilterByDays
  • sp_GetLogEntriesForDelete_FilterByEntriesAndDays

The log purge utility has a hard-coded time out value for the execution of the stored procedure. While not recommended, you can change this value within the stored procedure to accommodate extremely large log counts.

If you choose to turncate, truncating the OBJECTINSTANCEDATA and OBJECTINSTANCES tables should be enough, as these two tables contain the bulk of log data. You may not be able to truncate the POLICYINSTANCES due to foreign key constraints.

10Feb/140
Query for the Size of All Databases in SQL

If you need to see the size of all databases on an SQL server, here is a helpful query, one I always seem to need to write by memory:

SELECT d.name AS Name,
(SUM(mf.size) * 8 / 1024 / 1024) AS Size_GB
FROM sys.master_files mf
INNER JOIN sys.databases d ON d.database_id = mf.database_id
WHERE d.database_id > 4 -- skip system databases
GROUP BY d.name
ORDER BY d.name

 

Filed under: SQL, Windows No Comments
26Nov/130
No Connection!! Clear Your ARP Table

A great article! This is rare, but sometimes you need to know:

http://zeldor.biz/2011/09/clear-arp-cache/

 

 

Filed under: Linux, Windows No Comments
13Aug/130
SQL 2008 R2: Cannot Bring Database Back Online

I had a terribly stressful evening at work last night. I brought a database offline to move a file and when I tried to bring the database back online I received the following error:

Msg 5011, Level 14, State 7, Line 1
User does not have permission to alter database 'DatabaseName', the database does not exist, or the database is not in a state that allows access checks.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

Nothing I tried worked and a Google search was not helpful. Here is what happened:

I made a mistake right from the beginning, I should have moved the file before I set the database offline, but that is not what caused the problem:

ALTER DATABASE DatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

ALTER DATABASE DatabaseName SET OFFLINE;

-- copy the file to the new location...

ALTER DATABASE DatabaseName MODIFY FILE
(
Name = FileName,
Filename = 'F:\New\Location\Name.mdf'
);

ALTER DATABASE DatabaseName SET ONLINE;

ALTER DATABASE DatabaseName SET MULTI_USER;

-- delete old file...

As soon as I tried to set the database online, I received the error message: User does not have permission to alter database 'DatabaseName', the database does not exist, or the database is not in a state that allows access checks.

I did not think this was a permissions problem. At first I chased the part of the error that the database is not in a state that allows access checks. This led me nowhere because it makes sense that the database was not in a state that allows access checks because it has been set offline.

So a coworker looked at the file I moved. He discovered that file did not inherit permissions from the parent folder and the permissions were wrong. As soon as I reset permissions on the file I was able to set the database back online.

This was a frustrating problem, but I learned something I can add to my toolbox. Here is what I will do in the future:

ALTER DATABASE DatabaseName MODIFY FILE 
(
Name = FileName,
Filename = 'F:\New\Location\Name.mdf'
);

ALTER DATABASE DatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

ALTER DATABASE DatabaseName SET OFFLINE;

-- copy the file to the new location...

-- reset permissions on the file...

ALTER DATABASE DatabaseName SET ONLINE;

ALTER DATABASE DatabaseName SET MULTI_USER;

-- delete old file...

Filed under: SQL, Windows No Comments
1Aug/130
Get All Installed Software Using PowerShell

The following PowerShell script will return all installed software on a Windows server.

function GetInstalledMSIVersionNumber($MSIName) {
	#Define the variable to hold the location of Currently Installed Programs
	$registry = "SOFTWARE\\Microsoft\\Windows\\CurrentVersion\\Uninstall","SOFTWARE\\Wow6432Node\\Microsoft\\Windows\\CurrentVersion\\Uninstall";
	$Results = "";
	foreach($UninstallKey in $registry){
		$computername="ServerName";
		#Create an instance of the Registry Object and open the HKLM base key
		$reg=[microsoft.win32.registrykey]::OpenRemoteBaseKey('LocalMachine',$computername) 
		#Drill down into the Uninstall key using the OpenSubKey Method
		$regkey=$reg.OpenSubKey($UninstallKey) 
		#Retrieve an array of string that contain all the subkey names
		$subkeys=$regkey.GetSubKeyNames() 
		#Open each Subkey and use GetValue Method to return the required values for each
		foreach($key in $subkeys){
			$thisKey=$UninstallKey+"\\"+$key 
			$thisSubKey=$reg.OpenSubKey($thisKey) 
			if ($($thisSubKey.GetValue("DisplayName"))) {
				# header: DisplayName,DisplayVersion,Publisher,InstallDate,InstallLocation,InstanceId,UninstallString,EstimatedSize
				$Results = $Results + """" + $($thisSubKey.GetValue("DisplayName") + """" + "," + """" + $thisSubKey.GetValue("DisplayVersion") + """" + "," + """" + $thisSubKey.GetValue("Publisher") + """" + "," + """" + $thisSubKey.GetValue("InstallDate") + """" + "," + """" + $thisSubKey.GetValue("InstallLocation") + """" + "," + """" + $thisSubKey.GetValue("InstanceId") + """" + "," + """" + $thisSubKey.GetValue("UninstallString") + """" + "," + """" + $thisSubKey.GetValue("EstimatedSize") + """" + "`n");
			}
		} 
	}
	return $Results;
}
$Results = GetInstalledMSIVersionNumber("");
$Results;
25Jul/130
Collect Logs from a SAN Switch using Putty

How to collect logs from an EMC SAN Switch for debugging networking issues. The following worked successfully on a Connectrix 300 Brocade switch using PuTTY to connect to the switch:

1. Start the standalone Putty application

2. Select SSH or Telnet as the connection type and put in the IP of the Brocade switch.

3. On the left hand column, select 'logging' and on the right hand side choose 'all session output'

4. Name the log file (default is putty.log in the putty.exe directory)

5. Hit connect or open and you will be presented with a prompt for login, you may have to approve connection if this is your first time connecting.

6. Login to the Brocade switch using your credentials.

7. Run command supportshow, the screen will output all supportshow information and will add it to the log file specified in step 4.

8. Exit putty session by typing quit, gather log from selected location

Filed under: Windows No Comments