IT Monkey:   Join the Cireson Community today for your chance to win $50!
Cireson Partners, Customers and Community members share your customizations and examples here to help benefit the community as a whole to earn Kudos and badges.

DISCLAIMER

All files and projects located here are provided and come "as-is" and without any warranty or support. Use at your own risk. Your use of Community Uploads is subject to our Terms of Use.

Cireson does not and will not support or maintain these enhancements, extensions, and scripts.

For Team Cireson uploads click here.

SCSM - Last Modified Date Manager for IR and SR

Conner_WoodConner_Wood Customer Advanced IT Monkey ✭✭✭
edited December 2016 in Community Uploads
Want to keep your Last Modified Dates the same after importing the latest and greatest Advanced Request Offering Management Pack Bundle?

If ya don't, get outta here >:)

Presenting the SCSM - Last Modified Date Manager for IR and SR... I made it specifically for the purpose of preventing additional syncing after importing the Advanced Request Offering MPB.

I found it extremely useful since other areas of IT rely of Last Modified Date for some reports because if I alter all the Last Modified Dates to something else then obviously the report isn't accurate to what it really should be, hence why I made the program.

I recommend disabling the DW Job Schedules and letting all the jobs finish before proceeding with the upgrade, then after all of it you can re-enable the DW Job Schedules once more using powershell.
Disable cmd == Get-SCDWJob | ForEach-Object {Disable-SCDWJobSchedule -JobName $_.Name}
Enable cmd ==  Get-SCDWJob | ForEach-Object {Enable-SCDWJobSchedule -JobName $_.Name}


TechNet Instructions:

Welcome one and all, you may be wondering what this program is for.  Cireson has released Version 5 of their Portal.  After reading the fine print it turns out that after installing the Portal there's a management pack called "Cireson.AdvanceRequestOffering.mpb" which can be imported into SCSM to enhance functionality.  The downside is there's a price, all those Last Modified Dates for IR and SR Tickets that are in the Live SCSM Database will be changed to the date that this MPB gets imported.

  1. Everyone must use downtime procedures, meaning no one is doing things in SCSM except you.
  2. Now you use this program, enter the correct SCSM SQL Server and Database Name, then click the button "Load DataTable from SCSM SQL Database"
  3. Depending on how many IRs and SRs are present, this can take a few seconds, be patient and wait for the DataTable list to appear
  4. Congrats, you're now ready to click "Export DataTable to Save File" ... you'll notice it will save to the path where you will later load the save file.
  5. Feel free to find and backup this save file if you're smart/nervous (cause you're smart).
  6. Close the SCSM Last Modified Dates Manager Program for now..... open the SCSM Console
  7. Now import the Cireson MPB called "Cireson.AdvanceRequestOffering" and watch as all your Last Modified Dates are assimilated
  8. But you have a plan, and a save file, cause you've been following these steps in order proving you're smart... Close the SCSM Console
  9. So after waiting a bit until SCSM settles down, open up the SCSM Last Modified Dates Manager Program again
  10. Enter the correct SCSM SQL Server and Database Name, then click the button "Load Saved File" ...
  11. You'll see it's loaded all of your correct last modified dates, fantastic, now click "Overrwrite SCSM SQL Database From Loaded DataTable"
  12. Give it some time, there's quite a few entries to update, eventually a message box will pop-up
  13. You've done it, you've proven your worth, a winner is you!

 

Note:  If you have already applied the Cireson MPB, you could be smart and just use an earlier backup of the database and restore it elsewhere, then grab the correct last modified dates and save them to the save file, then connect to the current Live SCSM Database and Overwrite the Last Modified Dates from there if your DW hasn't already synced.


