Dashboards & Visualizations

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

POR160893
Builder

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:

POR160893_0-1660124699563.png


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 🙂

Labels (1)
0 Karma
1 Solution

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

View solution in original post

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

POR160893
Builder

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

POR160893_0-1660128009550.png



What you think?

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

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

POR160893
Builder

Similarly,

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

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

POR160893_1-1660135556438.png

 




What may be the issue?


Thanks for help so far btw!

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

POR160893
Builder

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:

POR160893_0-1660137706168.png

 



 


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?

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

I am not sure why you are using streamstats

| stats count as Number_of_Devices by Location

POR160893
Builder

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".

POR160893_0-1660139710487.png

 

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

POR160893_1-1660139605951.png

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

Tags (1)
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Try eventstats if you want to keep the other fields

| eventstats count as Number_of_Devices by Location

POR160893
Builder

Still no luck ....

POR160893_0-1660141092832.png

 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

What would you like these results to look like?

POR160893
Builder

I need the results to look like the following:

POR160893_1-1660141989760.png



0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

POR160893
Builder

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

POR160893_0-1660148207097.png

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

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

POR160893
Builder

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

POR160893_0-1660153723978.png

 



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

Tags (1)
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

POR160893
Builder

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 ....

0 Karma

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

POR160893
Builder

We got there :)))))
Thanks so much for your help on this Sir. (I obviously gave you Kudos for all your messages 😀)

Tags (1)
0 Karma
Get Updates on the Splunk Community!

Admin Your Splunk Cloud, Your Way

Join us to maximize different techniques to best tune Splunk Cloud. In this Tech Enablement, you will get ...

Cloud Platform | Discontinuing support for TLS version 1.0 and 1.1

Overview Transport Layer Security (TLS) is a security communications protocol that lets two computers, ...

Splunk Cloud Platform | New Customer Testimonials

Enterprises of all sizes and across different industries are accelerating cloud adoption by migrating ...