Daily reports on the status of virtual machines using R and PowerShell

Introduction

Good afternoon. Already half a year we have a script (or rather a set of scripts) that generates reports on the state of virtual machines (and not only). I decided to share the experience of creation and the code itself. I count on criticism and on the fact that this material may be useful to someone.

Formation needs

We have a lot of virtual machines (about 1500 VM distributed across the 3rd vCenter). New ones are created and old ones are deleted quite often. To preserve the order, several custom fields were added to vCenter, in order to divide VMs into Subsystems, indicate whether they were test cases, and who created them and when. The human factor led to the fact that more than half of the cars were left with empty fields, which complicated the work. Once in half a year, someone was hysterical, started working on updating these data, but the result was no longer relevant for a week and a half.
At once I will clarify that everyone understands that there must be applications for the creation of machines, a process for their creation, etc. etc. And at the same time, all this process is strictly followed and in all order. Unfortunately, this is not the case here, but this is not the subject of the article :)

In general, it was decided to automate the validation of the fields.
We decided that a daily letter with a list of incorrectly filled cars to all responsible engineers and their superiors would be a good start.

At that moment, one of the colleagues had already implemented a script on PowerShell, which every day according to the schedule collected information on all the machines of all vCenter-s and formed 3 csv documents (each in its own vCenter), which were laid out on a shared disk. It was decided to take this script as a basis and supplement it with tests using the R language, which had some experience in working with it.

In the process of finalizing the decision was overwhelmed with information by mail, a database with the main and historical tables (more on that later), as well as analysis of vSphere logs to search for the actual creators of vm and the time they were created.

IDE RStudio Desktop and PowerShell ISE were used for development.

The script runs from a regular Windows virtual machine.

Description of general logic.

The overall logic of the scripts is as follows.

We collect data on virtual machines using PowerShell script, which we call through R, the result is combined into one csv. The reverse interaction between languages is done similarly. (it was possible to drive data directly from R to PowerShell as variables, but this is difficult, and having intermediate csv makes it easier to debug and share intermediate results with someone).
With the help of R, we form valid parameters for the fields, the values of which we check. - We form a word document, which will contain the values of these fields for insertion into the information letter, which will be the answer to the questions of colleagues "Not well, but how should I fill it out?".
We load data on all VMs from csv with the help of R, form the dataframe, remove unnecessary fields, and form an information xlsx document that will contain summary information on all VMs that we spread onto a shared resource.
For the dataframe for all VMs, we apply all validation checks for the fields and form a table containing only VMs with incorrectly filled fields (and only these fields).
The resulting VM list is sent to another PowerShell script that will look at the vCenter logs for VM creation events, which will allow you to specify the estimated creation time of the VM and the intended creator. This is the case when no one confesses whose car. This script does not work quickly, especially if there are a lot of logs, so we only look at the last 2 weeks, and also use workflow, which allows you to search for information on several VMs simultaneously. In the example script, there are detailed comments on this mechanism. The result is added to csv, which is again loaded into R.
We form a beautifully formatted xlsx document, in which incorrectly filled out fields will be highlighted in red, filters are applied to some columns, and additional columns are indicated containing the intended creators and the time when the VM was created.
We form an e-mail where we put a document describing the valid values of the fields, as well as a table with incorrectly filled WM. In the text, we indicate the total number of incorrectly created VMs, a link to a shared resource and a motivational picture. If there are no incorrectly filled VMs, send another letter with a more joyful motivational picture.
We write data on all VMs in a SQL Server database, taking into account the implemented mechanism of historical tables (a very interesting mechanism - about which more on that later)

Actually scripts

The main file with the code on the R

