Перейти к содержанию
Shell

Строим отчеты по доступу к файлам

Recommended Posts

Shell

Навеяно темой http://www.symantec.com/connect/downloads/...t-report-e-mail

Где то на форуме была политика для ведения лога по записанным и прочитанным файлам на съемных накопителях.

в этой теме покажу как получать отчеты о доступе к файлам на съемных устройствах с помощью простых манипуляций.

Будет рассмотрена связка SQL 2008 + SEPM 11.0.6005

Что нам понадобится. Во первых, настроить Database Mail согласно рекомендаций.

http://marslert.com/blog/2009/03/16/config...-database-mail/

С помощью запроса SQL Query получаем параметры которые нам будут нужны:

SELECT [profile_id]      ,[name]       ,[description]      ,[last_mod_datetime]      ,[last_mod_user]   ,'EXEC [email protected]_name = ''' + name + ''',@recipients = ''ваш_емайл@company.com'', @subject = ''Test'',@body  = ''Message'',  @body_format = ''HTML'';' AS TestSQL  FROM [msdb].[dbo].[sysmail_profile]

Получаем строку типа

EXEC msdb.dbo.sp_send_dbmail  @profile_name = 'Database Mail',  @recipients = 'ваш_емайл@company.com',   @subject = 'Test',  @body  = 'Message',    @body_format = 'HTML';

В SEPM на необходимую группу вешаем политику. Импорт из атача Log documents from-to removable drives.dat

В этой политике уже готово правило которое будет записывать в Client Management logs-->Control Log все операции с файлами с расширениями *.doc, *.docx, *.xls, *.xlsx,*.pst,*.xml,*.txt,*.xlt,*.pdf,*.csv,*.xml,*.mht,*.mhtml,*.rar,*.zip,*.7z,

*.0*

36fd53d7ebd2t.jpg

8c38f4f77194t.jpg

При это в логе на клиенте SEPM будет отображаться информация в формате ниже на скрине с указанием названия правила которое фигурирует выше в политике (важно!).

01a44d56d321t.jpg

Настраиваем в SEPM сроки хранения журнала на клиенте и его размер (в SEPM выбираем Clients-->группу-->в политиках Client log settings).

Нужные нам параметры - в control log. Обязательно ставим галку Upload to management server.

ee9f73c02dc8t.jpg

Устанавливаем в параметрах сервера SEPM настройки хранения логов.

Все логи с клиентов будут собираться в нашу базу в таблицы:

[Endpoint].[Endpoint].[AGENT_BEHAVIOR_LOG_1]

[Endpoint].[Endpoint].[AGENT_BEHAVIOR_LOG_2]

Endpoint - название БД. Что в них собирается - вы можете посмотреть сами и выбрать необходимые поля :)

По сути поля почти теже что и в логе на клиенте.

Обращение к этим двум таблицам сразу возможно через уже встроенную вьюшку [Endpoint].[Endpoint].[V_AGENT_BEHAVIOR_LOG] содержащую простейшее обращение к этим таблицам:

SELECT     *FROM         AGENT_BEHAVIOR_LOG_1UNION ALLSELECT     *FROM         AGENT_BEHAVIOR_LOG_2

Строим запрос к БД с нужными полями, преобразуем формат времени из bigint в удобоваримый.

Возьмем следующие поля:

FILE_NAME - имя файла с которым производятся операции

VAPI_NAME - операция Read\Write (к сожалению, другие операции не будут видны)

CALLER_PROCESS_NAME -- имя процесса которым читается\записывается файл

HOST_NAME,USER_NAME,DOMAIN_NAME - имя хоста (будет без домена), имя пользователя (будет без домена), имя конечного домена без сабдоменов.

EVENT_TIME - собственно время. Время в БД юниксовое, так что приводим его в удобоваримый вид.

select PARAMETER AS FILE_NAME,VAPI_NAME,CALLER_PROCESS_NAME,HOST_NAME,USER_NAME,DOMAIN_NAME, EVENT_TIME = DATEADD(SECOND, EVENT_TIME / 1000, '19700101 00:00'), RULE_NAMEfrom [Endpoint].[Endpoint].[V_AGENT_BEHAVIOR_LOG]where [rule_name] like '%Log documents from-to removable drives%'order by [EVENT_TIME];

Обращаю внимание на строку '%Log documents from-to removable drives%' - берем совпадения по названию правила. В БД оно несколько изменено на конце, поэтому построен запрос именно так.

Если правильно все сделали и журналы уже собраны в SEPM - в результате запроса увидите результат о доступе к файлам на устройстве.

Собрав данные в БД вы простым запросом за интересующий вас интервал по любым полям (пользователю\компу\домену\времени) можете сделать выборку.

Переходим к украшательствам.

Нужны автоматические письма? Делаем автоматический запуск SQL Agent (без него не работают джобы) и вешаем задание.

В задание вешаем скрипт:

DECLARE @xml NVARCHAR(MAX)DECLARE @body NVARCHAR(MAX)SET @xml = CAST(( select PARAMETER AS 'td',' ' FILE_NAME,VAPI_NAME AS 'td',' ',CALLER_PROCESS_NAME AS 'td',' ',HOST_NAME AS 'td',' ',USER_NAME AS 'td',' ',DOMAIN_NAME AS 'td',' ',DATEADD(SECOND, EVENT_TIME / 1000, '19700101 00:00') AS 'td',' 'from [Endpoint].[Endpoint].[V_AGENT_BEHAVIOR_LOG]where ([rule_name]like '%Log documents from-to removable drives%') and(DATEADD(SECOND, EVENT_TIME / 1000, '19700101 00:00') between DATEADD (HOUR, -24, getdate()) and GETDATE())order by [EVENT_TIME] FOR XML PATH ('tr'), ELEMENTS ) AS NVARCHAR(MAX))SET @body ='<html><H3><center><FONT COLOR="#7E2217">SEPM - Отчет за последние 24 часа по записи\чтению на съемные накопители USB</FONT></H3></center><body><table border = 1><tr><FONT COLOR="#736AFF"><th>Имя файла</th><th>Операция</th><th>Процесс</th><th>Имя ПК</th><th>Имя пользователя</th><th>Имя домена</th><th>Время (Мск)</th></tr>'SET @body = @body + '<style>' + 'td {font-size:8pt;} '+ '</style>'+ @xml +'</table></FONT></body></html>'EXEC msdb.dbo.sp_send_dbmail/*msdb.dbo.sp_send_dbmail*//*EXEC [msdb].[dbo].[sysmail_profile]*/@recipients=N'ваш_емйл@company.com',@body = @body,@body_format = 'HTML',@subject ='SEPM Application Control - USB Read\Write Attempts',@profile_name = 'Database Mail'

@profile_name = 'Database Mail' - здесь прописать то что получилось в результате запроса выше (EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Database Mail', @recipients = 'ваш_емайл@company.com', @subject = 'Test', @body = 'Message', @body_format = 'HTML';)

Скрипт автоматом будет получать время запуска и строить отчет за последние 24 часа за счет

(DATEADD(SECOND, EVENT_TIME / 1000, '19700101 00:00') between DATEADD (HOUR, -24, getdate()) and GETDATE())

Если нужен другой срок - думаю, вы в состоянии будете изменить параметры.

На свой указанный в скрипте емайл будете получать отчеты.

Если есть какие то добавления\желания - все можно сделать на основе этой реализации.

Удачи :)

Log_documents_from_to_removable_drives.rar

Log_documents_from_to_removable_drives.rar

  • Upvote 10

Поделиться сообщением


Ссылка на сообщение
Поделиться на другие сайты
Shell

Опять кнопки редактировать нет)

Добавлю одно замечание:

Если время желаете по МСК в отчетах - выставьте

(DATEADD(SECOND, EVENT_TIME / 1000, '19700101 04:00')

Поделиться сообщением


Ссылка на сообщение
Поделиться на другие сайты
angel-keeper

Думаю стоит добавить в тему "Шаблоны политик"

Поделиться сообщением


Ссылка на сообщение
Поделиться на другие сайты

  • Сообщения

    • Gregoryjed
      Online casino slots are a popular pastime for many people - but with so much to choose from and learn, it can be tricky to find the best ones! Luckily, this article has all of the answers you need to find your next favorite game. With the recent release of a new craze known as "auctioning," many resorts have begun to create games that incorporate this concept. The game's rules can be explained in just the amount of time it takes to play one round. However, there are some who would argue that these games can actually make your gambling habits worse - since you don't have to learn anything, you're at a disadvantage because you're playing "blind."

      How to Win At Slot Machines

      You're not going to win at slots, but the more you play, the better your chances. One great way to win at slot machines is to play with a bonus code. One bonus code will usually give the player 10 free spins up to a certain amount or 20% extra payout after each spin. Slot machines are booming with popularity. Why? Slots have an instant jackpot, and there's always a chance for someone to win BIG! There are some simple steps to follow if you want to be successful at slots. There are many different reasons why people enjoy playing online slots, but there are some basic principles behind winning at slot machines. Here are the most important tips that will help you win at online slots:

      What are the Basic Ways to Play casino slots?

      There are a lot of different games to play at an online casino. One you might want to try is the slots, which is where the most money is won in slot machines across the world. Online casino slots vary depending on where you are playing and what games they have available. Some casinos specialize in just one game while others have many different options to choose from. There are a variety of ways that you can play casino slots. Some of these include the "Wheel of Fortune" and "Lucky Ladys Charm." Playing these games is probably one of the easiest ways to understand how the game works. If you are more interested in playing games with higher odds, then try playing video poker or blackjack.

      Which online casinos Offer Welcome Bonuses?

      Casinos that offer welcome bonuses to new players typically also have a number of other sign-up incentives, including free spins, cash back, and match deposits. These promotions can help to entice players away from their competitors and give them the best possible chance at winning big. There are various online casinos that offer welcome bonuses. These include 100% bonus, free chips, no deposit bonus, and match bonus. However, bonuses vary from casino to casino. It is important to choose a casino because of their terms and conditions. online casinos offer welcome bonuses to encourage new players to join. These welcome bonuses typically consist of a percentage of the first deposit that is given back to the player, usually in the form of free slots and/or free cash. Some online casino welcome bonuses also include an additional bonus on top of that, such as a coupon code or match bonus.

      Best Casino Sites in the UK

      The biggest mistake that players make is not doing sufficient research on the best casino sites in the UK. It's important to find a reliable and affordable site, such as Casino Rama, so that you can enjoy your time playing at one. Online casino slots are a great way to try and win big while doing something you enjoy. There are plenty of UK casinos that offer slots so it can be a daunting task trying to find the right one. The following websites offer the best casinos with the best games, bonuses and more: So you want to win at online casino slots? Rather than getting in over your head, it's best to start small - with the UK's best casinos. These sites have a great selection of games and are some of the most trusted around.

      Best online casinos in the UK

      As online casinos have become more popular, so have the rules and guidelines surrounding their game selection. One thing you should know is that the best online casino games are those that can produce a lot of revenue for your business. The UK has a handful of the top online casinos that offer slots, roulette, and other games. These include jackpotbetonline.com, jackpotbetgame.com, jackpotcasinotips.com and more to come soon as they continue to grow in popularity as an alternative to brick-and-mortar casinos. UK online casinos offer players a variety of different games with huge jackpots to be won. Different online casinos offer different types of payouts, such as the amount you will get from playing slots, or how much your deposit will pay out. It's important to know what each casino offers in order to find one that better suits your needs.

      Why Is casino slots so Addictive?

      casino slots have the potential to be highly addictive. This is because of the random nature of slot games and how the outcome of each round is different. The specific combination of symbols in each game can make a player feel as if they are always winning, which leads to them considering it a fun activity. They'll also spend more money on slot machines in order to keep this feeling going, which can lead to gambling problems and spending too much money. Many people love the thrill of playing slots. Some people enjoy slot machines because they are easy to understand and can feel in control. There are a few ways to determine why slots are so addictive and some suggestions on how to avoid it.

      Conclusion

      In conclusion, it is important to realize that playing slots online is not the same as playing slots in a land-based casino. One of the most important rules to remember when playing at a land-based casino is to never play for more than what you can afford, but online slots are not always so easy.
    • Joshua3233Chids
      This is my first time come here
      https://gto5767.com/home.php?mod=space&uid=1902793
      http://www.atlasroleplay.com/forum/profile/koreanneck14
      https://spinalhub.win/wiki/H124pc_Wholesale_Clear_Acrylic_Crystal_Stud_Earring_Field_Show_Jewelry_Reward_Binsh1
      http://quanboo.com/home.php?mod=space&uid=53013
      http://idea.informer.com/users/cobwebplier5/?what=personal

      I am very happy to cme here I am very happy to cme here I am very happy to cme here I am very happy to cme here I am very happy to cme here 8287d12
    • Ego Dekker
      Домашние антивирусы ESET были обновлены до версии 15.0.23.
    • Thomaspaymn
      Hello there, I'm completely new here, I am not sure in the event this section is a right place to create this and also sorry with this, but I was hoping a person here on anti-malware.ru would be able to assist me.
      I am just wondering anybody knows just about any trusted company for signals for crypto. Is this website good and anyone worked with them ?
      binance futures signals
      Also please introduce any good and comprehensive blog for more inormation about this kind of services. We appreciate it.
    • Gabrielmop
      Какое абстрактное мышление
      filmkont.online
×