Query to pull PushAddress data

Create_DE-Query_PushAddressDataView.ssjs

<script runat="server" language="javascript">
Platform.Load("core","1");
var name = "PushAddressDataView";
/*
Load this is a Cloud Page to create a data extension based on the _PushAddress Data View and a Query Activity to populate it.
JOIN to _Subscribers as follows:
_PushAddress.ContactID = _Subscribers.SubscriberID
*/

var deObj = {
	"CustomerKey" : name,
	"Name" : name,
	"Fields" : [
		{"Name":"ContactID", "FieldType":"Number", "Ordinal": 1},
		{"Name":"DeviceID", "FieldType":"Text", "MaxLength": 200 , "Ordinal": 2},
		{"Name":"APID", "FieldType":"Text", "MaxLength": 38 , "Ordinal": 3},
		{"Name":"Status", "FieldType":"Number", "Ordinal": 4},
		{"Name":"Source", "FieldType":"Number", "Ordinal": 5},
		{"Name":"SourceObjectId", "FieldType":"Text", "MaxLength": 200 , "Ordinal": 6},
		{"Name":"Platform", "FieldType":"Text", "MaxLength": 100 , "Ordinal": 7},
		{"Name":"PlatformVersion", "FieldType":"Text", "MaxLength": 100 , "Ordinal": 8},
		{"Name":"Alias", "FieldType":"Text", "MaxLength": 100 , "Ordinal": 9},
		{"Name":"OptOutStatusID", "FieldType":"Number", "Ordinal": 10},
		{"Name":"OptOutMethodID", "FieldType":"Number", "Ordinal": 11},
		{"Name":"OptOutDate", "FieldType":"Date", "Ordinal": 12},
		{"Name":"OptInStatusID", "FieldType":"Number", "Ordinal": 13},
		{"Name":"OptInMethodID", "FieldType":"Number", "Ordinal": 14},
		{"Name":"OptInDate", "FieldType":"Date", "Ordinal": 15},
		{"Name":"Channel", "FieldType":"Text", "MaxLength": 20 , "Ordinal": 16},
		{"Name":"CreatedDate", "FieldType":"Date", "Ordinal": 17},
		{"Name":"CreatedBy", "FieldType":"Text", "MaxLength": 150 , "Ordinal": 18},
		{"Name":"ModifiedDate", "FieldType":"Date", "Ordinal": 19},
		{"Name":"ModifiedBy", "FieldType":"Text", "MaxLength": 150 , "Ordinal": 20},
		{"Name":"City", "FieldType":"Text", "MaxLength": 200 , "Ordinal": 21},
		{"Name":"State", "FieldType":"Text", "MaxLength": 200 , "Ordinal": 22},
		{"Name":"ZipCode", "FieldType":"Text", "MaxLength": 20 , "Ordinal": 23},
		{"Name":"FirstName", "FieldType":"Text", "MaxLength": 100 , "Ordinal": 24},
		{"Name":"LastName", "FieldType":"Text", "MaxLength": 100 , "Ordinal": 25},
		{"Name":"UTCOffset", "FieldType":"Decimal","MaxLength": 4, "Scale": 2, "Ordinal": 26},
		{"Name":"IsHonorDST", "FieldType":"Boolean", "Ordinal": 27},
		{"Name":"SystemToken", "FieldType":"Text", "MaxLength": 4000 , "Ordinal": 28},
		{"Name":"ProviderToken", "FieldType":"Text", "MaxLength": 200 , "Ordinal": 29},
		{"Name":"Badge", "FieldType":"Number", "Ordinal": 30},
		{"Name":"LocationEnabled", "FieldType":"Boolean", "Ordinal": 31},
		{"Name":"TimeZone", "FieldType":"Text", "MaxLength": 50 , "Ordinal": 32},
		{"Name":"Device", "FieldType":"Text", "MaxLength": 100 , "Ordinal": 33},
		{"Name":"HardwareId", "FieldType":"Text", "MaxLength": 100 , "Ordinal": 34},
		{"Name":"DeviceType", "FieldType":"Text", "MaxLength": 20 , "Ordinal": 35}
    ]
};

var myDE = DataExtension.Add(deObj);

Write("PushAddress DE Result: "+Stringify(myDE)+"\r\n");

var queryDef = {
    Name : name,
    CustomerKey : name,
    TargetUpdateType : "Overwrite",
    TargetType : "DE",
    Target : {
        Name : name,
        CustomerKey : name
    },
    QueryText : 'SELECT _ContactID as "ContactID",\r_DeviceID as "DeviceID",\r_APID as "APID",\r_Status as "Status",\r_Source as "Source",\r_SourceObjectId as "SourceObjectId",\r_Platform as "Platform",\r_PlatformVersion as "PlatformVersion",\r_Alias as "Alias",\r_OptOutStatusID as "OptOutStatusID",\r_OptOutMethodID as "OptOutMethodID",\r_OptOutDate as "OptOutDate",\r_OptInStatusID as "OptInStatusID",\r_OptInMethodID as "OptInMethodID",\r_OptInDate as "OptInDate",\r_Channel as "Channel",\r_CreatedDate as "CreatedDate",\r_CreatedBy as "CreatedBy",\r_ModifiedDate as "ModifiedDate",\r_ModifiedBy as "ModifiedBy",\r_City as "City",\r_State as "State",\r_ZipCode as "ZipCode",\r_FirstName as "FirstName",\r_LastName as "LastName",\r_UTCOffset as "UTCOffset",\r_IsHonorDST as "IsHonorDST",\r_SystemToken as "SystemToken",\r_ProviderToken as "ProviderToken",\r_Badge as "Badge",\r_LocationEnabled as "LocationEnabled",\r_TimeZone as "TimeZone",\r_Device as "Device",\r_HardwareId as "HardwareId",\r_DeviceType as "DeviceType"\rFROM _PushAddress'
};

var status = QueryDefinition.Add(queryDef);
Write("PushAddress Query Result: "+Stringify(status)+"\r\n");

</script>

sfmc-sql-pushaddress.sql

select
  _ContactID ContactID
, _DeviceID DeviceID
, _APID APID
, _Status Status
, _Source Source
, _SourceObjectId SourceObjectId
, _Platform Platform
, _PlatformVersion PlatformVersion
, _Alias Alias
, _OptOutStatusID OptOutStatusID
, _OptOutMethodID OptOutMethodID
, _OptOutDate OptOutDate
, _OptInStatusID OptInStatusID
, _OptInMethodID OptInMethodID
, _OptInDate OptInDate
, _Channel Channel
, _CreatedDate CreatedDate
, _CreatedBy CreatedBy
, _ModifiedDate ModifiedDate
, _ModifiedBy ModifiedBy
, _City City
, _State State
, _ZipCode ZipCode
, _FirstName FirstName
, _LastName LastName
, _UTCOffset UTCOffset
, _IsHonorDST IsHonorDST
, _SystemToken SystemToken
, _ProviderToken ProviderToken
, _Badge Badge
, _LocationEnabled LocationEnabled
, _TimeZone TimeZone
, _Device Device
, _HardwareId HardwareId
, _DeviceType DeviceType
from _PushAddress