topic Re: Show Splunk field based on count of another field on a bar chart in Dashboards & Visualizations

How to show Splunk field based on count of another field on a bar chart?

POR160893 — Wed, 10 Aug 2022 14:23:08 GMT

Hi,

I have a table of data which I need to display the count of "Migration Stratus" in a bar chart.

Here is the raw data:

Location	Number of Devices	Migration Status	When Planned
Bangalore, India	10	Not Started	Not Known
Cork, IRE	4	Not Started	Not Known
Eldorado Du Sol, Brazil	3	Not Started	Not Known
Hopkinton, USA	4	Not Started	Not Known
Otemachi, Japan	3	Not Started	Not Known
Cyberjaya, Malaysia	4	Not Started	Not Known
Limerick, IRE	4	Not Started	Not Known
Austin, USA	6	Not Started	Not Known
Penang, Malaysia	5	Not Started	Not Known
Durham, USA	6	Not Started	Not Known
Singapore, Singapore	4	Not Started	Not Known
Santa Clara, USA	2	Not Started	Not Known
Sydney, Australia	2	In Progress	FY23 Q2
Xiamen, China	6	Not Started	Not Known

Here is my current output, where when I hoover over "In Progress" it shows 1 and over "Not Started", shows 13:

At the moment, my query is just counting the number of rows for a particular value in "Migration Status" and this value is what is seen when hoovering over each bar on bar chart:

| inputlookup Migration-Status-Symantec3.csv
| fillnull value=null
| eval dummy = 'Migration Status'
| chart count over "Migration Status" by dummy

What I need is the SUMMATION of "Number of Devices" for a particular value in "Migration Status".

How can this be achieved????

Many thanks as always 🙂

Re: Show Splunk field based on count of another field on a bar chart

ITWhisperer — Wed, 10 Aug 2022 09:53:49 GMT

| chart sum('Number of Devices') over "Migration Status" by dummy

Re: Show Splunk field based on count of another field on a bar chart

POR160893 — Wed, 10 Aug 2022 10:40:17 GMT

I tried this but seems it is not picking up the dummy variable:

What you think?

Re: Show Splunk field based on count of another field on a bar chart

ITWhisperer — Wed, 10 Aug 2022 10:51:48 GMT

Looks like you might not need the quotes in the sum function

| chart sum(Number of Devices) over "Migration Status" by dummy

Re: Show Splunk field based on count of another field on a bar chart

POR160893 — Wed, 10 Aug 2022 12:46:10 GMT

Similarly,

For the same dashboard but different data source, the data is as follows:

I need to find total number for each category in "Migration Comments" but the same query is not working?

What may be the issue?

Thanks for help so far btw!

Re: Show Splunk field based on count of another field on a bar chart

ITWhisperer — Wed, 10 Aug 2022 13:07:00 GMT

You are trying sum strings - I wouldn't have thought that that was what you wanted to do?

Re: Show Splunk field based on count of another field on a bar chart

POR160893 — Wed, 10 Aug 2022 13:21:59 GMT

You are correct actually 🙂

I didn't articulate my issue exactly.

I have the following query:
| inputlookup Migration-Status-All.csv
| search Vendor = "McAfee"
| eval Migration_Status=case(like('Planned Migration Completion Iteration', "Decom by%"), "Done", 'Planned Migration Completion Iteration'=="In progress", "In progress", 1==1, "Not Started")
| table Location, Migration_Status, "Planned Migration Completion Iteration"
| streamstats count as Number_of_Devices by Location

But:

As you can, there are multiple entries for the same Location .... I need the sum(Number_Of_Devices) by Location and have that sum to be placed under "Number_of_Devices".

Is this possible?

Re: Show Splunk field based on count of another field on a bar chart

ITWhisperer — Wed, 10 Aug 2022 13:30:43 GMT

I am not sure why you are using streamstats

| stats count as Number_of_Devices by Location

Re: Show Splunk field based on count of another field on a bar chart

POR160893 — Wed, 10 Aug 2022 13:55:52 GMT

The only reason I used the streamstats command is I need the following 4 columns:
"Location", ""Migration Status", "Planned Migration Completion Iteration", "Number of Devices".

But there is no count of devices per location.
When I run your query, I get this:

So, there are missing columns .....
Not sure why

Re: Show Splunk field based on count of another field on a bar chart

ITWhisperer — Wed, 10 Aug 2022 14:13:51 GMT

Try eventstats if you want to keep the other fields

| eventstats count as Number_of_Devices by Location

Re: Show Splunk field based on count of another field on a bar chart

POR160893 — Wed, 10 Aug 2022 14:18:20 GMT

Still no luck ....

Re: Show Splunk field based on count of another field on a bar chart

ITWhisperer — Wed, 10 Aug 2022 14:25:17 GMT

What would you like these results to look like?

Re: Show Splunk field based on count of another field on a bar chart

POR160893 — Wed, 10 Aug 2022 14:33:47 GMT

I need the results to look like the following:

Re: Show Splunk field based on count of another field on a bar chart

ITWhisperer — Wed, 10 Aug 2022 14:37:18 GMT

Isn't that what you already had with your streamstats solution?

Re: Show Splunk field based on count of another field on a bar chart

POR160893 — Wed, 10 Aug 2022 16:17:43 GMT

No, streamstats gave me individual rows for the same country .... should be 1 entry p2r location:

In above screenshot, should be 1 entry got "Bratislava" with "Number of Devices" = 2

Re: Show Splunk field based on count of another field on a bar chart

ITWhisperer — Wed, 10 Aug 2022 16:21:58 GMT

Those are two different locations, one is in Slovakia, the other is in SLovakia

Re: Show Splunk field based on count of another field on a bar chart

POR160893 — Wed, 10 Aug 2022 17:48:52 GMT

Changing how "Slovakia" was spelled sadly didn't make any difference:

All I need is a count(device) by Country 🙄

Re: Show Splunk field based on count of another field on a bar chart

ITWhisperer — Wed, 10 Aug 2022 20:26:02 GMT

Well it did make a difference - the count now has 1 and 2

Re: Show Splunk field based on count of another field on a bar chart

POR160893 — Wed, 10 Aug 2022 20:32:02 GMT

Hahaha touche Sir ... sadly, it is not the change I need 😞
I need a single row entry per location with the total number of devices for that given location ....

Re: Show Splunk field based on count of another field on a bar chart

ITWhisperer — Wed, 10 Aug 2022 20:38:32 GMT

| stats count as "Number of Devices" values(Migration Status) as "Migration Status" values(Planned Migration Completion Iteration) as "Planned Migration Completion Iteration" by Location