#     (       ) setwd("C:\\Scripts\\getVm") ####    #### library(tidyverse) library(xlsx) library(mailR) library(rmarkdown) #####         ##### source(file = "const.R", local = T, encoding = "utf-8") #        ,  . if (file.exists(filenameVmCreationRules)) {file.remove(filenameVmCreationRules)} ####       render("VM_name_rules.Rmd", output_format = word_document(), output_file = filenameVmCreationRules) #        ,   if (file.exists(allVmXlsxPath)) {file.remove(allVmXlsxPath)} ####       PowerShell .    csv. system(paste0("powershell -File ", getVmPsPath)) #  df fullXslx_df <- allVmXlsxPath %>% read.csv2(stringsAsFactors = FALSE) #     full_df <- fullXslx_df %>% mutate( #       ,    ,      , isSubsystemCorrect = Subsystem %>% gsub("[[:space:]]", "", .) %>% str_split(., ",") %>% map(function(x) (all(x %in% AllowedValues$Subsystem))) %>% as.logical(), isOwnerCorrect = Owner %in% AllowedValues$Owner, isCategoryCorrect = Category %in% AllowedValues$Category, isCreatorCorrect = (!is.na(Creator) & Creator != ''), isCreation.DateCorrect = map(Creation.Date, IsDate) ) #        ,  . if (file.exists(filenameAll)) {file.remove(filenameAll)} ####  xslx    #### #      full_df %>% write.xlsx(file=filenameAll, sheetName=names[1], col.names=TRUE, row.names=FALSE, append=FALSE) ####  xslx      #### #  df incorrect_df <- full_df %>% select(VM.Name, IP.s, Owner, Subsystem, Creator, Category, Creation.Date, isOwnerCorrect, isSubsystemCorrect, isCategoryCorrect, isCreatorCorrect, vCenter.Name) %>% filter(isSubsystemCorrect == F | isOwnerCorrect == F | isCategoryCorrect == F | isCreatorCorrect == F) #        ,  . if (file.exists(filenameIncVM)) {file.remove(filenameIncVM)} #   VM     csv incorrect_df %>% select(VM.Name) %>% write_csv2(path = filenameIncVM, append = FALSE) #      incorrect_df_filtered <- incorrect_df %>% select(VM.Name, IP.s, Owner, Subsystem, Category, Creator, vCenter.Name, Creation.Date ) #    numberOfRows <- nrow(incorrect_df) ####   #### #        ,  . #   -     if (numberOfRows > 0) { #       ,  . if (file.exists(creatorsFilePath)) {file.remove(creatorsFilePath)} #  PowerShell ,     VM.    csv. system(paste0("powershell -File ", getCreatorsPath)) #     creators_df <- creatorsFilePath %>% read.csv2(stringsAsFactors = FALSE) #     ,       incorrect_df_filtered <- incorrect_df_filtered %>% select(VM.Name, IP.s, Owner, Subsystem, Category, Creator, vCenter.Name, Creation.Date ) %>% left_join(creators_df, by = "VM.Name") %>% rename(` ` = CreatedBy, `  ` = CreatedOn) #    emailBody <- paste0( '<html> <h3> ,  .</h3> <p>           H:  :<p> <p>\\\\server.ru\\VM\\', sourceFileFormat, '</p> <p>      <strong> </strong> .   <strong>', numberOfRows,'</strong>.</p> <p>   2  . <strong> </strong>  <strong>  </strong>,     vCenter   2 </p> <p>        .      </p> <p><img src="data/meme.jpg"></p> </html>' ) #    if (file.exists(filenameIncorrect)) {file.remove(filenameIncorrect)} #       .. source(file = "email.R", local = T, encoding = "utf-8") ####       #### send.mail(from = emailParams$from, to = emailParams$to, subject = "    ", body = emailBody, encoding = "utf-8", html = TRUE, inline = TRUE, smtp = emailParams$smtpParams, authenticate = TRUE, send = TRUE, attach.files = c(filenameIncorrect, filenameVmCreationRules), debug = FALSE) ####   ,      #### } else { #    emailBody <- paste0( '<html> <h3> ,  </h3> <p>           H:  :<p> <p>\\\\server.ru\\VM\\', sourceFileFormat, '</p> <p>,   ,     </p> <p><img src="data/meme_correct.jpg"></p> </html>' ) ####      VM #### send.mail(from = emailParams$from, to = emailParams$to, subject = " ", body = emailBody, encoding = "utf-8", html = TRUE, inline = TRUE, smtp = emailParams$smtpParams, authenticate = TRUE, send = TRUE, debug = FALSE) } #######     ##### source(file = "DB.R", local = T, encoding = "utf-8")

