Need SQL queries for number new/open/closed tickets

[kruegerM]

My management is looking for some Excel spreadsheets/charts with the number of new/closed/still open tickets on a weekly basis. I thought I had the queries down, but my closed numbers aren't matching what I get from running searches through the interface. The query I'm currently using is:

select count(*) from ticket where change_time >= '2016-09-19 00:00:00' and change_time <= '2016-09-25 23:59:59' and ticket_state_id in (2,3,9,10) and archive_flag = 0

(The above query is what I used for last week)
Can someone tell me what I'm missing? I'd do it through the interface except what's not shown above is this is being done for nine (9) different groups, so queue_id's are also in the mix... But even without the queue_id's, if I run the above to get all closed tickets, my numbers aren't matching...

My query for New tickets matches what I get through the interface - which is why I'm chasing the Closed one.

Any ideas greatly appreciated.

[wurzel]

Hi,

why not use the statistic module for this? Native export in excel. And graphical output on dashboard if you like.

Flo

[kruegerM]

Management has decided on a single sheet showing all nine groups and individual numbers broken out by priority within each group - a chart for each, and then a department-wide section. All on one sheet. Using vb and odbc/sql calls is the simplest method to give them what they are looking for.

[wurzel]

Hi,

OK. Understood. I can not help you with SQL.

Flo

[root]

You should no reinvent the logic, use the GenericInterface operation TicketSearch from Excel by using VB as a client.
The list of the possible parameter is documented here: https://otrs.github.io/doc/api/otrs/4.0 ... ch.pm.html

[kruegerM]

Thanks.
Does anyone have any examples on how they have produced this?
Learning API calls and determining how to call from/to Excel will take a bit longer than I have been given to produce this output ...
Any help / suggestions appreciated.

[root]

Which version of Excel?

[kruegerM]

2013
though I also have 2010 and 2003 available ...

[root]

Then you should be able to access the GenericInterface and use the TicketSearch operation. Examples can bei found in the forums and using a search engine.

[kruegerM]

Thanks much. I'll start digging.

[kruegerM]

Ran into a couple of snags - could use help unravelling.

1) Looking through all the tags within 'TicketSearch', I don't see anything for dates, so not sure how I would search for tickets closed between two dates, for example.

2) When I run my script, I get an msxml3.dll error "The server returned an invalid or unrecognized response". The apache log shows the corresponding error: Message: Could not load web service configuration for web service at
//otrs/nph-genericinterface.pl/Webservice/GenericTicketConnector'

Here is the code construct I'm working with:

Set objHttp = CreateObject("MSXML2.ServerXMLHTTP")
sURL = "http://10.11.35.212//otrs/nph-genericin ... tConnector'"
sEnv = "<?xml version=""1.0"" encoding=""utf-8""?>"
sEnv = sEnv & "<soap:Envelope xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" xmlns:soap=""http://schemas.xmlsoap.org/soap/envelope/"">"
sEnv = sEnv & " <soap:Body>"
sEnv = sEnv & " <TicketSearch>"
sEnv = sEnv & " <UserLogin>my_uid</UserLogin>"
sEnv = sEnv & " <Password>my_pw</Password>"
sEnv = sEnv & " <QueueIDs>33</QueueIDs>"
sEnv = sEnv & " <StateIDs>2,3,9,10</StateIDs>"
sEnv = sEnv & " </TicketSearch>"
sEnv = sEnv & " </soap:Body>"
sEnv = sEnv & "</soap:Envelope>"
objHttp.Open "GET", sURL, False
objHttp.setRequestHeader "Content-Type", "text/xml"
objHttp.send sEnv


again, any pointers would be appreciated.

[root]

Without any experience on GI SOAP - I use the REST Connector, I would say a body isn't send with a GET request. Did you tried POST request?

[kruegerM]

POST request results in the same errors.
I haven't investigated the REST connector - guess that will be my next stop.

[kruegerM]

OK - changed GenericTicketConnector to be REST instead of SOAP. Tried a "simple" curl request:
curl http://otrstest/otrs/nph-genericinterfa ... ogin=<myID>
get the response: curl: (52) Empty reply from server
apache error_log has
Could not load web service configuration for web service at /otrs/nph-genericinterface.pl/Webservice/GenericTicketConnectorREST/Ticket/128767?UserLogin=<myid>

