Submitting your feedback...
Knowledge Base Article
3065OSI8 - How to delete archive and snapshot events
Product: PI Data Archive
Version(s): 3.3 and higher

Issue

  • How can I delete one or many snapshot or archive events?
  • How can I delete a large number of events from the archive?

Solution

The example scripts and instructions in this Knowledge Base Article are split into the different tools available to delete archive and snapshot events.
To delete a small number of specific archive events, see the section PI SMT Archive Editor Plug-in.

To delete a large number of events, or the current snapshot event, see the following sections based on the tool you prefer to use:
  • PowerShell Tools for the PI System - installed with PI System Management Tools 2015+. This is recommended to be tried first for PI Data Archive versions 3.4.380.36 and above. In general, the most robust deletion tool is the PowerShell Tools for the PI System.
  • PI-OLEDB 
  • piconfig - installed with the PI Data Archive

Known issues which could prevent events from being deleted

There are several known issues which could prevent events from being deleted as expected

Refer to the Known Issues below if events are unable to be deleted after following the steps on this page:

Known issues with all deletion methods:

Known issues with piconfig:

Known issues with SMT Archive Editor:

Known issues with snapshot event deletion:

  • Although this is not an official bug or "known issue," please be aware that snapshot events for buffered points cannot be deleted by non-buffered sources. See the last item under "Notes" below.

Notes

  • ALWAYS BACKUP YOUR DATA BEFORE DELETING. There is no way to undo a delete with any utility. OSIsoft strongly recommends that you practice deleting small amounts of data on a test system before deleting real data.
  • Deleting large amounts of data may affect availability of an online PI Data Archive. If you continually need to perform bulk deletions, try to understand and correct the root cause. For example, use appropriate exception/compression settings, increase the available disk space, move older archives to a second tier storage and so on, instead of deleting data in bulk.
  • For more information on using piconfig and deleting values from the PI Archive table (piarc), see the piconfig section of PI Data Archive User Guide in Live Library.
  • You can also delete a small number of events using PI Manual Logger. See the Review previous tag values section in the PI Manual Logger Data Collector Guide.
  • See KB 2987OSI8 for information on how to retrieve subsecond time stamps.
  • Note that "deleting" events is actually just sending back an event with the delete or remove write mode. As such, the deletion events sent to delete snapshot events will still be considered "in-order" and will still be subject to encountering the buffer lock error "-11414", if the point whose snapshot is being deleted is buffered. Refer to KB00093 - Send Exception Error "[-11414] Buffered point does not accept new events"for additional details, or if there are difficulties deleting snapshot events for buffered points.

PI SMT Archive Editor plug-in

  1. Run PI System Management Tools.
  2. Select the server containing the event you want to delete.
  3. Open Data > Archive Editor.
  4. Right-click the event you want to delete and select Delete.
  5. Click the Save changes icon for your deletions to take effect. Note that there is no prompt to confirm deletions.

PowerShell Tools for the PI System

If you have PI System Management Tools 2015 installed AND the version of PI Data Archive is 3.4.380 and above, you can use Powershell Tools for PI System to delete the current snapshot or archived events.

Deleting the current snapshot event 

$con=Connect-PIDataArchive -PIDataArchiveMachineName MyPIServer
Remove-PIValue -Event (Get-PIValue -PointName sinusoid -Time (Get-Date) -ArchiveMode Previous -Connection $con) -Connection $con

To verify that the value is deleted, you can use a command like the following example:
Get-PIValue -PointName sinusoid -Time (Get-Date) -Connection $con​

Deleting archive events within a time range

First, you can verify the list of events to be deleted by running the following command:
$con=Connect-PIDataArchive -PIDataArchiveMachineName MyPIServer
Get-PIValue -StartTime "01-jan-2016" -EndTime "03-jan-2016" -PointName sinusoid -Connection $con

To delete the above events:
Remove-PIValue -Event (Get-PIValue -StartTime "01-jan-2016" -EndTime "03-jan-2016" -PointName sinusoid -Connection $con) -Connection $con

Deleting archive events with a specific value within a time range

This command will list all of the events that are equal to 94.827 for tag sinusoid for the time range of "20-Apr-16 13:00:00" until "20-Apr-16 13:15:00"

To display the results into an easily viewable table format, 'Select-Object -Property Timestamp, Value' was appended.
$con=Connect-PIDataArchive -PIDataArchiveMachineName MyPIServer
$pt = Get-PIPoint -Name sinusoid -Connection $con
Select-Object -Property TimeStamp, Value
Get-PIValue -PIPoint $pt -StartTime "20-Apr-16 13:00:00" -EndTime "20-Apr-16 13:15:00" | Where-Object {$_.Value -eq 94.827} |Select TimeStamp, Value