Script for getting vm list on PowerShell

 #       $vCenterNames = @( "vcenter01", "vcenter02", "vcenter03" ) $vCenterUsername = "myusername" $vCenterPassword = "mypassword" $filename = "C:\Scripts\getVm\data\allvm\all-vm-$(get-date -f yyyy-MM-dd).csv" $destinationSMB = "\\server.ru\myfolder$\vm" $IP0="" $IP1="" $IP2="" $IP3="" $IP4="" $IP5="" #    vCenter,    .  ,      (, ) Connect-VIServer -Server $vCenterNames -User $vCenterUsername -Password $vCenterPassword write-host "" #       vCenter- function Get-VMinventory { #       ,   $AllVM = Get-VM | Sort Name $cnt = $AllVM.Count $count = 1 #            foreach ($vm in $AllVM) { $StartTime = $(get-date) $IP0 = $vm.Guest.IPAddress[0] $IP1 = $vm.Guest.IPAddress[1] $IP2 = $vm.Guest.IPAddress[2] $IP3 = $vm.Guest.IPAddress[3] $IP4 = $vm.Guest.IPAddress[4] $IP5 = $vm.Guest.IPAddress[5] If ($IP0 -ne $null) {If ($IP0.Contains(":") -ne 0) {$IP0=""}} If ($IP1 -ne $null) {If ($IP1.Contains(":") -ne 0) {$IP1=""}} If ($IP2 -ne $null) {If ($IP2.Contains(":") -ne 0) {$IP2=""}} If ($IP3 -ne $null) {If ($IP3.Contains(":") -ne 0) {$IP3=""}} If ($IP4 -ne $null) {If ($IP4.Contains(":") -ne 0) {$IP4=""}} If ($IP5 -ne $null) {If ($IP5.Contains(":") -ne 0) {$IP5=""}} $cluster = $vm | Get-Cluster | Select-Object -ExpandProperty name $Bootime = $vm.ExtensionData.Runtime.BootTime $TotalHDDs = $vm.ProvisionedSpaceGB -as [int] $CreationDate = $vm.CustomFields.Item("CreationDate") -as [string] $Creator = $vm.CustomFields.Item("Creator") -as [string] $Category = $vm.CustomFields.Item("Category") -as [string] $Owner = $vm.CustomFields.Item("Owner") -as [string] $Subsystem = $vm.CustomFields.Item("Subsystem") -as [string] $IPS = $vm.CustomFields.Item("IP") -as [string] $vCPU = $vm.NumCpu $CorePerSocket = $vm.ExtensionData.config.hardware.NumCoresPerSocket $Sockets = $vCPU/$CorePerSocket $Id = $vm.Id.Split('-')[2] -as [int] #       $Vmresult = New-Object PSObject $Vmresult | add-member -MemberType NoteProperty -Name "Id" -Value $Id $Vmresult | add-member -MemberType NoteProperty -Name "VM Name" -Value $vm.Name $Vmresult | add-member -MemberType NoteProperty -Name "Cluster" -Value $cluster $Vmresult | add-member -MemberType NoteProperty -Name "Esxi Host" -Value $VM.VMHost $Vmresult | add-member -MemberType NoteProperty -Name "IP Address 1" -Value $IP0 $Vmresult | add-member -MemberType NoteProperty -Name "IP Address 2" -Value $IP1 $Vmresult | add-member -MemberType NoteProperty -Name "IP Address 3" -Value $IP2 $Vmresult | add-member -MemberType NoteProperty -Name "IP Address 4" -Value $IP3 $Vmresult | add-member -MemberType NoteProperty -Name "IP Address 5" -Value $IP4 $Vmresult | add-member -MemberType NoteProperty -Name "IP Address 6" -Value $IP5 $Vmresult | add-member -MemberType NoteProperty -Name "vCPU" -Value $vCPU $Vmresult | Add-Member -MemberType NoteProperty -Name "CPU Sockets" -Value $Sockets $Vmresult | Add-Member -MemberType NoteProperty -Name "Core per Socket" -Value $CorePerSocket $Vmresult | add-member -MemberType NoteProperty -Name "RAM (GB)" -Value $vm.MemoryGB $Vmresult | add-member -MemberType NoteProperty -Name "Total-HDD (GB)" -Value $TotalHDDs $Vmresult | add-member -MemberType NoteProperty -Name "Power State" -Value $vm.PowerState $Vmresult | add-member -MemberType NoteProperty -Name "OS" -Value $VM.ExtensionData.summary.config.guestfullname $Vmresult | Add-Member -MemberType NoteProperty -Name "Boot Time" -Value $Bootime $Vmresult | add-member -MemberType NoteProperty -Name "VMTools Status" -Value $vm.ExtensionData.Guest.ToolsStatus $Vmresult | add-member -MemberType NoteProperty -Name "VMTools Version" -Value $vm.ExtensionData.Guest.ToolsVersion $Vmresult | add-member -MemberType NoteProperty -Name "VMTools Version Status" -Value $vm.ExtensionData.Guest.ToolsVersionStatus $Vmresult | add-member -MemberType NoteProperty -Name "VMTools Running Status" -Value $vm.ExtensionData.Guest.ToolsRunningStatus $Vmresult | add-member -MemberType NoteProperty -Name "Creation Date" -Value $CreationDate $Vmresult | add-member -MemberType NoteProperty -Name "Creator" -Value $Creator $Vmresult | add-member -MemberType NoteProperty -Name "Category" -Value $Category $Vmresult | add-member -MemberType NoteProperty -Name "Owner" -Value $Owner $Vmresult | add-member -MemberType NoteProperty -Name "Subsystem" -Value $Subsystem $Vmresult | add-member -MemberType NoteProperty -Name "IP's" -Value $IPS $Vmresult | add-member -MemberType NoteProperty -Name "vCenter Name" -Value $vm.Uid.Split('@')[1].Split(':')[0] #           .   ,      . $elapsedTime = $(get-date) - $StartTime $totalTime = "{0:HH:mm:ss}" -f ([datetime]($elapsedTime.Ticks*($cnt - $count))) clear-host Write-Host "Processing" $count "from" $cnt Write-host "Progress:" ([math]::Round($count/$cnt*100, 2)) "%" Write-host "You have about " $totalTime "for cofee" Write-host "" $count++ #  ,   ""       $Vmresult } } #         csv $allVm = Get-VMinventory | Export-CSV -Path $filename -NoTypeInformation -UseCulture -Force #         ,   ,  . try { Copy-Item $filename -Destination $destinationSMB -Force -ErrorAction SilentlyContinue } catch { $error | Export-CSV -Path $filename".error" -NoTypeInformation -UseCulture -Force }

