Lightning AU - SQL Logs
Config details to access Database of Lighting AU detailed transaction logs through SQL Management Studio.
Tip: https://www.freeformatter.com/xml-formatter.html
Server name: lightning-sql-server-a-uk-prod.database.windows.net
Login: lightning-au-support
Password: GWocNQ7yiZwT8w6qUSC47msD7xh8uKJf
Default Database: lightning-sql-database-uk-prod
SQL EXAMPLE:
/*
==== Statuses ====
1 = Pending
2 = Booked
3 = Failed
4 = PartialBooked
=== Segment Types ====
1 = Flight
2 = Hotel
3 = Car
4 = Rail
=== Itinerary Passenger Approver Level ====
0 = None
1 = Passive
2 = PreTrip
=== Itinerary Passenger Approval State ====
0 = Auto
1 = Pending
2 = Approved
3 = Declined
4 = Expired
=== Itinerary Passenger Approver Status ====
0 = Pending
1 = ApprovalEmailSent
2 = DeclinedEmailSent
3 = ApprovedEmailSent
4 = ExpiredEmailSent
*/
--STEP #1: Failed Booking Report
SELECT IP.Id AS PassengerId, I.DebtorIdentifier, IP.Title AS Passenger, IP.Email, IP.ClientId, IP.BookingMessage, IP.SabreErrorXml, IP.SabreReservationXml, IP.*
FROM ItineraryPassenger IP WITH(NOLOCK)
INNER JOIN Itinerary I WITH(NOLOCK) ON IP.ItineraryId = I.Id
WHERE IP.ItineraryStatusId IN (3) AND IP.[Enabled] = 1 AND IP.[IsDeleted] = 0
AND IP.CreatedOn > '2018-05-02 00:00:00 +10:00'
order by IP.Id desc
--STEP 2 Change the ID to the PassengerID you want to look at
DECLARE @passengerId BIGINT = 130113
-- Show me the Flight/Hotel/Car/Rail Items for that passenger
SELECT IP.Id AS PassengerId, IP.Title AS Passenger, IP.Email, IP.ClientId, IP.BookingMessage, IP.SabreErrorXml, IP.SabreReservationXml, IP.* FROM ItineraryPassenger IP WITH(NOLOCK) WHERE IP.Id = @passengerId
SELECT SabreFlightReservationXml, * FROM ItineraryPassengerFlight WITH(NOLOCK) WHERE ItineraryPassengerId = @passengerId AND [Enabled] = 1 AND [IsDeleted] = 0
SELECT SabreHotelReservationXml, * FROM ItineraryPassengerHotel WITH(NOLOCK) WHERE ItineraryPassengerId = @passengerId AND [Enabled] = 1 AND [IsDeleted] = 0
SELECT SabreCarReservationXml, * FROM ItineraryPassengerCar WITH(NOLOCK) WHERE ItineraryPassengerId = @passengerId AND [Enabled] = 1 AND [IsDeleted] = 0
SELECT SabreRailReservationXml, * FROM ItineraryPassengerRail WITH(NOLOCK) WHERE ItineraryPassengerId = @passengerId AND [Enabled] = 1 AND [IsDeleted] = 0
SELECT * FROM ItineraryPassengerPayment WITH(NOLOCK) WHERE ItineraryPassengerId = @passengerId AND [Enabled] = 1 AND [IsDeleted] = 0
SELECT Selected, * FROM ItineraryPassengerPersonalMembership WITH(NOLOCK) WHERE ItineraryPassengerId = @passengerId AND [Enabled] = 1 AND [IsDeleted] = 0
SELECT * FROM ItineraryPassengerApprovers WITH(NOLOCK) WHERE ItineraryPassengerId = @passengerId AND [Enabled] = 1 AND [IsDeleted] = 0
TIP: /* IP.ItineraryStatusId IN (3) AND */