Stats similar to dashboard 7 days stats

[kishore]

How to create statisc report similar to 7 days stats o dashboard.

I want to get number of tickets created and number of tickets closed on each day in the same statistics report.

I have tried to create new statistics report I can only get either number of tickets closed or number of tickets created, but not both in the same report.

The reason when creating a new statistics report of type "TicketAccumulation" I can only select one X-axis element with radio button.

Is there any way to create the report that i need.

Thanks,
Kishore

[crythias]

It's a bit tough to get that number.

The current ticket states are in the ticket table.

Code: Select all

select count(if (change_time >= DATE_SUB(CURDATE(), INTERVAL 7 DAY), if (ticket_state_id='2', 1, if (ticket_state_id='3', 1, NULL)), NULL)) AS closed, count(if (create_time >= DATE_SUB(CURDATE(), INTERVAL 7 DAY), 1, NULL)) AS open from ticket

You can drop this in your SQL box.

Definitely not something simple to code for a generic case.

What does it do?
If the change_time was in the last 7 days, count if the current state is closed successfully or closed unsuccessfully (based upon the ids in ticket_state table).
If the create_time was in the last 7 days, give a count of that, too.

[crythias]

oops. That's a sum of all tickets in the past 7 days.
hah.

Here you go:

Code: Select all

select DATE_FORMAT(change_time, "%m-%d-%Y") seven, count(if (change_time >= DATE_SUB(CURDATE(), INTERVAL 7 DAY), if (ticket_state_id='2', 1, if (ticket_state_id='3', 1, NULL)), NULL)) AS closed, count(if (create_time >= DATE_SUB(CURDATE(), INTERVAL 7 DAY), 1, NULL)) AS open from ticket where change_time >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) group by seven

What's different?
Added the date for the last 7 days, assumed that new and closed will both have a change_time in the last 7 days, and I am grouping by the date.

[kishore]

ThankQ very much for your response. I will try what you suggested.

[kishore]

The results from the query does not match the values in Dashboard 7 days stats. Both the closed count and created count does not match.

[crythias]

What can I say?
hmm.
Maybe your stats view is covering different states or different permissions.
Do you have standard states and state ids or do you have custom ones?

[crythias]

Closed count should match. Created may not match if they were created and closed within the same period.

I cheated and grabbed from tickets, not articles.

the concepts should similarly apply, but I may not get back to this for a few days.

[srivatsatatti]

Can i have a stats in Dashboard for Escalated tickets v/s open tickets instead of Closed and Open.

[crythias]

Get the ticket state IDs and replace them in the query.

[srivatsatatti]

Thanks Crythias,

Get the ticket state IDs and replace them in the query.

But there is no states for Escalated tickets.


In DashboardTicketStatsGenric.pm, the code counts the total number of tickets in b/w two times by searching

Code: Select all

 my $CountCreated = $Self->{TicketObject}->TicketSearch(

            # cache search result 30 min
            CacheTTL => 60 * 30,

            # tickets with create time after ... (ticket newer than this date) (optional)
            TicketCreateTimeNewerDate => "$Year-$Month-$Day 00:00:00",

            # tickets with created time before ... (ticket older than this date) (optional)
            TicketCreateTimeOlderDate => "$Year-$Month-$Day 23:59:59",

            CustomerID => $Param{Data}->{UserCustomerID},
            Result     => 'COUNT',

            # search with user permissions
            Permission => $Self->{Config}->{Permission} || 'ro',
            UserID => $Self->{UserID},
        );
        if ( $CountCreated && $CountCreated > $Max ) {
            $Max = $CountCreated;
        }
        push @TicketsCreated, [ 6 - $Key, $CountCreated ];

[kishore]

I am trying the below code but did not work for me. May be you can do anything more. Please report back if you mange to get it.

