OTRS Reports

Moderator: crythias

Post Reply
luizfernandogondin
Znuny newbie
Posts: 7
Joined: 23 Dec 2015, 18:01
Znuny Version: 4.0.11
Real Name: Luiz Fernando Gondin
Company: Sucesso

OTRS Reports

Post by luizfernandogondin »

Hi guys! I'm starting to evaluate OTRS as a service desk software for my company and I've been reading and testing for the past 3 months. I've been able to set everything right (except for skins, but it's not a big deal) and the reports I needed.

First I created a thread in portuguese area (since Iam a Braziliam guy) but none of them answered, so I thought that some of you might know.

Portuguese thread > viewtopic.php?f=126&t=31103

I've been using some service desk softwares along the way (Kace, CA, Tivoli and etc) and all of them have a place where you can actually write a SQL query that fits whatever you need from a report without relying on a secondary app to do it for you.

After some google and lots of reading I think that OTRS does not have such "feature" in the community edition, is that right? If so, can anyone give me a hint about which software should I use to generate specifics reports?

My goal is to let users from different departments extract their on data from their queues.

The report is the last thing I need to set in order to put things in motion towards OTRS for production enviroment.

I'll appreciate any help.

Best Regards,
jojo
Znuny guru
Posts: 15019
Joined: 26 Jan 2007, 14:50
Znuny Version: Git Master
Contact:

Re: OTRS Reports

Post by jojo »

Hi,

OTRS can already gerate different reports via the build in statistic module. If you need external reporting you can use nearly all classical report generators (like crytal reports, Jasper etc) via SQL.
"Production": OTRS™ 8, OTRS™ 7, STORM powered by OTRS
"Testing": ((OTRS Community Edition)) and git Master

Never change Defaults.pm! :: Blog
Professional Services:: http://www.otrs.com :: enjoy@otrs.com
luizfernandogondin
Znuny newbie
Posts: 7
Joined: 23 Dec 2015, 18:01
Znuny Version: 4.0.11
Real Name: Luiz Fernando Gondin
Company: Sucesso

Re: OTRS Reports

Post by luizfernandogondin »

Hi Jojo! Thanks for replying. I tried to create reports using the stats module but they never get to work properly :P

The most wanted report on my company is the most (first 10) departaments that open tickets, the top users that open tickets, so they can analyze trends and act more effectly on the problem itself.

What I find odd is that when you create a new report using the builder, and choose "TicketAccumulation" there's no filter option for the last 10,15 entries so, it will list everything that matches the report parameter. While I know that users can export to excel and make it there, the current outdated software already has builtin reports that generates this data for them.

And you know how stubborn a user can be :P

Do you know if there's a way to build a report like that?

Best regards,
eandrex
Znuny expert
Posts: 213
Joined: 04 Nov 2012, 23:58
Znuny Version: OTRS 4.x
Real Name: Esteban
Company: NORTON DE COLOMBIA

Re: OTRS Reports

Post by eandrex »

Well, the reports you need dont look too complex and they can be generated in the SQL Box and export as CSV.

Log in as administrator, go to "Admin" -> "SQL Box" (Bottom right).

However, by reading your initial post, it seems like you want a mechanism for your customers to generate their own reports and as far as I know, OTRS in its vanilla version, does not support that feature (You would need to create a frontend module for the customer interface)
luizfernandogondin
Znuny newbie
Posts: 7
Joined: 23 Dec 2015, 18:01
Znuny Version: 4.0.11
Real Name: Luiz Fernando Gondin
Company: Sucesso

Re: OTRS Reports

Post by luizfernandogondin »

Thanks for your answer eandrex! Yeah, the SQL box helped me a lot! (Wondering how I did not see that before! hehehe)

It would be great if the customers (that is actually agents from another departments) could generate their reports by themselves. I'll google it for how to create new frontends and see if that fits their monthly reports.

The best scenario would be to have the SQL box inside stats reports for the agents generate whatever they need.

I'll get back to this post later and update with my findings, as I suspect many others will have doubts about the stats area.

If anyone has another ideas on how to acomplish new reports in OTRS, feel free to share within this post.

Best regards,
eandrex
Znuny expert
Posts: 213
Joined: 04 Nov 2012, 23:58
Znuny Version: OTRS 4.x
Real Name: Esteban
Company: NORTON DE COLOMBIA

Re: OTRS Reports

Post by eandrex »

Hello,

If your "customers" are literally agents that can login in the agent interface (otrs/index.pl), then *there is* a workaround you can do in order to acomplish what you need.

Workaround:
-Create as many groups as deparments you have.
-Agents that need to generate their own reports must belong to one of those deparments at least.
-Per each report you create, make sure you select the group(s) that will have access to it (here is when you select the group(s) you created in the step before)

