Home› Analyst Portal

How to obtain "created date" as a column in a saved search ?

Stephane_BouillonStephane_Bouillon Customer Advanced IT Monkey ✭✭✭
in Analyst Portal
When I create a search for incidents or service requests, the columns available do not contain the "Created Date". How can I add this column or is there another way to obtain that information for an exportable list of items ? I'm running v8.2 of the portal.

Best Answer

Answers

  • Brian_WiestBrian_Wiest Customer Super IT Monkey ✭✭✭✭✭
    Build a SQL Table Widget. 
    Add the columns you want and then you will have the export to csv option
  • Stephane_BouillonStephane_Bouillon Customer Advanced IT Monkey ✭✭✭
    Thanks for your time and effort Brian, I tried to create a simple query but I'm struggling with the translation of the enumerations. This is what I have for trying to convert the TierId into the support group name: I get results, but the Tier column is missing

    SELECT wi.WorkItemId AS 'Id', wi.Created, wi.AffectedUser, wi.Title, wi.impact, wi.urgency, wi.StatusId, wi.AssignedUser, wi.TierId, ds.DisplayString Tier, wi.LastModified, wi.ResolvedDate, wi.ClosedDate<br>FROM WorkItem AS wi <br>LEFT JOIN [Enumeration] enum ON enum.EnumerationID = wi.TierId <br>INNER JOIN [DisplayString] ds ON ds.ElementID=enum.EnumerationID

  • Brian_WiestBrian_Wiest Customer Super IT Monkey ✭✭✭✭✭
    You are close, Updated to match ones I use and that I know work in the portal. 

    SELECT wi.WorkItemId AS [Id], 
    wi.Created, 
    wi.AffectedUser, 
    wi.Title, 
    dsimpact.DisplayString as [Impact], 
    dsurgency.DisplayString as [Urgency], 
    dsstatus.DisplayString as [Status], 
    wi.AssignedUser, 
    dssupport.DisplayString as [Tier], 
    wi.LastModified, 
    wi.ResolvedDate, 
    wi.ClosedDate
    FROM WorkItem AS wi 

    LEFT JOIN [Enumeration] enum ON enum.EnumerationID = wi.TierId 
    LEFT JOIN [DisplayString] dssupport ON dssupport.ElementID=enum.EnumerationID 
    LEFT JOIN [DisplayString] dsstatus ON dsstatus.ElementID=wi.StatusId
    LEFT JOIN [DisplayString] dsimpact ON dsimpact.ElementID=wi.Impact
    LEFT JOIN [DisplayString] dsurgency ON dsurgency.ElementID=wi.urgency
  • Stephane_BouillonStephane_Bouillon Customer Advanced IT Monkey ✭✭✭
    Thanks I'm almost there, I added a TOP 3000 in the SELECT statement and an ORDER BY wi.Created DESC


    For some reason I'm getting a lot of duplicates in the result. My SQL skills are very rusty, no idea what I might be doing wrong ?
  • Brian_WiestBrian_Wiest Customer Super IT Monkey ✭✭✭✭✭
    Share you current code
  • Stephane_BouillonStephane_Bouillon Customer Advanced IT Monkey ✭✭✭
    I noticed that the query substitutes the enumeration values in different languages and creates a row for every possible combination of impact and urgency in three languages (Low-Faible-Laag, Medium-Moyen-Gemiddeld,  High-Elevée,Hoog)
  • Stephane_BouillonStephane_Bouillon Customer Advanced IT Monkey ✭✭✭
    Also the Status is translated. I only need the English data
  • Stephane_BouillonStephane_Bouillon Customer Advanced IT Monkey ✭✭✭
    When I look at the SQL query for one of the default ones it contains a statement (@LanguageCode, 'ENU') but the syntax is complicated. Where should I put that in my code ?

    SELECT TOP 3000 wi.WorkItemId AS [Id],&nbsp;wi.Created,&nbsp;wi.AffectedUser,&nbsp;wi.Title,&nbsp;dsimpact.DisplayString as [Impact],&nbsp;dsurgency.DisplayString as [Urgency],&nbsp;dsstatus.DisplayString as [Status],&nbsp;wi.AssignedUser,&nbsp;dssupport.DisplayString as [Tier],&nbsp; wi.LastModified,&nbsp;wi.ResolvedDate,&nbsp;wi.ClosedDate<br>FROM WorkItem AS wi&nbsp;<br>LEFT JOIN [Enumeration] enum ON enum.EnumerationID = wi.TierId&nbsp;<br>LEFT JOIN [DisplayString] dssupport ON dssupport.ElementID=enum.EnumerationID&nbsp;<br>LEFT JOIN [DisplayString] dsstatus ON dsstatus.ElementID=wi.StatusId<br>LEFT JOIN [DisplayString] dsimpact ON dsimpact.ElementID=wi.Impact<br>LEFT JOIN [DisplayString] dsurgency ON dsurgency.ElementID=wi.urgency<br>ORDER BY wi.Created DESC

  • Stephane_BouillonStephane_Bouillon Customer Advanced IT Monkey ✭✭✭
    Great !

    Thanks for your time and effort Brian, it's a life saver !
Sign In or Register to comment.