The script on PowerShell, pulling out the logs of the creators of virtual machines and the date of their creation

 #   ,      VM $VMfilePath = "C:\Scripts\getVm\creators_VM\creators_VM_$(get-date -f yyyy-MM-dd).csv" #   ,      $filePath = "C:\Scripts\getVm\data\creators\creators-$(get-date -f yyyy-MM-dd).csv" #   Workflow GetCreators-Wf { # ,        param([string[]]$VMfilePath) # ,     workflow $vCenterUsername = "myusername" $vCenterPassword = "mypassword" $daysToLook = 14 $start = (get-date).AddDays(-$daysToLook) $finish = get-date # ,     csv  ,       $UnknownUser = "UNKNOWN" $UnknownCreatedTime = "0000-00-00" #      ,      . $vCenterNames = @( "vcenter01", "vcenter02", "vcenter03" ) #   VM  csv     $list = Import-Csv $VMfilePath -UseCulture | select -ExpandProperty VM.Name # ,     ( 5   ) foreach -parallel ($row in $list) { #  ,       ,     $Using InlineScript { #      $StartTime = $(get-date) Write-Host "" Write-Host "Processing $Using:row started at $StartTime" Write-Host "" #    ,         $con = Connect-VIServer -Server $Using:vCenterNames -User $Using:vCenterUsername -Password $Using:vCenterPassword #   vm $vm = Get-VM -Name $Using:row #  2  .     ,  - .   , $Event = $vm | Get-VIEvent -Start $Using:start -Finish $Using:finish -Types Info | Where { $_.Gettype().Name -eq "VmBeingDeployedEvent" -or $_.Gettype().Name -eq "VmCreatedEvent" -or $_.Gettype().Name -eq "VmRegisteredEvent" -or $_.Gettype().Name -eq "VmClonedEvent"} # $Event = $vm | Get-VIEvent -Types Info | Where { $_.Gettype().Name -eq "VmBeingDeployedEvent" -or $_.Gettype().Name -eq "VmCreatedEvent" -or $_.Gettype().Name -eq "VmRegisteredEvent" -or $_.Gettype().Name -eq "VmClonedEvent"} #      ,      - If (($Event | Measure-Object).Count -eq 0){ $User = $Using:UnknownUser $Created = $Using:UnknownCreatedTime $CreatedFormat = $Using:UnknownCreatedTime } Else { If ($Event.Username -eq "" -or $Event.Username -eq $null) { $User = $Using:UnknownUser } Else { $User = $Event.Username } # Else $CreatedFormat = $Event.CreatedTime #     ,      ,   .      . $Created = $Event.CreatedTime.ToString('yyyy-MM-dd') } # Else Write-Host "Creator for $vm is $User. Creating object." #  .  . $Vmresult = New-Object PSObject $Vmresult | add-member -MemberType NoteProperty -Name "VM Name" -Value $vm.Name $Vmresult | add-member -MemberType NoteProperty -Name "CreatedBy" -Value $User $Vmresult | add-member -MemberType NoteProperty -Name "CreatedOn" -Value $CreatedFormat $Vmresult | add-member -MemberType NoteProperty -Name "CreatedOnFormat" -Value $Created #   $Vmresult } # Inline } # ForEach } $Creators = GetCreators-Wf $VMfilePath #     $Creators | select 'VM Name', CreatedBy, CreatedOn | Export-Csv -Path $filePath -NoTypeInformation -UseCulture -Force Write-Host "CSV generetion finisghed at $(get-date). PROFIT"

