Sample Queries

The following examples show some types of queries you can make with the Live Inventory SDK. Each query must use the das database.

Listing Malicious Files

If you have Carbon Black File Reputation enabled, you can use the following query to get a listing of the file names and prevalence of all malicious files determined to be on your systems that run the Carbon Black App Control Agent:

USE das 
                           SELECT First_Seen_Path, First_Seen_Name, Sha256, Threat,
                           Trust, Prevalence 
                           FROM bit9_public.ExFileCatalog
                           WHERE Threat IN ('2 - Malicious', '1 - Potential risk') ORDER BY First_Seen_Path,
                           First_Seen_Name
                        

If you run this query and there is data available, the output will be similar to the following:

First_Seen_Path	First_Seen_Name	Sha256*	Threat	Trust	Prev.
c:\temp\folder1	myfileapp.exe	46b8...	1 - Potential risk	2	1
c:\documents and settings\user1	numbergen.exe	552e...	1 - Potential risk	1	1
c:\documents and settings\user2	makemess.exe	4d9a...	1 - Potential risk	3	1
c:\hp\bin	endprocess.exe	1effc...	1 - Potential risk	3	13
c:\program files\mywebapp\	f4dothis.dll	abcd...	2 - Malicious	0	1
c:\jobfiles	myway.exe	2345...	2 - Malicious	0	1

Note: The *:Sha56 field will be fully expressed. It is truncated in this table due to formatting restictions.

Listing App Control Agent Systems by Policy and Enforcement Level

You can use the following query to determine how many systems are running the agent and group the results by Policy and Enforcement Level:

USE das 
                           SELECT Policy, Enforcement_Level, Disconnected_Level, COUNT(*) 
                           AS Computer_Count 
                           FROM bit9_public.ExComputers 
                           GROUP BY Policy, Enforcement_Level, Disconnected_Level ORDER BY Policy
                        

If you run this query and there is data available, the output will be similar to the following:

Policy	Connected_Enforcement_Level	Disconnected_Enforcement_Level	Count
Agent Disabled	None (Disabled)	None (Disabled)	3
Research Team	Medium (Prompt Unapproved)	Medium (Prompt Unapproved)	6
Default Policy	None (Visibility)	None (Visibility)	1
General Office	High (Block Unapproved)	High (Block Unapproved)	49
Guest Policy	High (Block Unapproved)	High (Block Unapproved)	1
IT Group	Low (Monitor Unapproved)	Low (Monitor Unapproved)	11

 

Listing New Unapproved Files by Policy

You can use the following query to determine how many new unapproved files have appeared during the past 24 hours, and group the results by Policy:

USE das 
                           SELECT Policy, COUNT(*) FROM bit9_public.ExFileInstances fi
                           JOIN bit9_public.ExComputers c 
                           ON c.Computer_Id = fi.Computer_Id
                           WHERE fi.Date_Created>DATEADD(day, -1, GetUTCDate()) AND 
                           Local_State = 'Unapproved'
                           GROUP BY Policy
                           ORDER BY COUNT(*) DESC
                        

If you run this query and there is data available, the output will be similar to the following:

Policy	New Unapproved File Count
Research Team	529
General Office	101
IT Group	257

Listing New Unapproved Files by Computer and Policy

You can use the following query to determine how many new unapproved files have appeared during the past 24 hours, and group the results by Computer and Policy:

USE das
                           SELECT c.Computer, c.Policy, COUNT(*) as Unapproved_Count FROM bit9_public.ExFileInstances
                           fi
                           JOIN bit9_public.ExComputers c 
                           ON c.Computer_Id = fi.Computer_Id
                           WHERE fi.Date_Created>DATEADD(day, -1, GetUTCDate()) AND Local_State = 'Unapproved'
                           GROUP BY c.Computer, c.Policy
                           ORDER BY COUNT(*) DESC
                        

If you run this query and there is data available, the output will be similar to the following:

Computer Name	Policy	New Unapproved File Count
MYCORP\DESKTOP-3	Research Team	307
MYCORP\LAPTOP-1	General Office	215
MYCORP\LAPTOP-4	Research Team	32
MYCORP\DESKTOP-8	IT Group	3
MYCORP\DESKTOP-10	General Office	2
MYCORP\LAPTOP-7	General Office	1