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
@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"
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]
@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"
That worked perfectly...Thank you much!!!
one more query..in case i want to pass latest month always in my token then how would i paas that?
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")
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?