Examples of Dr.Web Server Database Queries

Provided below are a few examples of SQL queries to a PostgreSQL database. Queries to other databases may contain some differences due to the features of the database itself and the subtleties of its use.

info

For ease of understanding, the examples do not take the hierarchy of groups and stations into account and use less performance-optimal but easier-to-understand syntax.

To query the database directly

1.Open the Control Center of your Dr.Web Server.

2.Go to the Administration → SQL console section.

3.Write an SQL query. Examples of queries are given below.

4.Click Execute.

Examples of SQL queries

1.Find stations running server versions of Windows OS that are using virus databases issued before 2024.07.16-00:00:00 UTC.

SELECT

 stations.name Station,

 groups_list.name OS,

 station_products.crev Bases

FROM

 stations

INNER JOIN groups_list ON groups_list.platform =(

  CAST(stations.lastos AS INTEGER) & ~15728640

 )

AND (

   (

    CAST(stations.lastos AS INTEGER) & 2130706560

   ) = 33554560

 )

INNER JOIN station_products ON station_products.id = stations.id

AND station_products.product = '10-drwbases'

AND station_products.crev < 13020240716000000;

info

Data on the platforms of stations in the anti-virus network is stored as numeric codes.

To find out the correspondence between the code and the name of the station platform

1.Open the Control Center of your Dr.Web Server.

2.Navigate to the Administration → SQL console section.

3.Run the following command:

SELECT DISTINCT lastos FROM stations

4.Click Execute.

You will see a list of codes corresponding to platforms that have been identified on the stations of this anti-virus network.

5.Save the result of running the query to a file using the buttons on the toolbar or leave the tab open for further use of the obtained data.

6.Open the Administration → Lua console section.

7.Enter a command of the following form:

return { ['<code>'] = dwcore.platform_str(<code>)}

where <code> is one of the numeric codes obtained with the SQL query.

8.Click Execute.

2.Find stations with High and Maximum severity records in the Anti-virus network → Statistics → Status section.

SELECT

 stations.name Station

FROM

 stations

WHERE

 id IN (

  SELECT

    DISTINCT id

  FROM

     station_status

  WHERE

     severity >= 1342177280

 );

info

Data on station status severity is stored as numeric codes:

0—minimum,

268435456—low,

805306368—medium,

1342177280—high,

1879048192—maximum.

You can also check the station status severity codes the following way:

1.Open the Control Center of your Dr.Web Server.

2.Navigate to the Administration → Lua console section.

3.Run the following command:

require 'statusmessages' return statusmessages.severity

4.Click Execute.

3.Get the correspondence between station status messages and the number of stations with each of these statuses.

SELECT

 code Code,

COUNT(code) Num

FROM

 (

  SELECT

    DISTINCT id,

     code

  FROM

     station_status

 ) AS t

GROUP BY

 Code

ORDER BY

 Code;

 

info

Data on station status messages is stored as numeric codes.

To find out the correspondence between the codes and the station status messages

1.Open the Control Center of your Dr.Web Server.

2.Navigate to the Administration → Lua console section.

3.Enter the following code:

local sm = require 'statusmessages'

local messageByCode = require 'stats/message_by_code'

local codes = {}

for _,v in pairs(sm) do

local n = tonumber(v)

if n ~= nil then

   code = sm.get_code( n )

   codes[code] = messageByCode( code )

end

end

return codes

4.Click Execute.

4.Get the top 10 threats detected from 2024.06.01 through 2024.07.01 on stations included in the group with the identifier '373a9afb-9c9a-4d4d-b9b1-de817b96bcc5' or in any nested groups.

SELECT

 cat_virus.str Threat,

COUNT(cat_virus.str) Num

FROM

 station_infection

INNER JOIN cat_virus ON cat_virus.id = station_infection.virus

WHERE

 station_infection.infectiontime BETWEEN 20240601000000000

AND 20240701000000000

AND station_infection.id IN (

  SELECT

     sid

  FROM

     station_groups

  WHERE

     gid = '373a9afb-9c9a-4d4d-b9b1-de817b96bcc5'

    OR gid IN (

      SELECT

         child

      FROM

         group_children

      WHERE

         id = '373a9afb-9c9a-4d4d-b9b1-de817b96bcc5'

     )

 )

