Tapping into SysAid ticketing information with .NET

 
Author
Message
SysAider
5
 
Hello! I am new to SysAid, and I was not involved with the setup portion so forgive any ignorance towards certain pieces of knowledge.

My employer has asked that I figure out how to pull information from SysAid.

I've experience with SQL & MySQL databases and the relevant languages that tie them all together. My issue, is that neither myself or my employer knows anything about our setup. All I was told was 'it is hosted on the cloud' and when I inquired about the possibility of getting myself a DB user with read access, that request was shot down. So, how do I access information within the tickets, externally, if I cannot tap into the database? Or can I?

Please help!

Thanks,

James

This message was edited 1 time. Last update was at Nov. 19, 2013 10:45 PM

SysAider
15
 
I am going out on a limb here, but i am guessing that you simply need access to the analytics tab. If there is any report that Sysaid has not provided that you need access to you can pay them to design one for you with the information you need. I have the self hosted version, so i am not sure what the policies are for the hosted version. If it is a matter of getting information to a 3rd party system like a pbx or something similar again i would suggest looking into downloading the sysaid api from the Settings gear then select the integration menu you will find the API download there.
SysAider
5
 
cooperj wrote:I am going out on a limb here, but i am guessing that you simply need access to the analytics tab. If there is any report that Sysaid has not provided that you need access to you can pay them to design one for you with the information you need. I have the self hosted version, so i am not sure what the policies are for the hosted version. If it is a matter of getting information to a 3rd party system like a pbx or something similar again i would suggest looking into downloading the sysaid api from the Settings gear then select the integration menu you will find the API download there.


Thanks for responding! We were already aware of the analytics tab and the information it presents. I'm sorry for asking questions without fully understanding the system.

So, from what I gathered there are two versions available? One that we could host, and a hosted option, which I'm assuming we then have because of the previous statement by my employer 'it lives in the cloud'.

As far as the API, it seems like it's Java only. I am intimately familiar with .NET, C# and have some web service knowledge as well as experience with jQuery. Through my personal research, I stumbled upon this link: https://www.sysaid.com/Sysforums/posts/list/9316.page which contains a few relevant tidbits of information leading me to believe it's POSSIBLE to tie sysaid and C#/.NET together to play nicely, I just have no references to work with.

Thanks for any and all help!

-James



Super SysAider
62
 
Dear JamesCook,

While SysAid has been developed with Java, our API is designed to be consumed as Web Service, with all available methods and objects defined in the WSDL.

The SysAid API is only available in the Full/Enterprise & MSP editions of SysAid.

You can find more information on the API at the following links:
API Guide: https://www.sysaid.com/Sysforums/templates/default/help/files/API.htm
API Java Doc: https://www.sysaid.com/Sysforums/templates/default/PM/apidoc/index.html

In terms of generating code from our API WSDL, you can easily do this through your preferred IDE (Eclipse, IntelliJ, NetBeans, Visual Studio, etc.).

In Visual Studio Express 2012 for example, it is as simple as starting a new project of your choosing then using the Project --> Add Service Reference control.
Simply enter the WSDL URL in to the Address field and click Go; the "SysAidApiServiceService" should then be listed under Services and you can specify your preferred Namespace (optional) and click OK.
To then view the methods and objects available from the API Service, use the Object Browser to find your Project Service Reference (see attached screen shot).

In your Class, make sure you are using the Service Reference namespace you created from the WSDL.

In order to connect to the API Service and begin loading records from the database, you will need to firstly create a new SysAidApiServiceClient object, which we will call the "service" object, which will be used to login, load and save records in the DB and logout.
Once you have the service object, you must get a Session ID (Long), which is returned from the login method.
The Login method signature is 3 Strings: your Account ID, a SysAid Administrator's username, the password for the same user.
Once you have the Session ID value, you can use this as the authentication token in all other service methods.

Immediately below is an example I wrote in C# as a test a while back - it will log in, retrieve an apiServiceRequest object populated from SR #10 and then output the ID and Status of the SR and log out.
Below that it is an example in VB.NET which a customer had posted on the Community here some time ago which will log in, create a New Service Request object and then populate some of the properties (Category, Sub Category, Title, Description, etc.), save the new SR and alert the SR details in a message box, close the same SR and then log out.
I can verify my C# code works, but I have not tested the VB.NET one - it reads like it will work as the main class once the WSDL has been parsed and objects created under the sysaidWebService namespace.

