Dashboards & Visualizations

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

Path Finder

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

0 Karma
1 Solution

SplunkTrust
SplunkTrust

@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"

View solution in original post

0 Karma

Motivator

Try with this: index=dbconnect sourcetype=dbconnect:sql:SCCM_AVCompliance_AllServers [| makeresults |eval time=$token|s$ | eval time="01 ".time| eval time2= strptime(time, "%d %B %Y") | eval time3 = relative_time(time2, "+1mon") | eval time3 = relative_time(time3, "-1d") | eval earliest=time2 | eval latest=time3 | return earliest, latest]

0 Karma

SplunkTrust
SplunkTrust

@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"

View solution in original post

0 Karma

Path Finder

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

0 Karma

Path Finder

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

0 Karma

SplunkTrust
SplunkTrust

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")

0 Karma

Path Finder

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?

0 Karma