GROUP BY

 cat_virus.str

ORDER BY

 Num DESC

LIMIT

10;

5.Get the top 10 infected stations.

SELECT

 Station,

 Grp,

 Num

FROM

 (

  SELECT

     stations.id,

     groups_list.id,

     stations.name Station,

     groups_list.name Grp,

    COUNT(stations.id) Num

  FROM

     station_infection

    INNER JOIN stations ON station_infection.id = stations.id

    INNER JOIN groups_list ON groups_list.id = stations.gid

  GROUP BY

     stations.id,

     groups_list.id,

     stations.name,

     groups_list.name

  ORDER BY

     Num DESC

  LIMIT

    10

 ) AS t;

6.Remove all stations from any user groups that are not primary for those stations.

DELETE FROM

 station_groups;

INSERT INTO station_groups(sid, gid)

SELECT

 stations.id,

 groups_list.id

FROM

 stations

INNER JOIN groups_list ON stations.gid = groups_list.id

AND groups_list.type NOT IN(1, 4);

7.Find objects on the anti-virus network that have the specified domain whitelisted in the personal settings of the SpIDer Gate component.

SELECT

 stations.name Station

FROM

 station_cfg

INNER JOIN stations ON stations.id = station_cfg.id

WHERE

 station_cfg.component = 38

AND station_cfg.name = 'WhiteVirUrlList'

AND station_cfg.value = 'domain.tld';

SELECT

 groups_list.name Grp

FROM

 group_cfg

INNER JOIN groups_list ON groups_list.id = group_cfg.id

WHERE

 group_cfg.component = 38

AND group_cfg.name = 'WhiteVirUrlList'

AND group_cfg.value = 'domain.tld';

SELECT

 policy_list.name Policy

FROM

 policy_cfg

INNER JOIN policy_list ON policy_list.id = policy_cfg.id

WHERE

 policy_cfg.component = 38

AND policy_cfg.name = 'WhiteVirUrlList'

AND policy_cfg.value = 'domain.tld';

8.Get the events of unsuccessful administrator logins to the Control Center with the corresponding authorization error codes from the audit log.

SELECT

 admin_activity.login Login,

 admin_activity.address Address,

 activity_data.value ErrorCode,

 admin_activity.createtime EventTimestamp

FROM

 admin_activity

INNER JOIN activity_data ON admin_activity.record = activity_data.record

WHERE

 admin_activity.oper = 10100

AND admin_activity.status != 1

AND activity_data.item = 'Error';

9.Find stations running Windows OS that do not have the required security patches installed.

Option 1:

SELECT

 stations.name Station

FROM

 stations

WHERE

 id NOT IN (

  SELECT

     station_env_kb.id

  FROM

     station_env_kb

    INNER JOIN stations ON stations.id = station_env_kb.id

  WHERE

     (

      CAST(stations.lastos AS INTEGER) & 2130706432

     )= 33554432

    AND station_env_kb.name IN (

      SELECT

         id

      FROM

         env_strings

      WHERE

         str IN(

          'KB4012212', 'KB4012213', 'KB4012214',

          'KB4012215', 'KB4012216', 'KB4012217',

          'KB4012598'

         )

     )

 );

info

Data on the platforms of stations in the anti-virus network is stored as numeric codes.

Option 2, where '24e27d73-d21d-b211-a78c-85419c46f0e6' is the UUID of the Windows system group:

SELECT

 stations.name Station

FROM

 stations

WHERE

 id NOT IN (

  SELECT

     station_env_kb.id

  FROM

     station_env_kb

    INNER JOIN stations ON stations.id = station_env_kb.id

  WHERE

     stations.osgroup IN

     (

      SELECT

         child

      FROM

         group_children

      WHERE

         id='24e27d73-d21d-b211-a78c-85419c46f0e6'

      UNION ALL

      SELECT

        '24e27d73-d21d-b211-a78c-85419c46f0e6'

     )

    AND station_env_kb.name IN (

      SELECT

         id

      FROM

         env_strings

      WHERE

         str IN(

          'KB4012212', 'KB4012213', 'KB4012214',

          'KB4012215', 'KB4012216', 'KB4012217',

          'KB4012598'

         )

     )

 );