The xlsx library deserves special attention, which allowed making an attachment to the letter clearly formatted (as the manual likes), and not just a csv table.

Forming a beautiful xlsx document with a list of incorrectly filled machines

 #    #   : "xls"  "xlsx" wb<-createWorkbook(type="xlsx") #         TABLE_ROWNAMES_STYLE <- CellStyle(wb) + Font(wb, isBold=TRUE) TABLE_COLNAMES_STYLE <- CellStyle(wb) + Font(wb, isBold=TRUE) + Alignment(wrapText=TRUE, horizontal="ALIGN_CENTER") + Border(color="black", position=c("TOP", "BOTTOM"), pen=c("BORDER_THIN", "BORDER_THICK")) #    sheet <- createSheet(wb, sheetName = names[2]) #   addDataFrame(incorrect_df_filtered, sheet, startRow=1, startColumn=1, row.names=FALSE, byrow=FALSE, colnamesStyle = TABLE_COLNAMES_STYLE, rownamesStyle = TABLE_ROWNAMES_STYLE) #  ,     autoSizeColumn(sheet = sheet, colIndex=c(1:ncol(incorrect_df))) #   addAutoFilter(sheet, cellRange = "C1:G1") #   fo2 <- Fill(foregroundColor="red") cs2 <- CellStyle(wb, fill = fo2, dataFormat = DataFormat("@")) #              rowsOwner <- getRows(sheet, rowIndex = (which(!incorrect_df$isOwnerCorrect) + 1)) cellsOwner <- getCells(rowsOwner, colIndex = which( colnames(incorrect_df_filtered) == "Owner" )) lapply(names(cellsOwner), function(x) setCellStyle(cellsOwner[[x]], cs2)) #              rowsSubsystem <- getRows(sheet, rowIndex = (which(!incorrect_df$isSubsystemCorrect) + 1)) cellsSubsystem <- getCells(rowsSubsystem, colIndex = which( colnames(incorrect_df_filtered) == "Subsystem" )) lapply(names(cellsSubsystem), function(x) setCellStyle(cellsSubsystem[[x]], cs2)) #    rowsCategory <- getRows(sheet, rowIndex = (which(!incorrect_df$isCategoryCorrect) + 1)) cellsCategory <- getCells(rowsCategory, colIndex = which( colnames(incorrect_df_filtered) == "Category" )) lapply(names(cellsCategory), function(x) setCellStyle(cellsCategory[[x]], cs2)) #  rowsCreator <- getRows(sheet, rowIndex = (which(!incorrect_df$isCreatorCorrect) + 1)) cellsCreator <- getCells(rowsCreator, colIndex = which( colnames(incorrect_df_filtered) == "Creator" )) lapply(names(cellsCreator), function(x) setCellStyle(cellsCreator[[x]], cs2)) #   saveWorkbook(wb, filenameIncorrect)

