How do I create a pie chart based on total vs specific app logins?

[daveywfii]

I am looking to create a simple pie chart that contrasts the total number of users during any give timeframe vs how many logged into a specific app. I am probably over thinking this, but what I did is a search for distinct_count of users during a period and then joined another search that calculates the distinct_count of users that logged into a specific app over that same period. For example: 

index="okta" "outcome.result"=SUCCESS displayMessage="User single sign on to app"
| stats dc(actor.alternateId) as "Total Logins"
| join [ | search index="okta" "target{}.displayName"="Palo Alto Networks - Prisma Access"  "outcome.result"=SUCCESS displayMessage="User single sign on to app"
| stats dc(actor.alternateId) as "Total Palo Logins"]
| table  "Total Palo Logins" "Total Logins"

Only issue is I can't get a proper pie graph of the percentage of Palo Logins vs Total Logins. Any help would be appreciated. I am sure I am missing something simple here. 

[caiosalonso]

Hi,

You can try using the transpose command to return your rows as columns, so you can get a pie chart from your query.

index="okta" "outcome.result"=SUCCESS displayMessage="User single sign on to app"
| stats dc(actor.alternateId) as "Total Logins"
| join [ | search index="okta" "target{}.displayName"="Palo Alto Networks - Prisma Access"  "outcome.result"=SUCCESS displayMessage="User single sign on to app"
| stats dc(actor.alternateId) as "Total Palo Logins"]
| table  "Total Palo Logins" "Total Logins"
| transpose

[daveywfii]

So this gets me a pie graph of Total Logins and Total Palo Logins, but I wanted a pie graph of the total palo logins as percentage of the total and not as two separate percentages in the graph. 

[caiosalonso · Accepted solution]

Undesrtood. Now I tried to eval two new fields that would be the percentage of Palo Alto Logins and Total Logins, and them I used the transpose to return rows as columns. Is this the results you expect?

index="okta" "outcome.result"=SUCCESS displayMessage="User single sign on to app"
| stats dc(actor.alternateId) as "Total Logins"
| join [ | search index="okta" "target{}.displayName"="Palo Alto Networks - Prisma Access"  "outcome.result"=SUCCESS displayMessage="User single sign on to app"
| stats dc(actor.alternateId) as "Total Palo Logins"]
| table  "Total Palo Logins", "Total Logins"
| eval "Total Palo Logins Percentage" = round("Total Palo Logins"*100/"Total Logins", 2)
| eval "Total Logins Percentage" = 100 - round("Total Palo Logins"*100/"Total Logins", 2)
| table "Total Palo Logins Percentage", "Total Logins Percentage"
| transpose

Don't know if this was the best way to calculate the percentage, but It seems to be working.

[daveywfii]

Hey thanks for the suggestion. It worked as long as I used single quotes inside the (), but I ended up going a "simpler" way to display the difference between them.

index="okta" "outcome.result"=SUCCESS displayMessage="User single sign on to app"
| stats dc(actor.alternateId) as "Total Logins"
| join [ | search index="okta" "target{}.displayName"="Palo Alto Networks - Prisma Access" "outcome.result"=SUCCESS displayMessage="User single sign on to app"
| stats dc(actor.alternateId) as "Users Logging into Prisma"]
| table "Users Logging into Prisma", "Total Logins"
| eval "Users Not Using Prisma"=('Total Logins' - 'Users Logging into Prisma')
| table "Users Logging into Prisma", "Users Not Using Prisma"
| transpose

As a side question, how are you doing the markup to designate SPL? 

[caiosalonso]

Good it helped you.

I use the option to insert code on the formatting tab, as HTML/XML. It doesn't have an option for SPL, but It makes the code more clear to read.