Monthly AV compliance report expert query

[mohammadsharukh]

Dear All,

Scenario--> 1AV server is having multiple endpoint reporting to it. This AV server integrated with Splunk and through the AV server we are reciving DAT version info. for all the reporting endpoints.

Requirement--> Need to generate a AV monthly DAT compliance report.

 

The criteria for DAT compliance is 7 days. within 7 days system should be updated to latest DAT.

Workdone till now--> THere is no intelligenec in data to get the latest DAT from AV-Splunk logs. Only endpoint that are updated with N DAT is coming. I used EVAL command and tied the Latest/today DAT to the today DATE (Used today_date--convert-->today_DAT). Based on that I am able to calculate the DAT compliance for 7 days keeping the today_DAT for the 8th day as reference. This splunk query is able to give correct data for whatever time frame with  the past 7 days compliance only.

Issue--> for past 30 days i.e 25th to 25th of every month, I wanted to divide the logs with 7 days time frame starting from e.g 25th dec, 1 jan,  8th jan 15th jan 22jan  till 25th Jan (last slot less than 7days) and then calculate for each 7 day time frame to know what is the overall compliance on 25th jan. Accordingly calculate the overall 25th dec, 1 jan,  8th jan till 25th Jan  data for a month to give the final report

Where stuck--> current query i tried to add the "bin" command for 7 days but unable to tie the latest DAT date (today_DAT date for the 1st Jan) to 7th day for first bin then 8th Jan for second bin so on and so forth

In case there is any other method/query to do the same stuff. Kindly let me know

PFA screenshot for your reference

@PickleRick

@ITWhisperer 

@yuanliu 

[ITWhisperer]

Screenshots aren't particularly helpful, it is better to paste your search into a code block </>.

Also, providing sample (anonymised) events or representative dummy events, again in a code block, also helps.

Having said that, does something like this help:

| bin span=7d _time aligntime=earliest
| stats max(version) as latestversion by ComputerName, _time
| rex field=latestversion "(?<latestversionT>\d{6})"
| eval today_date=strftime(now(),"%d-%m-%y") ``` No longer required? ```
| eval today_DAT=strftime(now(),"%y%m%d") ``` No longer required? ```
| eval diff = floor((relative_time(now(),"@d") - strptime(latestversionT, "%y%m%d"))/86400)
| eval status = if(diff<=7,"Compliant","Non-Compliant")

I generated some dummy sample data like this:

| gentimes start=-30 increment=1h
| rename starttime as _time 
| fields _time
| eval ComputerName=mvindex(split("ABCDEFGHIJ",""),random()%10)
| eval version=strftime(relative_time(_time,"-".(random()%5+1)."d"),"%y%m%d").printf("%03d",(random()%100))