Home General Discussion

Search

Alfie_ThomasAlfie_Thomas Customer IT Monkey ✭

We are trying to create searchs to go into views which shows tickets based on affected user office from AD. We are able to do this in SCSM but was wondering if there was a way to do this from the Cireson portal? 


Have attached a Screen shot of where I am, but want to add the affected users to then allow a view to then show a region.


Thank you.

Best Answer

Answers

  • Justin_WorkmanJustin_Workman Cireson Support Super IT Monkey ✭✭✭✭✭

    @Alfie_Thomas - SQL Table widget on a dashboard page would work for this.

    You could use something like this against ServiceManagement:

    DECLARE @Office nvarchar(32)

    SET @Office = '<office you are searching for here>'


    SELECT w.WorkItemId, 

    w.Title, 

    sds.DisplayString as 'Status',

    w.AssignedUser,

    w.AffectedUser,

    u.Office,

    tds.DisplayString as 'SupportGroup',

    w.Created,

    w.LastModified

    FROM WorkItem w

    JOIN CI$User u

    ON w.AffectedUserId = u.Id

    JOIN DisplayString sds

    ON sds.ElementID = w.StatusId

    AND sds.LocaleID = 'ENU'

    JOIN DisplayString tds

    ON tds.ElementID = w.TierId

    AND tds.LocaleID = 'ENU'

    WHERE u.Office = @Office

  • Alfie_ThomasAlfie_Thomas Customer IT Monkey ✭

    Thanks Justin,

    Such a swift reply as usual. In the portal this doesn't save the query it just stalls. I tried running it against the Servicemanagement DB but it fails with

    Msg 208, Level 16, State 1, Line 3

    Invalid object name 'WorkItem'.

    Is there a specific table I need to target?

  • Justin_WorkmanJustin_Workman Cireson Support Super IT Monkey ✭✭✭✭✭

    The WorkItem table should be present in ServiceManagement. If you expand the database, and expand tables in SSMS, do you see the WorkItem table?

  • Alfie_ThomasAlfie_Thomas Customer IT Monkey ✭

    Hi Justin,


    That worked but our DB is named SCServiceManagerCB so when I run the query from the portal, the datasource is ServiceManager and not our naming.


    Is there away to update the source Cireson is looking from in the portal as I think this is why it won't work. against the DB it works and returns data but not via Cireson portal.


    Thanks,

  • Justin_WorkmanJustin_Workman Cireson Support Super IT Monkey ✭✭✭✭✭
    Answer ✓

    @Alfie_Thomas - Can you raise a support ticket for this so we can take a closer look?

Sign In or Register to comment.