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

[POR160893]

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 🙂

[ITWhisperer]

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

[POR160893]

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

What you think?

[ITWhisperer]

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

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

[POR160893]

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!

[ITWhisperer]

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

[POR160893]

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?

[ITWhisperer]

I am not sure why you are using streamstats

| stats count as Number_of_Devices by Location

[POR160893]

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

[ITWhisperer]

Try eventstats if you want to keep the other fields

| eventstats count as Number_of_Devices by Location

[POR160893]

Still no luck ....

[ITWhisperer]

What would you like these results to look like?

[POR160893]

I need the results to look like the following:

[ITWhisperer]

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

[POR160893]

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

[ITWhisperer]

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

[POR160893]

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

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

[ITWhisperer]

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

[POR160893]

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

[ITWhisperer · Accepted solution]

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

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