Data from CSV in to Bar Charts

[p0se]

Hello,

I am new to Grafana and have a problem mapping Time Series from a CSV file.

my CSV file gives me the following values.

The values come from a ticket system.
I have already picked out the necessary values that I want to display.

Ticketnummer (Unique number for a ticket, Ticket ID)
Geschlossen (Date when the ticket was closed)
Status (Ticket status)

I would like to see how many tickets were closed per year.
it would probably be enough to output the values of the table by year. So how many entries in the table are there for the year 2020, how many entries are there for 2021…

At the moment I get the following output.

The output should look something like this.

I guess I have to work with the transform function here, however I don’t know exactly how, does anyone have a tip for me?

[melori.arellano]

welcome to the community @p0se!

Is it possible to share the sample CSV data instead of a screenshot so the community can mock up your problem and give suggestions?

Can you share which values you’ve already filtered in your CSV? For example, does the CSV data only include closed status tickets for the year?

[yosiasz]

in sql server

SELECT  count(1),
        concat(year(geshloschen),' (', count(1) ,')')   AS metric
FROM tickets
GROUP BY year(geshloschen)

[yesoreyeram]

If you are using Infinity datasource, you can use the UQL to do this for you.

The UQL query I am using

parse-csv
| extend "Geschlossen"=todatetime("Geschlossen") # converts the Geschlossen string to actual date
| extend "rounded_date"=startofyear("Geschlossen") # rounds the Geschlossen to year
| summarize count() by "rounded_date" # group by date
| order by "rounded_date" asc

[p0se]

Thank you for your reply.

shure, here you can find the CSV.
https://drive.google.com/file/d/1o-n1dYxjvfI3t9SjOLo2QmgWkqOikSyr/view?usp=sharing

The CSV file contains many more fields.
I assumed that closed status is actually enough for my display.
I have already set the type for three fields an set “ignore unknown”

Ticketnummer= Number
Geschlossen= Time
Status= String

I also used Transform with the filter data bye values function to show me only the closed tickets.

Thanks for your help!

[p0se]

Thank you also for your contribution.

The CSV file is located locally on the Grafana server.
But via infinity datasource I can’t find a way to add local files.

Do you have an idea?
Unfortunately I could not find anything in the documentation.

Maybe there is another way to get the data.

The CSV file is from an OTRS export.
OTRS is a ticket system which also has an API.
https://doc.znuny.org/doc/api/otrs/6.0/Perl/

I wanted to save myself the trouble of exporting the data via the API and then importing it into another database to read it out with Grafana.

Therefore I thought the automatic CSV export via OTRS is easier.

The API is based on Perl.
Can I use Grafana to pull the data directly through the API?

[yosiasz]

There is a config setting that you have to set to allow local files. Couldn’t you hit the api from grafana directly?

[p0se]

I have set this setting, otherwise I would not be able to open local .csv files.

I just don’t know how to set a local file within the infinity datasource.

[yosiasz]

I only see two options. How to use csv stored locally in Grafana · yesoreyeram/grafana-infinity-datasource · Discussion #54 · GitHub

Maybe there is a programmatic way of embedding the csv file’s data into the inline Data section?

Another thing that worked for me was I spun up a small node express api that read the csv fiel from disk and pointed the infinity Source as URL to my node api. But I think that is gross. Might as well hit the original api and serve it up for this infinity plugin to consume it from an url.

[smoofoperator]

This might help: How to convert timestamp and then group by date?