locked
¿How to add Service Request Source List Values from SQL, Powershell or Csv? RRS feed

  • Question

  • Hi.

    I want to add Service Request - Source List  Values from SQL, a csv file or powershell.

    ¿Is it possible?

    ¿How is it easier? ¿script? ¿Wich is the table that contains this values?

    Regards.


    Tuesday, January 13, 2015 2:32 PM

Answers

  • good idea! :D,  i've created this script and work for me :) ......this add the values to the enum from a csv file

    import-module Smlets
    $mpname = 'MP_Personalizaciones_Solicitudes'

    ##OBTIENE EL MANAGEMENT PACK PADRE
    $mp = Get-SCSMManagementPack | ?{$_.FriendlyName -eq $mpname}

    ##OBTIENE LA LISTA SOBRE LA QUE SE DESEA AGRAGAR LOS VALORES
    $parentEnum = Get-SCSMEnumeration ServiceRequestSourceEnum

    #Get-SCSMEnumeration $mp | ?{$_.Name -match 'ServiceRequestSource'} | Get-SCSMChildEnumeration | Sort-Object Ordinal | select -last 2

    ##VARIABLE QUE AUMENTA EN 5 CADA VEZ QUE SE AGREGA UN VALOR Y SE ASIGNA AL ORDINAL DE LA LISTA
    $count = (($parentEnum | Get-SCSMChildEnumeration).Ordinal | Measure-Object -Maximum).Maximum
    $csvfolder = "E:\test"
    $csvdata = Import-Csv $($csvfolder + "\SolicitudesSource.csv") -Encoding UTF8

    if ($csvdata) {
    $tot = $csvdata.Count
    $actual = 0
        foreach($record in $csvdata.DisplayName){
            $actual++
            $perc = $actual * 100 / $tot
            Write-Progress -Activity 'Adding Enumeration' -Status "Adding Child $record to parent
                $($parentEnum | ?{$_.Ordinal -eq $null}) - ($actual of $Tot)" -Percentcomplete $perc
            $count++
            #$record = $csvdata.DisplayName
            $enumName = $record -replace [regex]::Escape(' '),'' `
                                -replace [regex]::Escape('-'),'' `
                                -replace [regex]::Escape('á'),'a' `
                                -replace [regex]::Escape('é'),'e' `
                                -replace [regex]::Escape('í'),'i' `
                                -replace [regex]::Escape('ó'),'o' `
                                -replace [regex]::Escape('ú'),'u' `
                                -replace [regex]::Escape('ý'),'y' `
                                -replace [regex]::Escape('ñ'),'n'
            $name = "ServiceRequestSourceEnum." + $enumName
            ##AQUI SE AGREGA CADA UNO DE LOS VALORES DEL PLANO A LA LISTA 
            Add-SCSMEnumeration -Parent ($parentEnum | ?{$_.Ordinal -eq $null}) -ManagementPack $mp `
                -DisplayName $record -Ordinal $count -Name $name
            #($parentEnum[$parentEnum.Length-1] | Get-SCSMChildEnumeration).GetXML()
            #$parentEnum.GetManagementPack()
            $mp.AcceptChanges()
        }
    }
    Thursday, January 15, 2015 4:13 PM

All replies

  • I am not sure if I understand your requierement right. But are you looking for something like this?

    Service Manager – Create List Items using Enum Builder


    Andreas Baumgarten | H&D International Group

    • Marked as answer by DANIELVIVAS92 Tuesday, January 13, 2015 8:57 PM
    • Unmarked as answer by DANIELVIVAS92 Tuesday, January 13, 2015 10:41 PM
    Tuesday, January 13, 2015 5:58 PM
  • You can add to the SR source list using enum builder as Andreas suggest, or using PowerShell (the builtin cmdlets or SMLets). You can add from whatever you like, but do not edit the SQL db directly unless you really know what you are doing.

    In SMLets command is called Add-SCSMEnumeration. Do a Get-Help Add-SCSMEnumeration and go from there :D


    Cheers,

    Anders Spælling

    Senior Consultant

    Blog:   Twitter:   LinkedIn:   

    Please remember to 'Propose as answer' if you find a reply helpful

    • Marked as answer by DANIELVIVAS92 Tuesday, January 13, 2015 8:57 PM
    • Unmarked as answer by DANIELVIVAS92 Tuesday, January 13, 2015 10:41 PM
    Tuesday, January 13, 2015 8:28 PM
  • Hey. I'm trying the EnumBuilder but not work for me.

    I have generated the following xml for add values to the SOURCE Service Request Enum :


    <ManagementPack ContentReadable="true" SchemaVersion="1.1" OriginalSchemaVersion="1.1">
      <Manifest>
        <Identity>
          <ID>SR.SOURCE.TEST</ID>
          <Version>1.0.0.0</Version>
        </Identity>
        <Name>SRSOURCETEST</Name>
        <References>
          <Reference Alias="ServiceManager.ServiceRequest.Library">
            <ID>ServiceManager.ServiceRequest.Library</ID>
            <Version>7.5.2905.0</Version>
            <PublicKeyToken>31bf3856ad364e35</PublicKeyToken>
          </Reference>
        </References>
      </Manifest>
      <TypeDefinitions>
        <EntityTypes>
          <EnumerationTypes>
            <EnumerationValue ID="ServiceRequestSourceEnum.UNO.DOS.TRES.Enum" Accessibility="Public" Ordinal="0" Parent="" />
            <EnumerationValue ID="ServiceRequestSourceEnum.UNOx.DOSx.Enum" Accessibility="Public" Ordinal="1" Parent="" />
          </EnumerationTypes>
        </EntityTypes>
      </TypeDefinitions>
      <LanguagePacks>
        <LanguagePack ID="ENU" IsDefault="true">
          <DisplayStrings>
            <DisplayString ElementID="SR.SOURCE.TEST">
              <Name>SRSOURCETEST</Name>
            </DisplayString>
            <DisplayString ElementID="ServiceRequestSourceEnum.UNO.DOS.TRES.Enum">
              <Name>TRES</Name>
            </DisplayString>
            <DisplayString ElementID="ServiceRequestSourceEnum.UNOx.DOSx.Enum">
              <Name>DOSx</Name>
            </DisplayString>
          </DisplayStrings>
        </LanguagePack>
      </LanguagePacks>
    </ManagementPack>



    When i try to import the MP it says:

    : XSD verification failed for the management pack. [Line: 19, Position: 110]
    The 'Parent' attribute is invalid - The value '' is invalid according to its datatype 'ManagementPackIdentifierReference' - The Pattern constraint failed.The Pattern constraint failed.

    If i delete Parent="" of items, i can import the mp succesfully but i can't see the values.

    any suggestion? Please help me...

    Regards


    Tuesday, January 13, 2015 10:32 PM
  • The enum builder do'nt work for me. Thnks.
    Thursday, January 15, 2015 4:01 PM
  • good idea! :D,  i've created this script and work for me :) ......this add the values to the enum from a csv file

    import-module Smlets
    $mpname = 'MP_Personalizaciones_Solicitudes'

    ##OBTIENE EL MANAGEMENT PACK PADRE
    $mp = Get-SCSMManagementPack | ?{$_.FriendlyName -eq $mpname}

    ##OBTIENE LA LISTA SOBRE LA QUE SE DESEA AGRAGAR LOS VALORES
    $parentEnum = Get-SCSMEnumeration ServiceRequestSourceEnum

    #Get-SCSMEnumeration $mp | ?{$_.Name -match 'ServiceRequestSource'} | Get-SCSMChildEnumeration | Sort-Object Ordinal | select -last 2

    ##VARIABLE QUE AUMENTA EN 5 CADA VEZ QUE SE AGREGA UN VALOR Y SE ASIGNA AL ORDINAL DE LA LISTA
    $count = (($parentEnum | Get-SCSMChildEnumeration).Ordinal | Measure-Object -Maximum).Maximum
    $csvfolder = "E:\test"
    $csvdata = Import-Csv $($csvfolder + "\SolicitudesSource.csv") -Encoding UTF8

    if ($csvdata) {
    $tot = $csvdata.Count
    $actual = 0
        foreach($record in $csvdata.DisplayName){
            $actual++
            $perc = $actual * 100 / $tot
            Write-Progress -Activity 'Adding Enumeration' -Status "Adding Child $record to parent
                $($parentEnum | ?{$_.Ordinal -eq $null}) - ($actual of $Tot)" -Percentcomplete $perc
            $count++
            #$record = $csvdata.DisplayName
            $enumName = $record -replace [regex]::Escape(' '),'' `
                                -replace [regex]::Escape('-'),'' `
                                -replace [regex]::Escape('á'),'a' `
                                -replace [regex]::Escape('é'),'e' `
                                -replace [regex]::Escape('í'),'i' `
                                -replace [regex]::Escape('ó'),'o' `
                                -replace [regex]::Escape('ú'),'u' `
                                -replace [regex]::Escape('ý'),'y' `
                                -replace [regex]::Escape('ñ'),'n'
            $name = "ServiceRequestSourceEnum." + $enumName
            ##AQUI SE AGREGA CADA UNO DE LOS VALORES DEL PLANO A LA LISTA 
            Add-SCSMEnumeration -Parent ($parentEnum | ?{$_.Ordinal -eq $null}) -ManagementPack $mp `
                -DisplayName $record -Ordinal $count -Name $name
            #($parentEnum[$parentEnum.Length-1] | Get-SCSMChildEnumeration).GetXML()
            #$parentEnum.GetManagementPack()
            $mp.AcceptChanges()
        }
    }
    Thursday, January 15, 2015 4:13 PM