Report of IR's by Location

Answers

• Options

  Shane_White Cireson Support Super IT Monkey ✭✭✭✭✭

  May 2019

  Hi @Scott_Wright
  
  Run this against your Service Manager Database and let me know if this is kind of along the lines on what you wanted?
  
  

  SELECT aml.DisplayName as 'LocationName', bme.DisplayName as 'TicketName',

   aml.Name_95003569_4B1C_C946_F24F_71EF9033F9A8 as 'Location',

    aml.LocationState_BD74724A_69C1_B971_4FDE_19AE2C068F1C as 'LocationState',

     aml.LocationCountry_3D103163_F8A1_4009_75E0_E9B397EA7C24 As 'LocationCountry'

    FROM [ServiceManager].[dbo].[MT_Cireson$AssetManagement$Location] as aml

    JOIN dbo.Relationship as r

    ON  r.TargetEntityId = aml.BaseManagedEntityId

    JOIN dbo.BaseManagedEntity as bme

    ON r.SourceEntityId = bme.BaseManagedEntityId

    Where (bme.DisplayName like 'IR%'

     OR bme.DisplayName like 'SR%'

      OR bme.DisplayName like 'CR%'

  OR bme.DisplayName like 'PR%')

  
  Thanks,
  Shane.

  1
• Options

  Scott_Wright Customer IT Monkey ✭

  May 2019 edited May 2019

  Hi Shane,
  
  First, thank you for taking the time to share your solution.  It's close, but I need to isolate the WI ID, so I can look up details from the SCSM DB (unless thay are also contained in the ServiceManager DB).  I need to list:
  WI, Location Name, Title, classification, support group, status, created date
  
  Example:
  

  "IR1234" "Store124" "Application Not Working" "Software Error" "Software Support Team" "Active" "05/23/19" 

  
  Do you think this is possible?

  0
• Options

  Shane_White Cireson Support Super IT Monkey ✭✭✭✭✭

  May 2019 Answer ✓

  Hi @Scott_Wright
  
  How is this one:
  

  
  

  SELECT ir.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C as 'Id', aml.DisplayName as 'LocationName',

  ir.Title_9691DD10_7211_C835_E3E7_6B38AF8B8104 as 'Title',

  lt.LTValue as 'Classification',

  lt2.LTValue as 'SupportGroup',

  lt3.LTValue as 'Status',

  FORMAT(ir.CreatedDate_6258638D_B885_AB3C_E316_D00782B8F688, 'dd/MM/yyyy') as 'CreatedDate'

    FROM [ServiceManager].[dbo].[MT_Cireson$AssetManagement$Location] as aml

    JOIN dbo.Relationship as r

    ON  r.TargetEntityId = aml.BaseManagedEntityId and r.RelationshipTypeId='D96C8B59-8554-6E77-0AA7-F51448868B43' 

    JOIN dbo.BaseManagedEntity as bme

    ON r.SourceEntityId = bme.BaseManagedEntityId

    JOIN dbo.MT_System$WorkItem$Incident as ir

    ON bme.DisplayName = ir.DisplayName

    JOIN Dbo.LocalizedText as LT

    ON ir.Classification_00B528BF_FB8F_2ED4_2434_5DF2966EA5FA = LT.MPElementId

    AND Lt.LanguageCode='ENU'

    AND LT.LTStringType=1

    JOIN Dbo.LocalizedText as LT2

    ON ir.TierQueue_1E9615C2_3386_2452_BA83_05B2169DF38C = LT2.MPElementId

    AND LT2.LanguageCode='ENU'

    AND LT2.LTStringType=1

    JOIN Dbo.LocalizedText as LT3

    ON ir.Status_785407A9_729D_3A74_A383_575DB0CD50ED = LT3.MPElementId

    AND LT3.LanguageCode='ENU'

    AND LT3.LTStringType=1

    Where (ir.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C like 'IR%')

    

  
  You could then do the same thing with SR/PR/CR and do a union to join all the tables but that would be quite long for me to post here haha! Just make sure the AS 'ColumnNames' all match up for the union to work.
  
  Hope this helps
  
  Shane

  5
• Options

  Scott_Wright Customer IT Monkey ✭

  May 2019

  This is great!  For some reason, it's only finding 8 records, but it's enough to get me started.  Thank you for all your help!

  1