7 Replies Latest reply on Nov 7, 2012 5:51 AM by Julian Wigman

    Total number of open calls at start of month

    Apprentice

      We would like to create a dashboard which displays open calls at the start of the month using queries in landesk.

       

      Since the status of calls might be resolved (but had been opened at the start of the month), the condition "status is end" cannot be used.

       

      Does anyone know if such a query is possible within Landesk and if so what conditional criteria would be used?

       

      Thanks

        • 1. Re: Total number of open calls at start of month
          Jamie Cannon ITSMMVPGroup

          Just trying to get a full understanding fo what you are looking for.

           

          You want to display all calls that are currently open that were created from previous months?  Or just a query that will show everything that is open?

           

          What I am thinking is you could do something like:

           

          Status.Title is not equal to "your closed status here"

           

          or you could try

           

          Status.Title is not one of "(add several statues here)"

           

          then use "creation date" is not Current Month if you want to filter out items that weren't created in the current month.  This should show only items that are open but not created in the month you are currently in.

           

          Hope this helps a little?  If I'm way off, just give me a little more detail and I can help.

          • 2. Re: Total number of open calls at start of month
            Apprentice

            Hi James,

             

            Thanks for your response.  To answer your question, I am trying to display all calls that where open at the start of the month.  I started out with the intention of using the "status.title" but that will just include calls with the current status of open.

             

            The idea is to have a dashboard that shows open calls at the start of the month and all calls resolved during the month.  I can do the resolved bit using the resolution.creation date (is current month) but just not sure how to get all calls that where "open" since some of those calls will be resolved as the month progresses.

             

            Hope that makes sense.

            • 3. Re: Total number of open calls at start of month
              Jamie Cannon ITSMMVPGroup

              Just use something like:

               

              Status.Title not one of _________________

               

              or Status.Title is one of _________________

               

              This way you can filter out the statuses you don't want it to pull (like Resolved).  I use Status.Tile is not one of (Resolved, Closed) all the time to pull any tickets that are "Open", "With 3rd Party", "Under Review", etc.  Then just pull where "creation date" is not Current Month and that should give you what you want as far as tickets currently not Resolved or Closed that were created at any point prior to the month you are in.

              • 4. Re: Total number of open calls at start of month
                ITSMMVPGroup

                It might be worth looking at MI if you have that option?  It's job is to stash a KPI (like the number of incidents open 'now') away in it's own area so that no matter what the status of an incident is later on, you can reliably know many were open at the point in time MI ran.  It also worth looking at the following article by the knowledge guru herself

                 

                http://community.landesk.com/support/docs/DOC-26166

                 

                My only other thought would be to run a scheduled job which set a flag 'This incident was open at the start of the month'.  Run the calculation every day, but make it so that it only really does something when it's the first of a month and the incident being looked at is not resolved or closed or ...You will not be able to go back retrospectively without a scripted solution, but at least going forward you'd get those stats building up.

                1 of 1 people found this helpful
                • 5. Re: Total number of open calls at start of month
                  Apprentice

                  Thanks David,

                   

                  That is really helpful.  Will take a look at MI for our upgrade.

                  • 6. Re: Total number of open calls at start of month
                    Apprentice

                    Thanks for the repsonse Jamie,

                     

                    I think David has clarified the requirements as I had not explained it very well.  We are looking to display the number of open incidents at a particular point if time, which means that using "status.title" is redundant since this will only display the current state of calls.  For example, in mid november, we want a query that displays the total of open calls on November 1st.

                     

                    I think David has clarified the ways in which this has been achieved and thanks again for responding.

                    • 7. Re: Total number of open calls at start of month
                      Julian Wigman ITSMMVPGroup

                      Hi,

                       

                      Might be missing something here but if you want at the start of the current month then IMHO you were along the right lines with your resolution creation date:

                       

                      ie IMHO should be something like:

                       

                      Resolutions.Creation Date  =  Is This Month

                      or

                      Resolutions.Creation Date  = is NULL

                       

                      if you want to run for previous months then I'd propose a prompted query along the lines of:

                       

                      Resolutions.Creation Date  =  Is Greater Than  (Prompted)

                      or

                      Resolutions.Creation Date  = is NULL

                       

                      Then at the prompt enter 01/xx/2012 00:00:00 at the prompt, where xx is you month in focus.

                       

                      You could also create a SQL view that does the magic of linking all of your tables together and selecting setting a sql calculated column (x) of the last month start where it was open.  Then point a new data connection at this view.  Then you can write a QueryTool query against the virtual Business Object this conncetion defines and just pick a single criteria of  calculated column X = Is This Month.

                       

                      Failing all thet then dive out to Crystal or MS Reporting Services to do the report and then embed that into console as a shortcut?

                       

                      Regards

                       

                      Julian