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 🙂
| stats count as "Number of Devices" values(Migration Status) as "Migration Status" values(Planned Migration Completion Iteration) as "Planned Migration Completion Iteration" by Location
| chart sum('Number of Devices') over "Migration Status" by dummy
I tried this but seems it is not picking up the dummy variable:
What you think?
Looks like you might not need the quotes in the sum function
| chart sum(Number of Devices) over "Migration Status" by dummy
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!
You are trying sum strings - I wouldn't have thought that that was what you wanted to do?
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?
I am not sure why you are using streamstats
| stats count as Number_of_Devices by Location
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
Try eventstats if you want to keep the other fields
| eventstats count as Number_of_Devices by Location
Still no luck ....
What would you like these results to look like?
I need the results to look like the following:
Isn't that what you already had with your streamstats solution?
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
Those are two different locations, one is in Slovakia, the other is in SLovakia
Changing how "Slovakia" was spelled sadly didn't make any difference:
All I need is a count(device) by Country 🙄
Well it did make a difference - the count now has 1 and 2
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 ....
| stats count as "Number of Devices" values(Migration Status) as "Migration Status" values(Planned Migration Completion Iteration) as "Planned Migration Completion Iteration" by Location
We got there :)))))
Thanks so much for your help on this Sir. (I obviously gave you Kudos for all your messages 😀)