Query Exchange

Description: This query leverages the new feature in Audit and Remediation to be able to query the Windows event log. The query specifically displays the latest CB Standard (formally CB Defense) background scan status. The background scan status event is sent to the Windows event viewer every time the system reboots. 

What The Data Shows: Shows the latest background scan status (in progress, complete, disabled) as well as the date and time that the scan event was registered.

SQL:

SELECT

  CASE

    WHEN data like "%IN_PROGRESS%" then "IN PROGRESS"

    WHEN  data like "%COMPLETE%" then "COMPLETE"

    WHEN  data like "%DISABLED%" then "DISABLED"

  END "Background Scan Status"

, MAX(DATETIME(datetime)) AS "Scan Status Update Date and Time"

FROM

 windows_eventlog where channel = 'Application' and eventid = '17' and data like '%BACKGROUND_SCAN%';

Works great for windows endpoint, any plans to support Linux Sensors? 

That would require a new table with the ability to parse the Linux Carbon Black file. I suggest reaching out to your account team and have them submit a feature request.  

Are Windows event log timestamps also capable of being queried?

Is there a way to convert the system times to UTC/unixepoch times?

 , check out https://community.carbonblack.com/t5/Query-Exchange/Programs-Installed-In-Non-Standard-Windows-Locations/idi-p/86760 for an example of converting unixepoch.

There are 2 timestamps that you can query against for the Windows event logs. Check out https://osquery.io/schema/4.6.0/#windows_eventlog

 Change this part:

MAX(DATETIME(datetime)) 

To this:

MAX((julianday(datetime) - 2440587.5)*86400.0)

 wrote:

@CtiMT1 Change this part:

 

MAX(DATETIME(datetime))

 

To this:

 

MAX((julianday(datetime) - 2440587.5)*86400.0)

---

Thanks for this, this really helped me out! I also went ahead and did some additional reading and testing. 

Turns out for our instance "datetime" returned event log data in UTC already!

This what I put in to test if the datetime was already doing UTC:

select datetime, datetime(datetime,'localtime') as "EventTime(System's Timezone)", datetime(datetime,'utc') as "EventTime(UTC)", (julianday(datetime) - 2440587.5)*86400.0 as "EventTime(JulianDay)"

Anything a Mac user can do to check Background Scan Status on all Mac Devices via A&R? :) 

 That would require a new table with the ability to parse the Mac Carbon Black log file. Submitting a feature request is the best recourse.  

Hi Team,

why is this query not part of the "recommended" section within Carbon Black Cloud - Audit & Remediation? 

Thanks,
Leon