Bentley Communities
Bentley Communities
  • Site
  • User
  • Site
  • Search
  • User
ProjectWise
  • Product Communities
ProjectWise
ProjectWise PowerShell Extensions Forum How to Find Where an Environment has been used
    • Sign In

    • State Verified Answer
    • Replies 6 replies
    • Answers 1 answer
    • Subscribers 67 subscribers
    • Views 808 views
    • Users 0 members are here
    • projectwise admin
    • PWPowerShell
    • ProjectWise

    How to Find Where an Environment has been used

    Robert Golding
    Offline Robert Golding over 1 year ago

    How can I easily find where an Environment has been selected / used?

    I am trying to clean up our Datasource and possibly remove some older Environments. I believe there are some projects which can be archived  and Environments changed to our updated one.

    I know I can create a new View in Explorer with the Foldername and Environment name but we have a very large Datasource with a large folder structure, i'm hoping to pinpoint with PowerShell or similar.

    Something like this (i know the syntax would be way off, its just an example)

    SELECT FolderPath WHERE EnvironmentName = 'Env1'

    Thanks!

    • Sign in to reply
    • Cancel

    Top Replies

    • Dave Cole
      Offline Dave Cole Tue, Apr 5 2022 8:38 AM +1
      I use this SQL and it runs faster than the equivalent PWPS SELECT [o_projectno],[o_envno],[o_projectcode],[o_projguid] FROM dbo.dms_proj WHERE o_envno LIKE '102' The environment number can be found…
    • Brian Flaherty
      Offline Brian Flaherty Wed, Apr 6 2022 5:20 PM in reply to Dave Cole +1 verified
      To expand on Dave's post. $EnvironmentName = 'Complex' $Environment = (Get-PWEnvironments).GetEnumerator() | Where-Object Name -EQ $EnvironmentName $SQL = "SELECT [o_projectno],[o_envno],[o_projectcode…
    • Kevin van Haaren
      0 Offline Kevin van Haaren Tue, Mar 29 2022 12:34 AM

      You can use Get-PWFolders to dump the entire folder structure and check it for the Environment. However Get-PWFolders doesn't put the full path in the returned data by default and if you tell it to, it is very slow. So I would select all the folders with the environment you're interested in, then get the full paths of those folders:

      $fldrList = Get-PWFolders | Where Environment -eq 'Env1'
      $fldrList = $flderList |  Get-PWFolderPathAndProperties
      $fldrList | Select FullPath,Environment
      

      Depending on the number of folders that will be very slow.

      Take a look at the Clear-PWEnvironmentByFolderPath cmdlet to clear the environment from the folders.

       

      • Cancel
      • Vote Up 0 Vote Down
      • Sign in to reply
      • Verify Answer
      • Cancel
    • Brian Flaherty
      0 Offline Brian Flaherty Tue, Mar 29 2022 11:18 AM in reply to Kevin van Haaren

      Don't forget, there is a -PopulatePaths switch parameter available with the Get-PWFolders cmdlet.

      • Cancel
      • Vote Up 0 Vote Down
      • Sign in to reply
      • Verify Answer
      • Cancel
    • Kevin van Haaren
      0 Offline Kevin van Haaren Tue, Mar 29 2022 12:04 PM in reply to Brian Flaherty

      I looked at that for the 2nd line but Get-PWFolders doesn't seem to take an array of folder ids/paths for input so I'd need to write a foreach loop and i got lazy. But then I just tried it with the first line and it's a lot faster than i thought. For 35000 folders it was only 25 seconds and for 230,000 folders it was about 15 minutes (and 700 MB of memory). If that's acceptable performance, or you have a ton of folders with the environment assigned, you can revise the script to:

      $fldrList = Get-PWFolders -PopulatePaths | Where Environment -eq 'Env1'
      $fldrList | Select FullPath, Environment

       

      • Cancel
      • Vote Up 0 Vote Down
      • Sign in to reply
      • Verify Answer
      • Cancel
    • Dave Cole
      0 Offline Dave Cole Tue, Apr 5 2022 8:38 AM

      I use this SQL and it runs faster than the equivalent PWPS

      SELECT [o_projectno],[o_envno],[o_projectcode],[o_projguid]
      FROM dbo.dms_proj
      WHERE o_envno LIKE '102'

      The environment number can be found in the dms_env table

      • Cancel
      • Vote Up +1 Vote Down
      • Sign in to reply
      • Verify Answer
      • Cancel
    • Brian Flaherty
      +1 Offline Brian Flaherty Wed, Apr 6 2022 5:20 PM in reply to Dave Cole

      To expand on Dave's post.

      $EnvironmentName = 'Complex'
      $Environment = (Get-PWEnvironments).GetEnumerator() | Where-Object Name -EQ $EnvironmentName
      
      $SQL = "SELECT [o_projectno],[o_envno],[o_projectcode],[o_projguid]
      FROM dbo.dms_proj
      WHERE o_envno LIKE '$($Environment.ID)'"
      
      $SQLResults = Select-PWSQL -SQLSelectStatement $SQL

      Answer Verified By: Robert Golding 

      • Cancel
      • Vote Up +1 Vote Down
      • Sign in to reply
      • Reject Answer
      • Cancel
    >

    Communities
    • Home
    • Getting Started
    • Community Central
    • Products
    • Support
    • Secure File Upload
    • Feedback
    Support and Services
    • Home
    • Product Support
    • Downloads
    • Subscription Services Portal
    Training and Learning
    • Home
    • About Bentley Institute
    • My Learning History
    • Reference Books
    Social Media
    •    LinkedIn
    •    Facebook
    •    Twitter
    •    YouTube
    •    RSS Feed
    •    Email

    © 2023 Bentley Systems, Incorporated  |  Contact Us  |  Privacy |  Terms of Use  |  Cookies