topic Re: How do you calculate the percentage based upon the selection made in filter? in Dashboards & Visualizations

How do you calculate the percentage based upon the selection made in filter?

sbhatnagar88 — Wed, 30 Jan 2019 12:57:27 GMT

We have a dashboard panel which shows overall AV compliance % for windows servers.code is as below.

index=dbconnect sourcetype=dbconnect:sql:SCCM_AVCompliance_AllServers 
| table Name DC OU ResourceID SignatureUpTo1DayOld AntivirusSignatureAge AntivirusSignatureUpdateDateTime AntivirusSignatureVersion 
| rename Name as host 
| join host 
    [| inputlookup elixpediadashboardservers.csv 
    | search (host="*") Environment="*" 
    | search "Operating System"=WINDOWS] 
| append 
    [| inputlookup elixpediadashboardservers.csv 
    | search (host="*") Environment="*" 
    | search "Operating System"=WINDOWS] 
| dedup host  
| fillnull AntivirusSignatureAge Value=2 
| eval Compliance=if(AntivirusSignatureAge==0 OR AntivirusSignatureAge==1 ,"COMPLIANT","NONCOMPLIANT") 
| stats count(eval(Compliance=="COMPLIANT")) as compliant, count(eval(Compliance=="NONCOMPLIANT")) as noncompliant, count as total 
| eval AVUpdateCompliance=round((compliant/total)*100,2) 
| table AVUpdateCompliance

Now customer requirement is to add a filter on top of this panel, which shows last 4 months like
January 2019
December 2018
November 2018
October 2018

and this filter has already been created.

My query is...

How do i pass a month as a token in my query so that, if a user selects November 2018 from dropdown, then the panel should show AV % compliance only for the month of November.

Any help would be highly appreciable.

Thanks

Re: How do you calculate the percentage based upon the selection made in filter?

renjith_nair — Wed, 30 Jan 2019 13:39:25 GMT

@sbhatnagar88,

If the compliance information is indexed using the actual time of the event, add the token to base search

index=dbconnect sourcetype=dbconnect:sql:SCCM_AVCompliance_AllServers |where strftime(_time,"%B %Y")="$your_token_value$" 
|rest of your search"

Re: How do you calculate the percentage based upon the selection made in filter?

vishaltaneja070 — Tue, 29 Sep 2020 23:01:04 GMT

Re: How do you calculate the percentage based upon the selection made in filter?

sbhatnagar88 — Fri, 01 Feb 2019 06:07:20 GMT

That worked perfectly...Thank you much!!!

Re: How do you calculate the percentage based upon the selection made in filter?

sbhatnagar88 — Fri, 01 Feb 2019 09:47:10 GMT

one more query..in case i want to pass latest month always in my token then how would i paas that?

Re: How do you calculate the percentage based upon the selection made in filter?

renjith_nair — Fri, 01 Feb 2019 12:14:02 GMT

if you are looking for current month try strftime(now(),"%B %Y")
If you are always using latest month, you dont need a token , instead you can use it directly to the search

where strftime(_time,"%B %Y")=strftime(now(),"%B %Y")

Re: How do you calculate the percentage based upon the selection made in filter?

sbhatnagar88 — Fri, 01 Feb 2019 12:22:00 GMT

hi Renjith,

yes.Thanks.i got this one already but putting time filter on the index has made my searches/results very slow.Any idea about this?