topic Re: How do I create column chart using two fields and keep more fields in the statistical table? in Splunk Search

How do I create column chart using two fields and keep more fields in the statistical table?

LearningGuy — Thu, 25 Jan 2024 21:22:04 GMT

Hello,

How do I create bar chart using two fields and keep all fields in the statistical table?
The column chart automatically created the following chart below.
My intention is to create a report emailed periodically with all the fields, but the column chart only two fields
If I used table command only to show Name and GPA, it showed two graph, but it removed the rest of the fields

Please suggest. Thanks

StudentID	Name	GPA	Percentile	Email
101	Student1	4	100%	Student1@email.com
102	Student2	3	90%	Student2@email.com
103	Student3	2	70%	Student3@email.com
104	Student4	1	40%	Student4@email.com

| makeresults format=csv data="StudentID,Name,GPA,Percentile,Email
101,Student1,4,100%,Student1@email.com
102,Student2,3,90%,Student2@email.com
103,Student3,2,70%,Student3@email.com
104,Student4,1,40%,Student4@email.com"

Current graph

Expected result

Re: How do I create column chart using two fields and keep more fields in the statistical table?

ITWhisperer — Fri, 26 Jan 2024 00:01:20 GMT

You could try using a dashboard with a charting option

Re: How do I create column chart using two fields and keep more fields in the statistical table?

LearningGuy — Fri, 26 Jan 2024 02:25:35 GMT

Hello @ITWhisperer ,
I wonder why I didn't get notification when you responded.
Is it possible to only display 2-field column chart in a weekly report, but with all fields in the statistics table?
Thank you for your help.

Re: How do I create column chart using two fields and keep more fields in the statistical table?

scelikok — Fri, 26 Jan 2024 08:36:28 GMT

Hi @LearningGuy,

You can add a statistics table to show all values like below addition to @ITWhisperer solution;

<dashboard version="1.1" theme="light"> <label>Test</label> <row> <panel> <chart> <search> <query>| makeresults format=csv data="StudentID,Name,GPA,Percentile,Email 101,Student1,4,100%,Student1@email.com 102,Student2,3,90%,Student2@email.com 103,Student3,2,70%,Student3@email.com 104,Student4,1,40%,Student4@email.com"</query> <earliest>-24h@h</earliest> <latest>now</latest> </search> <option name="charting.chart">column</option> <option name="charting.drilldown">none</option> <option name="charting.data.fieldShowList">[Name,GPA]</option> </chart> </panel> </row> <row> <panel> <table> <search> <query>| makeresults format=csv data="StudentID,Name,GPA,Percentile,Email 101,Student1,4,100%,Student1@email.com 102,Student2,3,90%,Student2@email.com 103,Student3,2,70%,Student3@email.com 104,Student4,1,40%,Student4@email.com"</query> <earliest>-24h@h</earliest> <latest>now</latest> </search> <option name="drilldown">none</option> </table> </panel> </row> </dashboard>

Re: How do I create column chart using two fields and keep more fields in the statistical table?

PickleRick — Fri, 26 Jan 2024 11:18:21 GMT

I think you're talking about two different things.

@ITWhispereris showing you how to create a dashboard showing what you want whereas you want a report, which is simply a scheduled search. I don't think you can do this in just a report. The report lets you manage some settings of the visualization but the visualized data is the full set of results that you get in the results table.

Re: How do I create column chart using two fields and keep more fields in the statistical table?

ITWhisperer — Fri, 26 Jan 2024 11:50:57 GMT

You can schedule a dashboard as a PDF which is why I showed how you can determine which fields are used from the search in the dashboard - you could also include a table in the dashboard using the same results

<dashboard version="1.1" theme="light"> <label>Test</label> <row> <panel> <chart> <search> <query>| makeresults format=csv data="StudentID,Name,GPA,Percentile,Email 101,Student1,4,100%,Student1@email.com 102,Student2,3,90%,Student2@email.com 103,Student3,2,70%,Student3@email.com 104,Student4,1,40%,Student4@email.com"</query> <earliest>-24h@h</earliest> <latest>now</latest> <done> <set token="sid">$job.sid$</set> </done> </search> <option name="charting.chart">column</option> <option name="charting.drilldown">none</option> <option name="charting.data.fieldShowList">[Name,GPA]</option> </chart> </panel> </row> <row> <panel> <table> <search> <query>| loadjob $sid$</query> <earliest>-24h@h</earliest> <latest>now</latest> </search> </table> </panel> </row> </dashboard>

Re: How do I create column chart using two fields and keep more fields in the statistical table?

LearningGuy — Fri, 26 Jan 2024 16:02:25 GMT

Hello,

If I put your suggested search into a search in Splunk, it didn't work, but I was able to create a dashboard using your search in Splunk. I was also able to export into PDF manually by clicking export=>download PDF

1) How do I schedule a dashboard as a PDF? Should I create dashboard first, then put it on reports?
My goal is to send an email once a week with a report for specific time frame (e.g. 30 days) to determine a ranking.

2) What is the purpose of token=sid and <done> bracket?

Thanks

Re: How do I create column chart using two fields and keep more fields in the statistical table?

ITWhisperer — Fri, 26 Jan 2024 17:26:16 GMT

When the search completes, the done stanza is executed and in this instance sets a token using the job information from the search.

Re: How do I create column chart using two fields and keep more fields in the statistical table?

LearningGuy — Fri, 26 Jan 2024 18:15:56 GMT

This is great info.. thanks for providing the explanation.
However I only have two options: Export PDF and Print, I couldn't see "Schedule PDF delivery"