How to Calculate Percentage for a single value and display both Value and Percentage in the Bracket in the same Panel

[Mallik657]

I have a Sample Data like below. Now i need to display single value count of Completed and Pending in 2 different single value panel with their percentage in the bracket. (Screenshot is Attached)

Total=10
Completed=6
Pending=4

Now I need to display Single value count of completed 6(60%) and second single value count of Pending 4(40%) with Percentage in the bracket in the 2 Panels show in Photo.

Please provide me the query

ServerName             UpgradeStatus
==========         =============
Server1                     Completed
Server2                     Completed
Server3                     Completed
Server4                     Completed
Server5                     Completed
Server6                     Completed
Server7                     Pending
Server8                     Pending
Server9                     Pending
Server10                  Pending

[yuanliu]

---

1.  index=abc source="/opt/src/datasource.tmp" | dedup _raw | table Servers | stats count(Servers) as Total
2.  index=abc source="/opt/src/datasource.tmp" | dedup _raw | table CompletedServers | stats count(CompletedServers) as Completed

As @PickleRick points out, these searches you posted reveal potentially deeper problems that is your data.  If there is a need to dedup _raw, you should try clean up data first.  Also, there should never be two separate index searches using the same source.  PickleRick already illustrated a single search to get the same counts.   Let me further point out that most likely, the two searches produce the exact same count if Servers and CompletedServers appear in the same events.

But back to your original table

---

ServerName             UpgradeStatus
==========         =============
Server1                     Completed
Server2                     Completed
Server3                     Completed
Server4                     Completed
Server5                     Completed
Server6                     Completed
Server7                     Pending
Server8                     Pending
Server9                     Pending
Server10                  Pending

---

Obviously, neither of your searches will provide those "Pending" ones.  When asking a question in a public forum, it is really important to explain your input and output.  It is obvious that you did not think @sainag_splunk's and my previous answers did not give you the solution because you didn't even have the table. Because If you did, either of our searches will have given you the table you needed.

So, I venture to guess that the real question is how to derive the first table from the index data you have.  Once this table is formed, either of our suggestions would have given you the display you wanted.  Is this correct?

Back to the problem of UpgradeStatus.  When I point out that your searches do not produce Pending values, the big question is: What is in CompletedStatus?  Does it give "Completed" for some ServerName, and "Pending" for others?  And what is the field name that gives you ServerName?  Is it Servers used in your first search?

If both are true, and that ServerName and CompletedStatus appear in the same events, the solution is as simple as

index=abc source="/opt/src/datasource.tmp"
| stats dc(Servers) as count by CompletedStatus
| eventstats sum(count) as total
| eval count = count . " (" . round(count / total * 100) . "%)"
| fields - total
| transpose header_field=CompletedStatus
| fields - column

In other words, all that change from my previous answer is field names that I guess from the two meaningless searches.

Here are my four commandments of asking answerable data analytics questions:

• Illustrate data input (in raw text, anonymize as needed), whether they are raw events or output from a search that volunteers here do not have to look at.
• Illustrate the desired output from illustrated data.
• Explain the logic between illustrated data and desired output without SPL.
• If you also illustrate attempted SPL, illustrate actual output and compare with desired output, explain why they look different to you if that is not painfully obvious.

Volunteers are not your mind readers.  It is unfair to ask unanswerable questions here.

[Mallik657]

1. I need to dedup servers in first query. Count only Unique Servers. 

2. I need to consider all the Servers with duplicates in the second query and then CompletedStatus then count the completed. 

|.  

index=abc source="/opt/src/datasource.tmp" | dedup _raw | dedup Servers | table Servers | stats count(Servers) as Total

||.  

index=abc source="/opt/src/datasource.tmp" | dedup _raw | table Servers, CompletedStatus | stats count(CompletedStatus) as Completed

So 2 queries are compulsory. 

[PickleRick]

OK. There are several issues with your searches and maybe your data.

1. dedup _raw - that means you're removing duplicates of _whole events_. Which implies you have those duplicate events (otherwise the command would be unnecessary). That is not a typical situation. Or you simply don't need this comman (or you meant something completely different by it)

2. The table command very rarely makes sense in the middle of the search. Additionally, it is a performance hit in a distributed environment because it moves all procesing to search head tier so you don't benefit from map-reduce distributed processing. Table can be used at the end to present results in tabular view.

3. If you want to count distinct values of a field do just that. Doing strange stuff with dedup can fail you miserably if you have multivalued fields.