Code: Select all

	my $CountOpen = $Self->{TicketObject}->TicketSearch(

            # cache search result 30 min
            CacheTTL => 60 * 30,

			# tickets with open state
			States => 'open',


            CustomerID => $Param{Data}->{UserCustomerID},
            Result     => 'COUNT',

            # search with user permissions
            Permission => $Self->{Config}->{Permission} || 'ro',
            UserID => $Self->{UserID},
        );
        if ( $CountOpen > $Max ) {
            $Max = $CountOpen;
        }
        push @TicketsOpen, [ 6 - $Key, $CountOpen ];

[srivatsatatti]

I could Fix this,by editing the times

Code: Select all

 # tickets with create time after ... (ticket newer than this date) (optional)
            TicketCreateTimeNewerDate => "$Year-$Month-$Day 00:00:00",

            # tickets with created time before ... (ticket older than this date) (optional)
            TicketCreateTimeOlderDate => "$Year-$Month-$Day 23:59:59",

TicketEscalationResponseTimeNewerDate and TicketEscalationResponseTimeOlderDate
TicketEscalationSolutionTimeNewerDate and TicketEscalationSolutionTimeOlderDate

[srivatsatatti]

I am trying the below code but did not work for me. May be you can do anything more. Please report back if you mange to get it.

Code: Select all

	my $CountOpen = $Self->{TicketObject}->TicketSearch(

            # cache search result 30 min
            CacheTTL => 60 * 30,

			# tickets with open state
			States => 'open',


            CustomerID => $Param{Data}->{UserCustomerID},
            Result     => 'COUNT',

            # search with user permissions
            Permission => $Self->{Config}->{Permission} || 'ro',
            UserID => $Self->{UserID},
        );
        if ( $CountOpen > $Max ) {
            $Max = $CountOpen;
        }
        push @TicketsOpen, [ 6 - $Key, $CountOpen ];

@Kishore Guess you have to use StateType instead of States.

[kishore]

Thank you!!

It worked with statetype.

Code: Select all

        push @TicketsClosed, [ 6 - $Key, $CountClosed ];
		
		my $CountOpen = $Self->{TicketObject}->TicketSearch(
	

            # cache search result 30 min
            CacheTTL => 60 * 30,

			# tickets with open state
			StateType  => 'open',
			



            CustomerID => $Param{Data}->{UserCustomerID},
            Result     => 'COUNT',

            # search with user permissions
            Permission => $Self->{Config}->{Permission} || 'ro',
            UserID => $Self->{UserID},
        );
        if ( $CountOpen > $Max ) {
            $Max = $CountOpen;
        }
        push @TicketsOpen, [ 6 - $Key, $CountOpen ];
		$Self->{LogObject}->Log( Priority => 'error', Message => $CountOpen );
		
    }

[cf3d]

Sorry for necro'ing this.

I am trying to do the same thing here, I want to display open tickets. When I do the edits, the stats widget disappears.

Here is what I have added/edited in the DashboardTicketStatsGeneric.pm file for version 3.1.11

Code: Select all

my @TicketsOpen = ();

Code: Select all

      my $CountOpen = $Self->{TicketObject}->TicketSearch(
   

            # cache search result 30 min
            CacheTTL => 60 * 30,

         # tickets with open state
         StateType  => 'open',
         



            CustomerID => $Param{Data}->{UserCustomerID},
            Result     => 'COUNT',

            # search with user permissions
            Permission => $Self->{Config}->{Permission} || 'ro',
            UserID => $Self->{UserID},
        );
        if ( $CountOpen > $Max ) {
            $Max = $CountOpen;
        }
        push @TicketsOpen, [ 6 - $Key, $CountOpen ];
      $Self->{LogObject}->Log( Priority => 'error', Message => $CountOpen );
      

Code: Select all

	my $OpenText = $Self->{LayoutObject}->{LanguageObject}->Get('Open');

and

Code: Select all

        {
            data  => \@TicketsOpen,
            label => $OpenText,
            color => "#6F98DF"
        }

Any help would be appreciated. Thanks

[crythias]

@cf3d: Yes, this is a year old. No, I'm not sure how what you want is like this topic/thread.

You should create a new topic.