The output is approximately like this:

There was also an interesting nuance for setting up a Windows scheduller. It did not work to find the right parameters of rights and settings, so that everything starts as it should. As a result, the R library was found, which itself creates the task for launching the R script and does not even forget about the file for the logs. Then you can correct the task handles.

A piece of R code with two examples that creates a task in Windows Scheduler

 library(taskscheduleR) myscript <- file.path(getwd(), "all_vm.R") ##    62  taskscheduler_create(taskname = "getAllVm", rscript = myscript, schedule = "ONCE", starttime = format(Sys.time() + 62, "%H:%M")) ##      09:10 taskscheduler_create(taskname = "getAllVmDaily", rscript = myscript, schedule = "WEEKLY", days = c("MON", "TUE", "WED", "THU", "FRI"), starttime = "02:00") ##   taskscheduler_delete(taskname = "getAllVm") taskscheduler_delete(taskname = "getAllVmDaily") #   ( 4 ) tail(readLines("all_vm.log"), sep ="\n", n = 4)

Separately about the database

After setting up the script began to appear other issues. For example, I wanted to find a date when the VM was deleted, and the logs in the vCenter were already rubbed. Since the script adds files to the folder every day and doesn’t clean it (we clean it when we recall), we can look at the old files and find the first file in which this VM does not exist. But this is not cool.

I wanted to create a historical database.

The MS SQL SERVER functional - system-versioned temporal table came to the rescue. It is usually translated as temporary (non-temporary) tables.

You can read in detail in the official Microsoft documentation .

In short, we create a table, we say that we will have it with versioning and SQL Server creates 2 additional datetime columns in this table (the date the record was created and the date the record ends) and an additional table to which the changes will be written. As a result, we obtain up-to-date information and, by simple queries, examples of which are given in the documentation, we can see either the life cycle of a specific virtual machine, or the state of all VMs at a certain point in time.

From a performance point of view, the write transaction to the main table will not be completed until the write transaction to the temporary table is completed. Those. on tables with a large number of write operations, this functionality should be implemented with caution, but in our case it’s a really cool thing.

In order for the mechanism to work correctly, it was necessary for R to add a small piece of code that would compare the new table with the data for all VMs with the one stored in the database and write only changed rows into it. The code is not particularly tricky, it uses the compareDF library, but I will also give it below.