Note: If you are using PI System Management Tools 2016 R2, specify -AllAttributes after Get-PIPoint$pt = Get-PIPoint -AllAttributes -Name sinusoid -Connection $con

Once you verified the list, run the following command to delete the specific events:

Remove-PIValue -Event (Get-PIValue -PIPoint $pt -StartTime "20-Apr-16 13:00:00" -EndTime "20-Apr-16 13:15:00" | Where-Object {$_.Value -eq -94.827}) -Connection $con

Deleting archive events with a specific Digital State value within a time range

This command will list all of the events that have the System Digital State Set value 'Shutdown' (Set: 0 Offset: 254) for tag sinusoid for the time range of "20-Apr-16 13:00:00" until "20-Apr-16 13:15:00"

To display the results into an easily viewable table format, 'Select-Object -Property Timestamp, Value' was appended.
$con=Connect-PIDataArchive -PIDataArchiveMachineName MyPIServer
$pt = Get-PIPoint -Name sinusoid -Connection $con
Select-Object -Property TimeStamp, Value
Get-PIValue -PIPoint $pt -StartTime "20-Apr-16 13:00:00" -EndTime "20-Apr-16 13:15:00" | Where-Object { ($_.Value.State -eq 254) -and ($_.Value.StateSet -eq 0) } |Select TimeStamp, Value

Note: If you are using PI System Management Tools 2016 R2, specify -AllAttributes after Get-PIPoint$pt = Get-PIPoint -AllAttributes -Name sinusoid -Connection $con

Once you verified the list, run the following command to delete the specific events:
Remove-PIValue -Event (Get-PIValue -PIPoint $pt -StartTime "20-Apr-16 13:00:00" -EndTime "20-Apr-16 13:15:00" | Where-Object { ($_.Value.State -eq 254) -and ($_.Value.StateSet -eq 0) }) -Connection $con

Deleting archive events within a time range

Here are some methods to delete archive events within a time range.
  1. Deleting by a common attribute such as pointsource:
    $conn = Connect-PIDataArchive -PIDataArchiveMachineName myserver
    Get-PIPoint -Connection $conn -WhereClause 'pointsource:=R' | % { Get-PIValue -PIPoint $_ -StartTime '2016-01-08' -EndTime '2016 01-09' | Remove-PIValue -Connection $conn }
    
    
    Note: if you are using PI System Management Tools 2016 R2 and above, specify -AllAttributes after Get -PIPoint: Get-PIPoint -AllAttributes -Connection ...
  2. Deleting using a group of tagnames:
    $conn = Connect-PIDataArchive -PIDataArchiveMachineName myserver
    $tagnames = @('sinusoid','sinusoidu','cdt158')  
    $tagnames | % { Get-PIValue -PointName $_ -Connection $conn -StartTime '2016-01-09' -EndTime '2016-01-10' | Remove-PIValue -Connection $conn }
    
    
    If you would like to import a list of tagnames, you can have a text file that has each tagname line by line, which then can be imported replacing the second line with:
    $tagnames = Get-Content ..\path\to\file\taglist.txt.
    
    
    Example of the taglist.txt file:
    sinusoid
    sinusoidu
    cdt158
    
    
  3. If you are deleting a large amount of events, then you may want to use separate queries and iterate them in order to not run into -10722 timeout errors. This script is split into 5 queries:
     
    [DateTime] $startTime = '2016-01-10'
    [DateTime] $endTime = '2016-01-15 13:00'
    $diff = $endTime - $startTime
    $iterations = 5
    $eachIteration = $diff.Days /$iterations
    
    $conn = Connect-PIDataArchive -PIDataArchiveMachineName kd-lucille
    $tags = Get-PIPoint -Connection $conn -WhereClause 'pointsource:=R'
    
    Write-host $tags
    
    $tags | % {
    
        $thisTag = $_
        $thisTagName = $thisTag.Point.Name
        Write-Host " Now processing $thisTagName "
    
        0..($iterations - 1) | % {
    
            $start = $startTime.AddDays($_ * $eachIteration)
            $end = $start.AddDays($eachIteration)
    
            if ($_ -eq ($iterations - 1)) { $end = $endTime }
            Write-Host "Now processing $thisTagName from $start to $end"
            Get-PIValue -PIPoint $thisTag -StartTime $start -EndTime $end | Remove-PIValue -Connection $conn
        }
    }
    
    
    Note: If you are using PI System Management Tools 2016 R2, specify -AllAttributes after Get -PIPoint: Get-PIPoint -AllAttributes -Connection ...

