Create Custom Reports in OTRS and Retrieve "Age" from DB

[zyousafi]

I have written an extremely complex SQL query and I want to create a customized report in OTRS with it. How can I go about doing this? I have looked through all of the *.pm files and I was unable to find any where I could simply enter a query and run that report from the system itself. Right now I am running the report using PuTTy and PGADMIN3. The reason I am having to do this is because OTRS has not exposed the ticket_history table in the statistics module and I am unable to acquire some very basic historical data.

The second part of the query is with reference to the "Age" value that is associated with each and every single ticket. I can see the "Age" value in the GUI but I can't find the value itself in any of the tables. What's worst I can't figure out which two values I can use to calculate it myself. Can anyone help me here?

[crythias]

Age = now minus ticket create date.

[zyousafi]

Thank you for the prompt response crythias; however the problem with that is non-working hours would be considered as well. I want to calculate "Age" as it is being calculated in the system (i.e. now minus create date minus non-working hours). Any idea how I can do that?

[RStraub]

If you take a look at the API of the TimeObject, there's a function that let's you calculate (working-) time diff with three params:
- Date 1
- Date 2
- Calendar to use

[zyousafi]

Where can I find the API of the TimeObject RStraub? Let's say I find the API of the TimeObject, could I make this calculation via the DB directly? Or would I need to execute Perl script for it?

[crythias]

https://otrs.github.io/doc/api/otrs/sta ... me.pm.html

could I make this calculation via the DB directly? Or would I need to execute Perl script for it?

The math will probably boggle your SQL skills.

It's probably best to create a perl module that asks OTRS what it knows and use it for a stats report.

[zyousafi]

yes crythias my SQL skills will be put to the test; however I have a team of professionals who can help me with that. they just don't know have enough knowledge about OTRS and they don't know where that information would be stored. is there a way to be able to calculate this via SQL? what i, or my colleagues, don't have is PERL skills and that's why I would like to avoid that if at all possible.

[crythias]

Like I said, Age is now minus CreateTime:
https://github.com/OTRS/otrs/blob/rel-4 ... t.pm#L1200

Code: Select all

    $Ticket{Age} = $TimeObject->SystemTime() - $Ticket{CreateTimeUnix};

Perhaps you want WorkingTime:
https://github.com/OTRS/otrs/blob/rel-4 ... me.pm#L414

[zyousafi]

I want "Age" as it is calculated in the system. Yes it's "Now () - CreateTime" however it takes the settings in the calendar into consideration. Is there anyway to do that via the DB.

[crythias]

want "Age" as it is calculated in the system. Yes it's "Now () - CreateTime" however it takes the settings in the calendar into consideration.

Good luck. I don't know how you come to that conclusion, especially since I showed you where age is calculated. And no, there is no magic that moves create time or now because of calendar.

[zyousafi]

Okay well that's interesting. When I evaluated "Age" it seems it only counts working hours. The working hours that the user defines in the calendar. I will look at the code you shared again and track it in the system.