and if you want the "SQL Box" option to appear in the "Stats" menu, create a file in
/opt/otrs/Kernel/Config/Files/
for example Stats.xml
and paste this

Code: Select all

<?xml version="1.0" encoding="utf-8" ?>
<otrs_config version="1.0" init="Changes">
<ConfigItem Name="Frontend::Module###AgentStats" Required="0" Valid="1">
        <Description Translatable="1">Frontend module registration for the agent interface.</Description>
        <Group>Framework</Group>
        <SubGroup>Frontend::Agent::ModuleRegistration</SubGroup>
        <Setting>
            <FrontendModuleReg>
                <Title Translatable="1">Stats</Title>
                <Group>stats</Group>
                <GroupRo>stats</GroupRo>
                <Description>Stats</Description>
                <NavBarName>Stats</NavBarName>
                <NavBar>
                    <Name Translatable="1">Statistics</Name>
                    <Link>Action=AgentStats;Subaction=Overview</Link>
                    <LinkOption></LinkOption>
                    <NavBar>Stats</NavBar>
                    <Type>Menu</Type>
                    <Description Translatable="1"></Description>
                    <Prio>8500</Prio>
                    <Block>ItemArea</Block>
                    <AccessKey></AccessKey>
                </NavBar>
                <NavBar>
                    <GroupRo>stats</GroupRo>
                    <Description Translatable="1">Overview</Description>
                    <Name Translatable="1">Overview</Name>
                    <Link>Action=AgentStats;Subaction=Overview</Link>
                    <LinkOption></LinkOption>
                    <NavBar>Stats</NavBar>
                    <Prio>100</Prio>
                    <Type></Type>
                    <Block></Block>
                    <AccessKey></AccessKey>
                </NavBar>
                <NavBar>
                    <Group>stats</Group>
                    <Description Translatable="1">New</Description>
                    <Name Translatable="1">New</Name>
                    <Link>Action=AgentStats;Subaction=Add</Link>
                    <LinkOption></LinkOption>
                    <NavBar>Stats</NavBar>
                    <Prio>200</Prio>
                    <Type></Type>
                    <Block></Block>
                    <AccessKey></AccessKey>
                </NavBar>
                <NavBar>
                    <Group>stats</Group>
                    <Description Translatable="1">Import</Description>
                    <Name Translatable="1">Import</Name>
                    <Link>Action=AgentStats;Subaction=Import</Link>
                    <LinkOption></LinkOption>
                    <NavBar>Stats</NavBar>
                    <Prio>300</Prio>
                    <Type></Type>
                    <Block></Block>
                    <AccessKey></AccessKey>
                </NavBar>
                <NavBar>
                    <Group>admin</Group>
                    <Description Translatable="1">SQL Box</Description>
                    <Name Translatable="1">SQL Box</Name>
                    <Link>Action=AdminSelectBox</Link>
                    <LinkOption></LinkOption>
                    <NavBar>Stats</NavBar>
                    <Prio>400</Prio>
                    <Type></Type>
                    <Block></Block>
                    <AccessKey></AccessKey>
                </NavBar>
                <Loader>
                    <JavaScript>Core.Agent.Stats.js</JavaScript>
                </Loader>
            </FrontendModuleReg>
        </Setting>
    </ConfigItem>
</otrs_config>
after doing so, make sure you rebuild config (otrs.RebuildConfig) and set permissions(otrs.SetPermissions)

Three things:
-Only agents in the "admin" group would have access to that option (Change according to your needs)
-Since you didnt provide OTRS version, I assumed it is version 4.
-I havent tested it, but it could work..
luizfernandogondin
Znuny newbie
Posts: 7
Joined: 23 Dec 2015, 18:01
Znuny Version: 4.0.11
Real Name: Luiz Fernando Gondin
Company: Sucesso

Re: OTRS Reports

Post by luizfernandogondin »

Hi eandrex! Thx for your help :-) U're saving lives here!

Yep, iam using OTRS v4 and I was able to create the SQL box inside stats with your script, but what I meant is that I can build reports via SQL queries and it would be great if we could do it using the stats window settings, like permissions that is already in place e.q. paste the damn query and save it as a report :lol:

Using stats "wizard" I was able to create some of my reports and they are alreay visible to their own groups (since the report has some fixed itens, I needed to copy and upload the same report for each department and make some changes in filters).

What I could not find in stats wizard is how to count ticktes (ticketaccumulation) and at the same time, order by the top users and filter the last results. The filter "Last x results" only appears when I select "TicketList" which actually list all tickets instead of counting them.

Iam not a programmer at all, so Iam trying to learn whatever I need to make OTRS "happen" :lol:

Best regards,
Post Reply