PI-OLEDB

If you have PI-OLEDB installed, you can use the PI-OLEDB Tester to remove event(s). Run "%pihome%\OLEDB\Tools\PI OLEDB Tester\PIOLEDBTester.exe" and connect to the server containing the event(s) you want to delete.

Deleting the current snapshot event

In the window with label "SQL Statement", enter the a query like the following example:
DELETE from picomp2 WHERE tag='sinusoid' AND time=(SELECT time FROM picomp WHERE tag='sinusoid' AND time='*')

Click the "Execute" button below the window
To verify that the value is deleted, you can use a query like the following example:
SELECT time, value FROM piarchive..picomp WHERE tag='sinusoid' AND time='*'

Deleting archive events within a time range

In the window with label "SQL Statement", enter a query like the following example:
DELETE from piarchive..picomp2 WHERE tag='sinusoid' AND time BETWEEN 'y' AND 't'


To verify that the events are deleted, enter a query like the following example:
SELECT time,value FROM piarchive..picomp WHERE tag='sinusoid' AND time BETWEEN 'y' AND 't'

piconfig

Deleting the current snapshot event

Note that when you delete the current snapshot event it is immediately replaced with the previous archived event. Also, to delete multiple events specify them in order starting with the earliest event.
  1. At a command prompt, change to the pi\adm directory.
  2. Type piconfig and press Enter.
  3. Verify the time of the snapshot event that you want to delete. Type the following commands and press Enter after each one. For TAGNAME substitute the name of the tag whose event you want to delete:
    @table pisnap
    @mode list
    @ostr tag, time, value
    @select tag = TAGNAME
    ​@ends
    
    

    The output displays the tag name, time stamp, and value of the event you specified. For example:
    TAGNAME,23-Apr-04 09:22:10, 2.3452
    
    
  4. Delete the event using the PIARC table. Type the following piconfig commands and press Enter after each one. For TAGNAME, substitute the name of the tag and time stamp of value you want to delete. You will not receive any verification that the snapshot is removed.
    @table piarc 
    @mode edit 
    @istru tag, time, mode 
    TAGNAME,23-Apr-04 09:22:10, remove 
    @ends 
    @bye
    
    
  5. To verify that the value is deleted, you can repeat Step 3 or run APISNAP to see the current snapshot for the tag. You should see a new snapshot event.

Find and delete archive events in two steps

Note: Before using piconfig, or if there are difficulties deleting events with piconfig, consider using the PowerShell Tools for the PI System documented above.

The script in this example produces a record of all deleted events. If you do not need this detail, you can simplify the script by specifying tag and time only but not the value.
  1. Create a text file and enter the following commands:
    *Input file to find events
    @table piarc
    @mode list
    @output output.txt
    @timf 9
    @sigd 9
    @istru tag, starttime, endtime, count
    @ostru tag, time, value
    @ostru ... 
    , , ,
    @ends
    
    
  2. Replace " , , , " with the actual tag name, start time, end time, and count of the events you want to delete. For example, to retrieve the last 10 minutes of events (up to 15,000 events) for sinusoid:
    Sinusoid, *-10m, *, 15000
    
    

    Events from multiple points can be deleted by replacing " , , ," using the same format, placing the parameters for each point on a separate line:
    Sinusoid, *-15m, *, 15000
    cdt158, *-30m, *, 15000
    SinusoidU, *-45m, *, 15000
    
    
  3. Save the file as FINDEVENTS.TXT to the pi\adm directory.
  4. Open a command window, change to the pi\adm directory and run piconfig with the following syntax.
    C:\PI\adm>piconfig < findevents.txt
    
    

    This generates an output file called output.txt.
  5. Open the OUTPUT.TXT file in a text editor. (To open in Notepad, type notepad output.txt and press Enter.)
    Here is an example of OUTPUT.TXT file of events found for a specified time period:
    *> Sinusoid, *-10m, * 
    Sinusoid,8-Dec-05 11:39:05,40.923717499 
    Sinusoid,8-Dec-05 11:40:35,41.568099976
    
    
  6. Add the following piconfig commands on separate lines before the data (no empty lines):
    @table piarc
    @mode delete
    @istructure tag, time, value
    
    
  7. Add the @ends command after the data. Note that lines with asterisks will be ignored. After you are done, your file looks something like this:
    *> Sinusoid, *-10m, *
    @table piarc
    @mode delete
    @istructure tag, time, value
    Sinusoid,8-Dec-05 11:39:05,40.923717499
    Sinusoid,8-Dec-05 11:40:35,41.568099976
    
    
  8. Save the modified file as DELEVENTS.TXT and then redirect this DELEVENTS.TXT file back into piconfig using the following syntax to delete all the events in the file:

    C:\PI\adm>piconfig < delevents.txt
    
    

    Your events should now be deleted. You can verify this by repeating the first five steps; the deleted events will be absent from the OUTPUT.TXT file. You can also use PI DataLink, Processbook, and other tools to verify that the events are deleted.

