Pulling PSE Outage Data Into SQL Server

Freak Storm/Weather

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.