CA Service Management

  • Private Community
 View Only

  • 1.  Querying Activity Log (Status updates) in WEBI

    Posted Jul 18, 2014 02:57 PM

    SDM 12.6

    I'm trying to create some basic WEBI reports based on the activity log, specifically to find tickets with specific status changes. For example:

    Status changed from '[Initial]' to 'Closed'

    If I put something like "Description Matches pattern" and the above (with % at the end), nothing is matched. When I check the generated SQL it seems like it is converted the single quotes in to two singles, resulting in:

    SELECT
  alg.request_ref_num,
  alg.description
FROM
  alg
WHERE
  (
   alg.description  LIKE  '%Status changed from ''[Initial]'' to ''Closed''%'
   AND
   alg.request_ref_num  IN  ( '309843'  )
  )

    I've tried escaping the single quote ( \'[Initial]\' ), replacing it with _ ( _[Initial]_ )

    If I cut the query off earlier

    Status changed from%

    That works, but will potentially find more than the desired results. Really hoping I'm just missing something simple, anyone else gotten this sort of query to work?

    Also, the ref_num is just in there to limit the query for testing, the end goal is to find tickets with certain status changes.

     

    Edit: Kept testing, turns out its the square brackets causing the problem, not the quotes. Trying the following worked

    Status changed from % to 'Closed'%

    Escaping the bracket \[ didn't seem to work either



  • 2.  Re: Querying Activity Log (Status updates) in WEBI

    Posted Jul 18, 2014 03:07 PM

    Do you have the KPI daemon installed?  Here is another ideal situton for its use.  -fred SDM12.7 CABI 3.2 (SP5)



  • 3.  Re: Querying Activity Log (Status updates) in WEBI

    Posted Jul 18, 2014 03:15 PM

    We do not, this was just a quick example though, there are a few other activity based queries we have been asked about (transfers between specific teams being another one) so not sure that would satisfy all requests. Something to look in to though.



  • 4.  Re: Querying Activity Log (Status updates) in WEBI

    Posted Jul 18, 2014 03:22 PM

    KPI Daemon addresses the transferring between Groups as well!  See https://communities.ca.com/thread/241690443?sr=stream



  • 5.  Re: Querying Activity Log (Status updates) in WEBI

    Posted Jul 18, 2014 03:29 PM

    Interesting, thats a recent thread too so not sure how I missed it. I think we discussed enabling the option when we last upgraded and there was concern that it would cause a performance hit so we were instructed not to. Comments in that thread make it seem like that is not an issue. I assume enabling that option now would only capture data going forward correct? Or will it process the historical info as well?



  • 6.  Re: Querying Activity Log (Status updates) in WEBI

    Posted Jul 18, 2014 04:01 PM

    It will just capture data from the moment Service Desk comes back from a restart with the feature enabled in Options Manager.



  • 7.  Re: Querying Activity Log (Status updates) in WEBI
    Best Answer

    Posted Jul 18, 2014 03:13 PM

    AAAAAAAAAAAND just to answer my own question, looks like I got it. Escaping the brackets needs its own kind of format. The below works as expected

    Status changed from '[[]Initial]' to 'Closed'%

    From sql server - How can I escape square brackets in a LIKE clause? - Stack Overflow