topic Re: Line graph using csv in Splunk Enterprise

Line graph using csv

sandyjov1 — Tue, 11 Jan 2022 18:20:56 GMT

I am attempting to make a line graph with information from a csv w/ info from the past year.

	Nov 2020	December 2020	January 2021	February 2021
Events	19	9	5	7
Cleared	3	1	1	7
Incidents	3	1	1	0
False Positives	16	8	4	7

I need each category to have its own line on a line graph. The Months would be on x axis (nov20-nov21). It doesn't seem complicated, but cant seem to get the results . Any help would be appreciated. Thanks

Re: Line graph using csv

ITWhisperer — Tue, 11 Jan 2022 18:49:22 GMT

You need to use transpose, then get rid of some added fields and sort the dates

| makeresults | eval _raw="Category,November 2020,December 2020,January 2021,February 2021 Events,19,9,5,7 Cleared,3,1,1,7 Incidents,3,1,1,0 False Positives,16,8,4,7" | multikv forceheader=1 | fields - linecount _raw _time ``` the lines above set up some sample data ``` | transpose 0 header_field=Category column_name=Date | eval Date=strptime(Date."-01","%B_%Y-%d") | where isnotnull(Date) | sort 0 Date | fieldformat Date=strftime(Date,"%B %Y")

Re: Line graph using csv

sandyjov1 — Tue, 11 Jan 2022 19:27:09 GMT

Thank you for help!
Is there a better way for me to setup my csv, so that I wouldn't need to transpose?

Re: Line graph using csv

bowesmana — Tue, 11 Jan 2022 21:50:59 GMT

Transpose the CSV, so the rows are months and the columns are categories. That is how the timechart command will work to produce data for a time based chart where date is on the X axis.

Re: Line graph using csv

ITWhisperer — Tue, 11 Jan 2022 21:52:02 GMT

Put the dates in the first column and the categories across the top - it depends on how you set it up in the first place

Re: Line graph using csv

sandyjov1 — Tue, 11 Jan 2022 22:01:00 GMT

Thanks!
what would the timechart query look like if I fixed my csv that way?

Re: Line graph using csv

bowesmana — Wed, 12 Jan 2022 04:14:04 GMT

If your CSV looks like this

and it's in a file called events.csv, then all you need is this query

| inputlookup events.csv | eval Date=strptime(Date."-01","%B %Y-%d") | sort 0 Date | table Date *

You don't need to use the timechart as your data is already in the format a timechart would create

@ITWhisperer 's solution is perfectly good for your data as it stands, so unless it makes sense for you to change the data, just use his solution. If you are going to use the data in many places, just create a macro that would do all the initial load and transpose so your query just uses the macro to keep it clean. If you change your data format you just change the macro and everything will continue to work.

Re: Line graph using csv

sandyjov1 — Wed, 12 Jan 2022 18:05:45 GMT

Appreciate your help!

Re: Line graph using csv

sandyjov1 — Wed, 12 Jan 2022 18:06:29 GMT

Thanks, this worked! Appreciate your efforts 🙂