Skip to content

Pulling PSE Outage Data Into SQL Server

As part of documenting the craziness of this storm I have created a script that I run every 5 minutes that pushes data into a SQL Server database. Here is how it works, in the TSQL code I hit the PSE outage webservice url and pull the count of customer that are without power. I then insert it into a table.

DECLARE @Object as Int;
DECLARE @ResponseText as Varchar(8000);
DECLARE @url as NVARCHAR(4000);
DECLARE @TABLEVAR TABLE (responseXml VARCHAR(MAX))
DECLARE @Result INT;
	
SET @url = 'https://www.pse.com/api/sitecore/OutageMap/AnonymoussMapListView'; 
	
Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
Exec sp_OAMethod @Object, 'open', NULL, 'get', @url,'false';
EXEC @Result = sp_OAMethod @Object, 'setResquestHeader', NULL, 'Content-Type', 'application/json'
EXEC sp_OAMethod @Object, 'send', null;

	
INSERT INTO @TABLEVAR
EXEC @Result = sp_OAGetProperty @Object, 'responseText'--, @Response OUT 

	
select TOP 1 @ResponseText  = responseXML FROM @TABLEVAR;
    
set @responseText = substring(@responseText, 11, 200);
set @responseText = substring(@responseText, 0, charindex('}', @responseText) + 1);


DECLARE @outageCount INTEGER = 0;
DECLARE @customersAffected INTEGER = 0;
	
select @outageCount  = value
from openjson(@responseText)
where [key] = 'OutageCount';

select @customersAffected  = value
from openjson(@responseText)
where [key] = 'CustomerAfftectedCount';


INSERT Into pseLog(outageCount, customers) 
	        values (@outageCount, @customersAffected);
Exec sp_OADestroy @Object;

Next I just put that on a SQL Server agent job to run every 5 minutes, and we now have data tracking over time on the number of power outages.

Now I need some way to report on it, so I created a SSRS report to pull the data I have been collecting, and here is what it looks like.

Sometimes SSRS can just be the quickest way to pull data into a visual presentation.

 

More from Stedman Solutions:

SteveStedman5
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!

Leave a Reply

Your email address will not be published. Required fields are marked *