So again, if you want to count distinct values of those two fields (and so far you haven't said otherwise) the search with single stats command containing two aggregations is completely sufficient.

[yuanliu]

@sainag_splunk's solution should work.  A less literal, but more traditional way to do this is

| stats dc(ServerName) as count by UpgradeStatus
| eventstats sum(count) as total
| eval count = count . " (" . round(count / total * 100) . "%)"
| fields - total
| transpose header_field=UpgradeStatus
| fields - column

Here is an emulation

| makeresults format=csv data="ServerName,             UpgradeStatus
Server1,                     Completed
Server2,                     Completed
Server3,                     Completed
Server4,                     Completed
Server5,                     Completed
Server6,                     Completed
Server7,                     Pending
Server8,                     Pending
Server9,                     Pending
Server10,                  Pending"
| stats dc(ServerName) as count by UpgradeStatus
| eventstats sum(count) as total
| eval count = count . " (" . round(count / total * 100) . "%)"
| fields - total
| transpose header_field=UpgradeStatus
| fields - column

[Mallik657]

I have provided the sample data. I have huge data in few thousand lines. Which is pushed to Splunk. Query should be generic to accept any data size.

[sainag_splunk]

looking at the data, you can use something like this.

| makeresults
| eval servers="Server1,Server2,Server3,Server4,Server5,Server6,Server7,Server8,Server9,Server10"
| eval statuses="Completed,Completed,Completed,Completed,Completed,Completed,Pending,Pending,Pending,Pending"
| makemv delim="," servers
| makemv delim="," statuses
| mvexpand servers
| mvexpand statuses

| stats
count as total_servers,
count(eval(statuses="Completed")) as completed_count,
count(eval(statuses="Pending")) as pending_count

| eval completed_percentage = round(completed_count / total_servers * 100, 0)
| eval pending_percentage = round(pending_count / total_servers * 100, 0)

| eval "Completed Servers" = completed_count . " (" . completed_percentage . "%)"
| eval "Pending Servers" = pending_count . " (" . pending_percentage . "%)"

| fields "Completed Servers", "Pending Servers"

please upvote if this is helpful.

[Mallik657]

I have provided the sample data. I have huge data in few thousand lines. Which is pushed to Splunk. Query should be generic to accept any data size. Its not just 10 values.

[sainag_splunk]

Thank you for your feedback. We appreciate your engagement and aim to provide the best assistance possible based on the information shared in the community forum.

The query provided was our best effort to address your question using the details you offered. Community members, including myself, volunteer our time and knowledge to help fellow Splunk users.

If you need more tailored assistance or have specific requirements not covered in the initial query, there are a couple of options:

1. You can provide more detailed information about your data structure and exact requirements in the forum. This would help us refine the solution further.
2. For more in-depth, real-time support where you can share your screen and get personalized guidance, Splunk offers ondemand services. These allow for "shoulder surfing" and can address your specific needs more directly. 

Regarding the query itself, you can adapt the latter part to your specific environment as mentioned by @yuanliu  or me. 

| stats count as total_servers, count(eval(status_field="YourCompletedStatus")) as completed_count, count(eval(status_field="YourPendingStatus")) as pending_count | eval completed_percentage = round(completed_count / total_servers * 100, 0) | eval pending_percentage = round(pending_count / total_servers * 100, 0) | eval "Completed Servers" = completed_count . " (" . completed_percentage . "%)" | eval "Pending Servers" = pending_count . " (" . pending_percentage . "%)" | fields "Completed Servers", "Pending Servers"

Replace index, source, sourcetype, status_field, YourCompletedStatus, and YourPendingStatus with your specific values. This should work with your actual data structure.

We're here to help, and we hope this guidance proves useful for your specific use case.

Thanks.

[Mallik657]

Hi Sir,

I have two queries like below and I need to join both of queries and then divide Completed by Total and calculate percentage and display the percentage in bracket along with Completed count as shown in the above screenshot in panel.

1.  index=abc source="/opt/src/datasource.tmp" | dedup _raw | table Servers | stats count(Servers) as Total
2.  index=abc source="/opt/src/datasource.tmp" | dedup _raw | table CompletedServers | stats count(CompletedServers) as Completed

[PickleRick]

1. Each of your searches is unnecessarily complicated (and thus less efficient than it could be). You can cut the whole dedup and table part and get the same results.

2. Are you sure that count(Servers) and count(CompletedServers) respectively is what you need? It might be - I don't know your use case - but typically it's either the general count of results or distinct count of a field. Counts of fields are rare the proper solution. But there are uses for it. Yours might be one of them so just asking if you know what you're doing.

3. You don't have to join anything. Assuming the counts are OK, you simply need one search to calculate both stats

index=abc source="/opt/src/datasource.tmp"
stats count(CompletedServers) as Completed count(Servers) as Total

And that's it.