Query to pull PushAddress data

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 

In order this SQL works in Query studio I had to some changes

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

Like what? I've used my query in SFMC without any issue.

This is the error that i am getting running the SQl on Quesry studio in SFMC

Appears to be an issue isolated to Query Studio.

Thanks for share.
have a great year 2020

Hi Adam
I am trying to send a mobile push notification on a test app uploaded via usb cable for testing propose

Do you know How I can update the Status fiel on _Pushaddress. I wish chante the status from "No Opted in" to "Opted in"

@argenisperez you need sent this informations by API contacts/v1/contacts
Method PATCH
And this fields below

{
"name": "MobilePush Subscriptions",
"items": [{
"values": [{
"name": "Device ID",
"value": "VALUE"
},
{
"name": "Application",
"value": "VALUE"
},
{
"name": "Opt In Status",
"value": 2
},
{
"name": "Opt In Method",
"value": 13
},
{
"name": "Opt In Date",
"value":"DATE"
}
]
}]
}]

Hi Adam, is there any way to retrieve App Version number?

_APID perhaps?

_APID is really close :) but it's just an ID of the App

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

I receive the following error: Could not convert date and/or time from string data type. - Any quick tips on how to resolve in Query Studio?

You'll need to convert any dates to varchar data types, because apparently Query Studio can't figure that out. For example: convert(varchar(50), _CreatedDate) as CreatedDate,

Hello guys
Please help, what API can I use to retrieve the fields from _PushAddress data view? Just in case SF adds some more fields in the data views. Thanks.

[...] what API can I use to retrieve the fields from _PushAddress data view? Just in case SF adds some more fields in the data views.

AFAIK, this data is not exposed to the web services.

[...] what API can I use to retrieve the fields from _PushAddress data view? Just in case SF adds some more fields in the data views.

AFAIK, this data is not exposed to the web services.

Do you know if this data is available by SQL? (the custom attributes I mean)

Hi there,
I noticed that the ContacID is not the ContactKey used in MC.
Is there any way to connect the MobilePush ContactID to the AllSubs ContactKey?

Hi there, I noticed that the ContacID is not the ContactKey used in MC. Is there any way to connect the MobilePush ContactID to the AllSubs ContactKey?

Here's a response on that topic from Bill Mote who works at SF: https://salesforce.stackexchange.com/a/313851/5202