Dashboards & Visualizations

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

sbhatnagar88
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

renjith_nair
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"
Happy Splunking!

View solution in original post

0 Karma

vishaltaneja070
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

renjith_nair
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"
Happy Splunking!
0 Karma

sbhatnagar88
Path Finder

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

0 Karma

sbhatnagar88
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

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

Happy Splunking!
0 Karma

sbhatnagar88
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
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...