Code for R by writing data to the database

 #   library(odbc) library(compareDF) #   con <- dbConnect(odbc(), Driver = "ODBC Driver 13 for SQL Server", Server = DBParams$server, Database = DBParams$database, UID = DBParams$UID, PWD = DBParams$PWD, Port = 1433) ####    .   - . #### if (!dbExistsTable(con, DBParams$TblName)) { ####   #### create <- dbSendStatement( con, paste0( 'CREATE TABLE ', DBParams$TblName, '( [Id] [int] NOT NULL PRIMARY KEY CLUSTERED, [VM.Name] [varchar](255) NULL, [Cluster] [varchar](255) NULL, [Esxi.Host] [varchar](255) NULL, [IP.Address.1] [varchar](255) NULL, [IP.Address.2] [varchar](255) NULL, [IP.Address.3] [varchar](255) NULL, [IP.Address.4] [varchar](255) NULL, [IP.Address.5] [varchar](255) NULL, [IP.Address.6] [varchar](255) NULL, [vCPU] [int] NULL, [CPU.Sockets] [int] NULL, [Core.per.Socket] [int] NULL, [RAM..GB.] [int] NULL, [Total.HDD..GB.] [int] NULL, [Power.State] [varchar](255) NULL, [OS] [varchar](255) NULL, [Boot.Time] [varchar](255) NULL, [VMTools.Status] [varchar](255) NULL, [VMTools.Version] [int] NULL, [VMTools.Version.Status] [varchar](255) NULL, [VMTools.Running.Status] [varchar](255) NULL, [Creation.Date] [varchar](255) NULL, [Creator] [varchar](255) NULL, [Category] [varchar](255) NULL, [Owner] [varchar](255) NULL, [Subsystem] [varchar](255) NULL, [IP.s] [varchar](255) NULL, [vCenter.Name] [varchar](255) NULL, DateFrom datetime2 GENERATED ALWAYS AS ROW START NOT NULL, DateTo datetime2 GENERATED ALWAYS AS ROW END NOT NULL, PERIOD FOR SYSTEM_TIME (DateFrom, DateTo) ) ON [PRIMARY] WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = ', DBParams$TblHistName,'));' ) ) #    dbClearResult(create) } # if ####     #### #  ,     allVM_db_con <- tbl(con, DBParams$TblName) ####   #### #     (   ) allVM_db <- allVM_db_con %>% select(c(-"DateTo", -"DateFrom")) %>% collect() #     .   Id #       -,   +,   -  + ctable_VM <- fullXslx_df %>% compare_df(allVM_db, c("Id")) ####   #### #  Id ,      remove_Id <- ctable_VM$comparison_df %>% filter(chng_type == "-") %>% select(Id) # ,    (   -     ) if (remove_Id %>% nrow() > 0) { #        delete <- dbSendStatement(con, paste0(' DELETE FROM ', DBParams$TblName, ' WHERE "Id"=? ') # paste ) # send #      dbBind(delete, remove_Id) #    dbClearResult(delete) } # if ####   #### #  ,  ,   . allVM_add <- ctable_VM$comparison_df %>% filter(chng_type == "+") %>% select(-chng_type) # ,   ,      (  -  ) if (allVM_add %>% nrow() > 0) { #       dbWriteTable(con, DBParams$TblName, allVM_add, overwrite = FALSE, append = TRUE) } # if ####     #### dbDisconnect(con)

Total

As a result of the introduction of the script, order was established and maintained over several months. Sometimes incorrectly filled VMs appear, but the script serves as a good reminder and a rare VM gets into the list 2 days in a row.

A reserve was also made for the analysis of historical data.

It is clear that much of this can be implemented not “on the knee”, but by specialized software, but the task was interesting and, one might say, optional.

R has once again proved to be an excellent universal language, which is perfect not only for solving statistical problems, but also acts as an excellent “lining” between other data sources.

Source: https://habr.com/ru/post/454400/

All Articles