Find and delete archived events in a single script

The following example applies only to piconfig 3.3.362.47 and higher. Earlier versions of PI 3.3 do not accept the place holder parameters (%1%,%2%,%3%). To use this example with those versions, you must hard-code the parameters.

Note: Before using piconfig, or if there are difficulties deleting events with piconfig, consider using the PowerShell Tools for the PI System documented above.

Also note that his example finds and deletes events in a single script. It accepts the parameters you specify when you input the file. Because you will not have time to review the events before you delete them, use this script with caution and ALWAYS KEEP CURRENT BACKUPS OF YOUR DATA.
  1. Copy the following commands into a text file and save the file as DELEVENTS.TXT to the pi\adm directory. Make sure not to lose any hard returns when copying and pasting this text.
    *DELEVENTS.TXT
    @table piarc
    @mode list
    @istructure tag, starttime, endtime, count
    @ostructure tag, time
    @ostructure ...
    @timf 9
    @sigd 9
    @output tmpdelevents.dat
    %1%,%2%,%3%,10000
    @output
    *@exit - uncomment this to exit and review before deleting
    @mode ed,t
    @modify mode=remove
    @istructure tag, time
    @input tmpdelevents.dat
    @exit
    
    
  2. Open a command window, change to the pi\adm directory and run piconfig with the following syntax. Replace , , , with the name of the tag, the start time, and the end time between which you want to delete events:
    piconfig input delevents.txt , , , exit
    
    

    The following example will delete all sinusoid events for the last ten minutes:
    C:\PI\adm>piconfig input delevents.txt,Sinusoid, *-10m, * exit
    
    

    Note: Make sure there are no spaces between the parameters., Also note, that the redirection symbol (<) does not work in this case, so be sure to use the input command.

Delete events for more than one tag at a time

Note: Before using piconfig, or if there are difficulties deleting events with piconfig, consider using the PowerShell Tools for the PI System documented above.
  1. Copy the following commands into a text file.
    *DELEVENTS.TXT
    @table piarc
    @mode list
    @istructure tag, starttime, endtime, count
    @ostructure tag, time, value
    @ostructure ...
    @timf 9
    @sigd 9
    @output tmpdelevents.dat
    , , ,
    , , ,
    , , ,
    *@exit - uncomment this to exit and review before deleting
    @mode edit
    @modify mode=remove
    @istructure tag, time, value
    @input tmpdelevents.dat
    @exit
    
    
  2. Replace each , , , with the actual tag name, start time, end time, and count of the events you want to delete. You can add as many lines as you wish, each with the same structure as defined above. For example:
    sinusoid,y,t,10000
    cdt158,y,t,10000
    cdm158,y,t,10000
    sinusoidu,y,t,10000
    BA:Active,y,t,10000
    
    
  3. Save the file as DELEVENTS.TXT to the pi\adm directory (make sure not to lose any hard returns when copying and pasting this text).
  4. Open a command window, change to the pi\adm directory, and run piconfig with DELEVENTS.TXT redirected back into piconfig to delete the events specified in the file.
    C:\PI\adm>piconfig < delevents.txt
    
    
  5. Check to see if all values have been deleted. If some digital state values such as 'Shutdown' or 'Bad Value' still remain, then change 'value' to 'status' in the existing script and run it again to delete the digital state values:
    *DELEVENTS.TXT
    @table piarc
    @mode list
    @istructure tag, starttime, endtime, count
    @ostructure tag, time, status
    @ostructure ...
    @timf 9
    @sigd 9
    @output tmpdelevents.dat
    , , ,
    , , ,
    , , ,
    *@exit - uncomment this to exit and review before deleting
    @mode edit
    @modify mode=remove
    @istructure tag, time, value
    @input tmpdelevents.dat
    @exit
    
    
Article ID: 3065OSI8 Created: 2004-05-21
Article Type: How-To Last Updated: 2017-09-28