If you are comfortable with .NET, then this should definitely get you going
Let me know if I can be of any further assistance.

Cheers, Cael.


==============================================

This message was edited 1 time. Last update was at Oct. 02, 2015 10:18 AM

SysAider
5
 
Cael wrote:Dear JamesCook,

While SysAid has been developed with Java, our API is designed to be consumed as Web Service, with all available methods and objects defined in the WSDL.

The SysAid API is only available in the Full/Enterprise & MSP editions of SysAid.

You can find more information on the API at the following links:
API Guide: https://www.sysaid.com/Sysforums/templates/default/help/files/API.htm
API Java Doc: https://www.sysaid.com/Sysforums/templates/default/PM/apidoc/index.html

In terms of generating code from our API WSDL, you can easily do this through your preferred IDE (Eclipse, IntelliJ, NetBeans, Visual Studio, etc.).

In Visual Studio Express 2012 for example, it is as simple as starting a new project of your choosing then using the Project --> Add Service Reference control.
Simply enter the WSDL URL in to the Address field and click Go; the "SysAidApiServiceService" should then be listed under Services and you can specify your preferred Namespace (optional) and click OK.
To then view the methods and objects available from the API Service, use the Object Browser to find your Project Service Reference (see attached screen shot).

In your Class, make sure you are using the Service Reference namespace you created from the WSDL.

In order to connect to the API Service and begin loading records from the database, you will need to firstly create a new SysAidApiServiceClient object, which we will call the "service" object, which will be used to login, load and save records in the DB and logout.
Once you have the service object, you must get a Session ID (Long), which is returned from the login method.
The Login method signature is 3 Strings: your Account ID, a SysAid Administrator's username, the password for the same user.
Once you have the Session ID value, you can use this as the authentication token in all other service methods.

Immediately below is an example I wrote in C# as a test a while back - it will log in, retrieve an apiServiceRequest object populated from SR #10 and then output the ID and Status of the SR and log out.
Below that it is an example in VB.NET which a customer had posted on the Community here some time ago which will log in, create a New Service Request object and then populate some of the properties (Category, Sub Category, Title, Description, etc.), save the new SR and alert the SR details in a message box, close the same SR and then log out.
I can verify my C# code works, but I have not tested the VB.NET one - it reads like it will work, but I think it will not do what the comments suggest entirely...

If you are comfortable with .NET, then this should definitely get you going
Let me know if I can be of any further assistance.

Cheers, Cael.


==============================================



PERFECT! Thank you very much, this got me where I needed to be. I will reach out to you if I have any further questions!
SysAider
1
 
---

This message was edited 1 time. Last update was at Nov. 21, 2013 12:51 AM

Super SysAider
62
 
Glad I could help James

Plus I figure that this is a somewhat esoteric subject when it should not be. So I see it is beneficial to get the info out in the open where every one who needs it can find it.
SysAider
5
 
Cael wrote:Glad I could help James

Plus I figure that this is a somewhat esoteric subject when it should not be. So I see it is beneficial to get the info out in the open where every one who needs it can find it.


I'd like to press my luck and ask you for assistance with the following, as you're more familiar with the API than I!

I was asked to comb through these fields, and figure out if we can extrapolate said information from SysAid to our Portal in .NET.

Here are the things that we would like to extract, all I need to know is if it's possible to do each of these operations:



I'm combing through this link https://www.sysaid.com/Sysforums/templates/default/PM/apidoc/index.html trying to find things, but I figure asking the experts might speed my process up!

Thanks for any help,

James

This message was edited 1 time. Last update was at Nov. 22, 2013 10:19 PM

Super SysAider
62
 
Hi James,

Much of what you need to extrapolate would be easily done with direct queries on the DB, providing you are using MS SQL, MySQL or Oracle. (Only 1 connection at a time may be made to a Derby DB)
Please review our Database Guide here: https://www.sysaid.com/help-page.htm?helpPageId=5737&edition=2

You would just need to build the queries and dynamically define the date ranges for your samples.
To avoid unnecessary additional database load, you may want to return the results and insert them in to another DB for displaying on your portal.

The first few are simple;
Tickets Opened Count = SELECT count(id) WHERE insert_time BETWEEN x AND y
Tickets Closed Count = SELECT count(id) WHERE close_time BETWEEN x AND y
Tickets Closed Percentage = (opened / closed) * 100

The SLA calculations depend on how you are determining breaches.

