1E Shopping Database : Query for applications and their categories in Shopping

Select opsys.applicationref, SYS.PackageID, OPSYS.ApplicationId, OPSYS.DisplayName,opsys.Enabled, OPSYS.SMSInstallProgram, OP.ApplicationGroupID, AG.GroupName,opsys.Comment
from tb_Application OPSYS
join tb_SMSProgram SYS on SYS.SMSProgramID=OPSYS.SMSInstallProgram
join tb_Applications_ApplicationGroups OP on OPSYS.ApplicationID=OP.ApplicationID
join tb_ApplicationGroup AG on OP.ApplicationGroupID=AG.ApplicationGroupID 

1E Shopping Database : Query to find applications on a machine based on ip address

select  usr.FullName,mac.MachineName,mac.DomainName,mac.IPAddress,app.DisplayName,comp.RequestedTimestamp,comp.DateInstalled, 
DeliveryStatus =
CASE
comp.DeliveryStatus
           WHEN ‘1’ THEN ‘Completed’
        WHEN ‘2’ THEN ‘Failed’
        WHEN ‘3’ THEN ‘Pending’
        WHEN ” THEN ‘Unknown’
END
from
tb_CompletedOrder comp
join tb_Machine mac on comp.MachineId=mac.MachineId
join tb_User usr on comp.UserId=usr.UserId
join tb_Application app on comp.ApplicationId = app.ApplicationId
where  mac.IPAddress in (‘10.0.0.0’)

1E Shopping Database : Query to check app installed by a machine in Shopping

select  usr.FullName,mac.MachineName,mac.DomainName,mac.IPAddress,app.DisplayName,comp.RequestedTimestamp,comp.DateInstalled, 
DeliveryStatus =
CASE
comp.DeliveryStatus
           WHEN ‘1’ THEN ‘Completed’
        WHEN ‘2’ THEN ‘Failed’
        WHEN ‘3’ THEN ‘Pending’
        WHEN ” THEN ‘Unknown’
END
from
tb_CompletedOrder comp
join tb_Machine mac on comp.MachineId=mac.MachineId
join tb_User usr on comp.UserId=usr.UserId
join tb_Application app on comp.ApplicationId = app.ApplicationId

where MachineName like ‘%ABC123%’

1E Shopping Database : Sql query to find apps which failed in appstore -sort by time

select usr.FullName,mac.MachineName,mac.DomainName,mac.IPAddress,app.DisplayName,comp.RequestedTimestamp,comp.DateInstalled,
DeliveryStatus =
CASE
comp.DeliveryStatus
           WHEN ‘1’ THEN ‘Completed’
        WHEN ‘2’ THEN ‘Failed’
        WHEN ‘3’ THEN ‘Pending’
        WHEN ” THEN ‘Unknown’
END
from
tb_CompletedOrder comp
join tb_Machine mac on comp.MachineId=mac.MachineId
join tb_User usr on comp.UserId=usr.UserId
join tb_Application app on comp.ApplicationId = app.ApplicationId

where comp.DeliveryStatus =’2′ and RequestedTimestamp > ‘2014-01-30 00:00:00.700’