When I enter the cURL command, I am adding &Password=<mypw> but it's not showing up in the errorlog. But the error of not loading the configuration is continuing.

M

[root]

That my working curl request

Code: Select all

curl -v -X GET -k 'https://xxx/otrs/nph-genericinterface.pl/Webservice/GenericTicketConnectorREST/Ticket/392?UserLogin=xxx&Password=xx'

Possible errors:

- special characters are not URL encoded
- the used user has no access to the ticket
- Using & for parameter separation (UserLogin=x&Password=y) without escaping. & could be a special char for your shell

[kruegerM]

Thanks - Here is the result I get using the syntax you provided:

* About to connect() to xxxxxx port 80 (#0)
* Trying xx.xx.xx.xxx... connected
* Connected to xxxxx (xx.xx.xx.xxx) port 80 (#0)
> GET /otrs/nph-genericinterface.pl/Webservice/GenericTicketConnectorREST/Ticket/130656?UserLogin=xxxx&Password=xxxx HTTP/1.1
> User-Agent: curl/7.19.7 (x86_64-redhat-linux-gnu) libcurl/7.19.7 NSS/3.21 Basic ECC zlib/1.2.3 libidn/1.18 libssh2/1.4.2
> Host: xxxxx
> Accept: */*
>
* Empty reply from server
* Connection #0 to host otrstest left intact
curl: (52) Empty reply from server
* Closing connection #0


Apache error_log file shows the following:

Message: Could not load web service configuration for web service at /otrs/nph-genericinterface.pl/Webservice/GenericTicketConnectorREST/Ticket/130656?UserLogin=xxxxx&Password=xxxxx

I did verify my GenericTicketConnector transport is set to HTTP::REST
This is what I entered:
curl -v -X GET -k 'http://xxxx/otrs/nph-genericinterface.p ... sword=xxxx'

[root]

I'll create you a working test system and send the credentials via pm.

[kruegerM]

Thank for the system check. I can't believe all this chasing for a typo.


As I said in my PM, I'm now getting proper response from my cURL command, so consider me smiling.
Now -- to get Excel VBA to respond.

[kruegerM]

I lost it again ... The REST configuration works when I provide a TicketID. What if I need all the tickets in a queue and don't have IDs?

[kruegerM]

Never Mind - found it. ...../Ticket?UserLogin.... etc etc etc
From now on I drink ALL my coffee before trying to code.

[root]

TicketSearch - The URL is for the GET request is

Code: Select all

/otrs/nph-genericinterface.pl/Webservice/GenericTicketConnectorREST/Ticket?UserLogin=x&Password=y

And here you find the parameter, add the to the URL. Like

Code: Select all

/otrs/nph-genericinterface.pl/Webservice/GenericTicketConnectorREST/Ticket?UserLogin=x&Password=y&Queues=Raw

For multiple malues just repeat the parameter:

Code: Select all

/otrs/nph-genericinterface.pl/Webservice/GenericTicketConnectorREST/Ticket?UserLogin=x&Password=y&Queues=Raw&Queues=Misc

The call return all matching TicketIDs. And keep in mind that here is a configirable limit on the search result: GenericInterface::Operation::TicketSearch###SearchLimit

[kruegerM]

Do you know if there a limit in results? I'm only getting 500 tickets from a search. With your help on REST, I managed to get a SOAP run working too - but it also is only returning 500 hits.
I can't find anywhere that Excel VBA has a limit to items being pulled so now I'm backtracking to see if maybe the API does? I know when I run a search through the interface, it limits out at 2000 hits...

[root]

And keep in mind that here is a configirable limit on the search result: GenericInterface::Operation::TicketSearch###SearchLimit

I wrote about it This is a SysConfig setting

[kruegerM]

You're right - and that fixed it, thanks!
One last (?) note - Using REST - if I put in my queues as ..&Queues=Raw&Queues=Junk.. only Junk get's pulled. That is - if I put in multiple 'Queues' entries, only the last one counts.

[root]

On my systems all mentioned queues were pulled. Did you use the original apache include file?

[kruegerM]

yes. have I missed a change (again)?

[kruegerM]

Don't know why I cant get the REST api piece to work with multiple queues, but SOAP is working flawlessly for me so I'll run with it. Thank you again for all your help!