FCR (First Call Resolution) calculations can be discerned using the Version value of the SR, as each save increments the version, in conjunction with close_time != null.

Escalation calculations can be determined using the [Max] Escalation Level or [Max] Support Level values of the SR, depending on how you are operating your Service Desk.

Average Time calculations can be easily retrieved from getting the Average from your Timers within the SR table.
Please see our Online Aid for more information on Timers: https://www.sysaid.com/Sysforums/helpData.page?helpId=4143.

Please Note that your Support and Maintenance may not cover database issues caused by you inserting anything in to the SysAid database, so please exercise caution and only read from the DB.

Hope that helps somewhat!
Let me know if you need any clarification or further info.

Cheers,
Cael
SysAider
5
 
Cael wrote:Hi James,

Much of what you need to extrapolate would be easily done with direct queries on the DB, providing you are using MS SQL, MySQL or Oracle. (Only 1 connection at a time may be made to a Derby DB)
Please review our Database Guide here: https://www.sysaid.com/help-page.htm?helpPageId=5737&edition=2

You would just need to build the queries and dynamically define the date ranges for your samples.
To avoid unnecessary additional database load, you may want to return the results and insert them in to another DB for displaying on your portal.

The first few are simple;
Tickets Opened Count = SELECT count(id) WHERE insert_time BETWEEN x AND y
Tickets Closed Count = SELECT count(id) WHERE close_time BETWEEN x AND y
Tickets Closed Percentage = (opened / closed) * 100

The SLA calculations depend on how you are determining breaches.

FCR (First Call Resolution) calculations can be discerned using the Version value of the SR, as each save increments the version, in conjunction with close_time != null.

Escalation calculations can be determined using the [Max] Escalation Level or [Max] Support Level values of the SR, depending on how you are operating your Service Desk.

Average Time calculations can be easily retrieved from getting the Average from your Timers within the SR table.
Please see our Online Aid for more information on Timers: https://www.sysaid.com/Sysforums/helpData.page?helpId=4143.

Please Note that your Support and Maintenance may not cover database issues caused by you inserting anything in to the SysAid database, so please exercise caution and only read from the DB.

Hope that helps somewhat!
Let me know if you need any clarification or further info.

Cheers,
Cael



Being that we're on the 'cloud' system, is there any additional documentation that might prove relevant to our specific setup in getting tied into the database?

I keep asking the powers that be for direct read access to the database but no one wants to respond to my inquiries, seemingly because I don't think that they understand what I'm asking for

This message was edited 1 time. Last update was at Nov. 25, 2013 11:49 PM

SysAider
5
 
Hi James,

We have the same problem... I've been asking for read only access to the database for ages but it seems that will never happen due to the SysAid security policies. But I did manage to get a copy of our database backup that I can use for reference in addition to the DB Guide to develop sql queries to extract data that I need. Once you have a working query you can build a report which can then scheduled to run at certain intervals. There is a lot of useful info available on developing a report from a query on a SysAid webinar recording (https://www.youtube.com/watch?v=2-y-BStXnOY). If there is a need, the reports can be emailed as excel which can then be accessed via a C# application and SysAid API to perform some maintenance functions that would otherwise be done directly through sql if full access to the db was available.

Looking at the information you need to extract, it will be easiest to follow the route of creating a report from a query and set it up to send the report to the relevant people at required intervals. Also, keep in mind that you have access to all the fields through reports, whereas the API fields are limited.

Although the webinar video should give you all you need, please feel free to let me know if you need more info.

Regards,
Kobus

This message was edited 1 time. Last update was at Nov. 26, 2013 09:06 AM

Super SysAider
62
 
Sorry James, my info is not entirely helpful after all, completely missed the fact you are on the cloud.

I can attest to you not being allowed to connect to the cloud database, however SysAid will happily provide you with a dump of your database at your request, as Kobus mentioned.

Take a look at the reports available within SysAid already and then check out the Webinar which Kobus linked to above.
https://www.sysaid.com/Sysforums/templates/default/help/files/MnAnalyzer.htm

If you have the Manager Dashboard module, then you can create your own reports and even design custom reports using the report designing software suite iReport (compatible version: 3.7.6)
https://sourceforge.net/projects/ireport/files/iReport/iReport-3.7.6/

I apologise for getting your hopes up with my advice.
Good luck and if I can be of any further assistance don't hesitate to PM me.
Cheers,
Cael