Comments

  • fe_fefe_fe Customer IT Monkey ✭
    Hi Connor,
    It could be usefull if you made it possible to just replace one LastModified date with a custom one that I set.

    Also, I'm not that comfortable with editing the SCSM DB directly.
    Is it possible to edit LastModified date through Powershell?
  • Conner_WoodConner_Wood Customer Advanced IT Monkey ✭✭✭
    Hi @fe_fe

    The goal of this program was to retain the original dates on the tickets themselves so the Data Warehouse wouldn't attempt to sync a large amount of information.

    Due to the undocumented complexity of SCSM's design, it was my design choice to only alter the database in a way that would be valid data (such as data already entered in the Database), allowing any particular date on last modified could result in issues so I didn't pursue that course of action.

    I'm sure you're not comfortable, and there's plenty of valid reasons why you normally shouldn't.  The real question is how many limitations are you and your superiors willing to tolerate from SCSM?

    SMLets might allow you to, but I have doubts because SMLets really just uses the C# SDK behind the scenes.

  • Konstantin_Slavin-BoKonstantin_Slavin-Bo Customer Advanced IT Monkey ✭✭✭
    @Conner_Wood
    Thanks for a great program! I used it, for the first time, recently, and worked wonderfully, reverting back all datetime to the saved ones, which is really helpful, so thanks a lot!

    We experienced a kinda odd side-effect though, where all WIs was very slow to open and save, through out the next day of working in SCSM. The day after that, all was working fine again, but I'm curious to what could be the reason.

    So I used the program at about 10pm one evening, when I installed ARO. The next day, many analysts reported, that the console and portal was running very slow, when working on work items. Do you have any idea why? Is there some index which needs to be rebuild because of this or something?

    Anyways, thanks for a great tool!
  • Brian_WiestBrian_Wiest Customer Super IT Monkey ✭✭✭✭✭
    Been running the tool a few times. And I don't have that issue but I follow a standard practice of clearing the health state anytime I did a system change like this. 
  • Brian_WiestBrian_Wiest Customer Super IT Monkey ✭✭✭✭✭
    BTW Love the tool. We also updated it to include other work item classes. 
  • Konstantin_Slavin-BoKonstantin_Slavin-Bo Customer Advanced IT Monkey ✭✭✭
    @Brian_Wiest
    Thanks for your reply! By "clearing the health state" you mean deleting everything in the ..\Service Manager\Healh Service State\ dir?
  • Brian_WiestBrian_Wiest Customer Super IT Monkey ✭✭✭✭✭
    Correct
    Our process is to

    Stop all runbooks
    Stop the Cireson Cache builder
    Stop on all management server - "System Center Data Access Service","System Center Management Configuration","Microsoft Monitoring Agent"
    Truncate Last Modified on the ServiceManagement dbo
    Delete the \SCSM2012\Health Service State\*
    Start the management server services
    Start the Cireson cache builder
    Start the monitoring runbooks


  • Konstantin_Slavin-BoKonstantin_Slavin-Bo Customer Advanced IT Monkey ✭✭✭
    @Brian_Wiest
    That seems like a good procedure. We follow the same one, apart from deleting the health state, which is probably what makes the difference. I will test it out soon, as I need to extended our IR class with an additional property, and I plan on using this tool in the process.

    Thanks a lot!
  • Conner_WoodConner_Wood Customer Advanced IT Monkey ✭✭✭
    @Brian_Wiest ;@Konstantin_Slavin-Bo

    No problem, I'm glad you found one of the programs I've released on TechNet useful.

  • Brian_WiestBrian_Wiest Customer Super IT Monkey ✭✭✭✭✭
    @Conner_Wood it works so well that Cireson should add it to their KB now how to install the ARO Management Pack :smile:
  • Geoff_RossGeoff_Ross Cireson Consultant Super IT Monkey ✭✭✭✭✭
    @Conner_Wood
    Used this for the first time with a customer, worked a treat, very nice work sir!
  • Konstantin_Slavin-BoKonstantin_Slavin-Bo Customer Advanced IT Monkey ✭✭✭
    @Geoff_Ross
    Did the customer experience any significant slowdown in console / portal operations, specifically when working on WI afterwards? Did you follow a procedure similar to @Brian_Wiest's regarding deletion of the Health Service State\ directory?
  • Geoff_RossGeoff_Ross Cireson Consultant Super IT Monkey ✭✭✭✭✭
    @Konstantin_Slavin-Bo
    Nope, no slow down, everything worked well. And no, just exported the dates, imported the MP and imported the dates. All Good.
  • Konstantin_Slavin-BoKonstantin_Slavin-Bo Customer Advanced IT Monkey ✭✭✭
    @Geoff_Ross
    Hm, interesting. But thanks!
  • Brian_WiestBrian_Wiest Customer Super IT Monkey ✭✭✭✭✭
    @Konstantin_Slavin-Bo It might be also dependent on the number of work items in the active database. 
  • Martyn_FearnMartyn_Fearn Customer IT Monkey ✭
    @Konstantin_Slavin-Bo
    We experienced significant performance degradation after using this.  We put this down to the number of WI we have in the operational db (just short of 90k at the time).  It took about 2 days for performance to return to normal.

    In the course of trying to understand what was causing the issue we queried the db and could see a large number of workflows that were all running in excess of 600 minutes behind. 

    The tool itself worked perfectly, we'll just have to consider the impact to performance should we ever have to use it again.
  • Konstantin_Slavin-BoKonstantin_Slavin-Bo Customer Advanced IT Monkey ✭✭✭
    @Martyn_Fearn
    Ah okay, that explains it, as the workflows will have "rerun" on the updated WI, which appear not to be updated, which probably confuses SCSM. Thanks!

    Yeah, it's definitely an aspect to consider for the future.
  • Brian_WiestBrian_Wiest Customer Super IT Monkey ✭✭✭✭✭
    ALCON 
    When we utilize this tool one thing we do is shut down the DW as documented but also shut down the Cireson Cache builder along with the other management servers. Means only our primary management server is running. (the other 4 are shut down) 
    We have ~40K WI at a given time and once we complete the outage period we do not have performance issues.
  • Conner_WoodConner_Wood Customer Advanced IT Monkey ✭✭✭
    edited June 2017
    I suppose you could turn off the Primary Workflow Management Server services to prevent workflows from being run (though I assume they'll still be accumulating and queued)
    • Microsoft Monitoring Agent
    • System Center Data Access Service
    • System Center Management Configuration

    Then follow the instructions as usual but on a secondary server:
    1. Use the tool to save dates, close tool
    2. Then on a secondary SCSM Management Server import the Cireson ARO MPB
    3. Then use tool, load dates from file, Overrwrite SCSM SQL Database From Loaded DataTable

    Then you restart all the Management Servers including Primary Workflow Management Server.
  • Konstantin_Slavin-BoKonstantin_Slavin-Bo Customer Advanced IT Monkey ✭✭✭
    We also stopped all services related to SCSM on all other servers, so only the primary MS was running, and still experienced a significant slowdown the whole next working day. And the tool is reporting only 20k rows when loading the DataTable, but we only have a single secondary MS, so the workload is probably higher on each server.
  • Brian_WiestBrian_Wiest Customer Super IT Monkey ✭✭✭✭✭
    edited June 2017
    @Konstantin_Slavin-Bo Only your first installed Management server is the workload server. The second would be a console connection server. Have you made sure to separate out the server roles?
    Assuming your SCSM 2012 R2
  • Konstantin_Slavin-BoKonstantin_Slavin-Bo Customer Advanced IT Monkey ✭✭✭
    @Brian_Wiest
    Ah yes, of course. Yeah, the roles are separated. The secondary server is the Cireson Portal web server running a MS to enable IWA.
Sign In or Register to comment.