Posts Tagged ‘CSV’

With my previous post about importing data, I showed you how to use the built-in CSV connector to do this task and how to create the required Projection Classes. However if you are looking for a quicker more automated to achieve this, enter Cireson and their Asset Import Tool. It takes all the guess work out of the import process and handles the Projection Classes for you.

The installation of the application is easy and requires the copying a DLL file to the Service Manager Installation folder and the import of a Management Pack ( a management Pack Bundle in this case). The instructions provided with the application are very clear and concise.

As usual with the Cireson applications, the settings for the applications can be found and tweaked in the Administration | Settings. This time, this is where the License Key will need to be entered
image

So now, onto the “meat”. Let’s actually use the application and see how it works. The application works pretty much the same way as the CSV Connector. It is classified as a connector, and as such to use it, a new connector would need to be created. Connectors are found under Administration | Connectors

image

If you have any Projection or Combinations classes you would like to use the application does allow the use of this.image

For this demo, I have used the Windows Computer Class, additional information around the Projection classes can be found here, this will allow to see the information (headers) used by the required class. The Windows Computer class has the following information.

Name Type MinLength MaxLength AutoIncrement

PrincipalName

string

0

256

FALSE

DNSName

string

0

256

FALSE

NetbiosComputerName

string

0

256

FALSE

NetbiosDomainName

string

0

256

FALSE

IPAddress

string

0

256

FALSE

NetworkName

string

0

256

FALSE

ActiveDirectoryObjectSid

string

0

256

FALSE

IsVirtualMachine

string

0

256

FALSE

DomainDnsName

string

0

256

FALSE

OrganizationalUnit

string

0

256

FALSE

ForestDnsName

string

0

256

FALSE

ActiveDirectorySite

string

0

256

FALSE

OffsetInMinuteFromGreenwichTime

int (range: [-2147483648, 2147483647])

0

256

FALSE

LastInventoryDate

datetime

0

256

FALSE

ObjectStatus

enum:System.

ConfigItem.ObjectStatusEnum

0

256

FALSE

AssetStatus

enum:System.

ConfigItem.AssetStatusEnum

0

256

FALSE

Notes

richtext

0 4000

FALSE

DisplayName

string

0

256

FALSE

So, for this, I will bulk import a few computers with only a few simple parameters.

I am simply adding two machines

 image

The Asset Import Tool also allows you to test your configuration first.

image

Now we can map the Headers to the Projections, you will see I kept them the same, however if you are getting a file from the IT Department or any other department, this step here allows to map these correctly.image

Next, you can set your frequency, allowing you to automate the import. I have configured mine to run Every Hour.

image

Then you can create the connector.

Final Connector completed.

image

Now lets run it!!!!

image

Since we enabled the option to “Test”, the results were written to a log file allowing us to test before running in a production environment.

image

All looks good, let’s run it to bring in the data. A simple change needs to be made. We just need to remove a checkbox.

image

Ok, change made. let’s import the data. So now, let’s re-run the import process.The Connector has now re-run, now we can check the data. This time without the testing.

image

Now, we can see the new imported data. We can check here under Configuration Items | Computers | All Windows Computers

image

And there we have it, importing nice and easy.

(E-Mail me)

Follow me.

Facebook (Personal)

Twitter (Personal & System Center)

 Twitter (System Center Focused)

Advertisements

So, just recently I was asked by a customer to import data from an “old” SCSM system. The customer is moving to new hardware within the organization and is using this opportunity to clean up Service Manager and re-import only clean data.

So, the challenge is the following.

The customer wants to import Change Requests from the “old” Service Manager to the “new” Service Manager. Ok, so after some research and confirmation, it is NOT possible to simply move data from one Service Manager installation to another. I decided the best way to handle this would be to import the data using a CSV file. As a start I used the following links

http://www.netiviaconsulting.com/2012/05/04/importing-work-items-in-bulk-into-scsm2012-via-csv-import-connector/

http://blogs.technet.com/b/servicemanager/archive/2009/05/26/using-the-csv-import-feature.aspx

So, now onto the “meat” of the work, it is entirely up to you what data you would like to import.

Below is a sample of my XML file. It caters for the basic information on the Change Request form and for my customers needs, it caters for SIX Manual Activities and ONE Review Activity. I will break this down into smaller pieces in a moment.

The Appendix “A” here is key to the import process.

XML Code

<CSVImportFormat>
<Projection Type="System.WorkItem.ChangeRequestProjection">
<Seed>
<Class Type="System.WorkItem.ChangeRequest">
<Property ID="Id"/>
<Property ID="Notes"/>
<Property ID="Title"/>
<Property ID="Description"/>
<Property ID="ContactMethod"/>
<Property ID="Priority"/>
<Property ID="Impact"/>
<Property ID="Risk"/>
<Property ID="Reason"/>
</Class>
</Seed>
<Component Alias="CreatedBy">
<Seed>
<Class Type="System.Domain.User">
<Property ID="Domain"/>
<Property ID="UserName"/>
</Class>
</Seed>
</Component>
<Component Alias="Activity">
<Seed>
<Class Type="System.WorkItem.Activity.ManualActivity">
<Property ID="Id"/>
<Property ID="SequenceId"/>
<Property ID="Title"/>
</Class>
</Seed>
<Component Alias="ActivityAssignedTo">
<Seed>
<Class Type="System.Domain.User">
<Property ID="Domain"/>
<Property ID="UserName"/>
</Class>
</Seed>
</Component>
</Component>
<Component Alias="Activity">
<Seed>
<Class Type="System.WorkItem.Activity.ManualActivity">
<Property ID="Id"/>
<Property ID="SequenceId"/>
<Property ID="Title"/>
</Class>
</Seed>
<Component Alias="ActivityAssignedTo">
<Seed>
<Class Type="System.Domain.User">
<Property ID="Domain"/>
<Property ID="UserName"/>
</Class>
</Seed>
</Component>
</Component>
<Component Alias="Activity">
<Seed>
<Class Type="System.WorkItem.Activity.ManualActivity">
<Property ID="Id"/>
<Property ID="SequenceId"/>
<Property ID="Title"/>
</Class>
</Seed>
<Component Alias="ActivityAssignedTo">
<Seed>
<Class Type="System.Domain.User">
<Property ID="Domain"/>
<Property ID="UserName"/>
</Class>
</Seed>
</Component>
</Component>
<Component Alias="Activity">
<Seed>
<Class Type="System.WorkItem.Activity.ManualActivity">
<Property ID="Id"/>
<Property ID="SequenceId"/>
<Property ID="Title"/>
</Class>
</Seed>
<Component Alias="ActivityAssignedTo">
<Seed>
<Class Type="System.Domain.User">
<Property ID="Domain"/>
<Property ID="UserName"/>
</Class>
</Seed>
</Component>
</Component>
<Component Alias="Activity">
<Seed>
<Class Type="System.WorkItem.Activity.ManualActivity">
<Property ID="Id"/>
<Property ID="SequenceId"/>
<Property ID="Title"/>
</Class>
</Seed>
<Component Alias="ActivityAssignedTo">
<Seed>
<Class Type="System.Domain.User">
<Property ID="Domain"/>
<Property ID="UserName"/>
</Class>
</Seed>
</Component>
</Component>
<Component Alias="Activity">
<Seed>
<Class Type="System.WorkItem.Activity.ManualActivity">
<Property ID="Id"/>
<Property ID="SequenceId"/>
<Property ID="Title"/>
</Class>
</Seed>
<Component Alias="ActivityAssignedTo">
<Seed>
<Class Type="System.Domain.User">
<Property ID="Domain"/>
<Property ID="UserName"/>
</Class>
</Seed>
</Component>
</Component>
<Component Alias="Activity">
<Seed>
<Class Type="System.WorkItem.Activity.ManualActivity">
<Property ID="Id"/>
<Property ID="SequenceId"/>
<Property ID="Title"/>
</Class>
</Seed>
<Component Alias="ActivityAssignedTo">
<Seed>
<Class Type="System.Domain.User">
<Property ID="Domain"/>
<Property ID="UserName"/>
</Class>
</Seed>
</Component>
</Component>
<Component Alias="Activity">
<Seed>
<Class Type="System.WorkItem.Activity.ReviewActivity">
<Property ID="Id"/>
<Property ID="SequenceId"/>
<Property ID="Title"/>
</Class>
</Seed>
<Component Alias="ActivityAssignedTo">
<Seed>
<Class Type="System.Domain.User">
<Property ID="Domain"/>
<Property ID="UserName"/>
</Class>
</Seed>
</Component>
</Component>
</Projection>
</CSVImportFormat>
<code>


Okay, so now time to break it down into smaller easier to manage bits.

Base Change Request Projection Class. This is the framework for Change Request and this example included the “Created By” extension. All the property id’s can be found in the appendix here. Please note that the example below does NOT include the “Projection” closing XML Brace as this is included further down with the original coding.

XML Code

</pre>
<code></code>

<Projection Type="System.WorkItem.ChangeRequestProjection">
<Seed>
<Class Type="System.WorkItem.ChangeRequest">
<Property ID="Id"/>
<Property ID="Notes"/>
<Property ID="Title"/>
<Property ID="Description"/>
<Property ID="ContactMethod"/>
<Property ID="Priority"/>
<Property ID="Impact"/>
<Property ID="Risk"/>
<Property ID="Reason"/>
</Class>
</Seed>
<Component Alias="CreatedBy">
<Seed>
<Class Type="System.Domain.User">
<Property ID="Domain"/>
<Property ID="UserName"/>
</Class>
</Seed>
</Component>

<code>

So, now after this I have simply “bolted” on as many Manual Activities as I needed for my customer. In this case, it is SEVEN. I have a sample of the XML code below which can be simply copied and pasted as many times as needed. Please note that the example below does NOT include the “Projection” closing XML Brace as this is included further down with the original coding.

XML Code

</pre>
<code></code>

<Component Alias="Activity">
<Seed>
<Class Type="System.WorkItem.Activity.ManualActivity">
<Property ID="Id"/>
<Property ID="SequenceId"/>
<Property ID="Title"/>
</Class>
</Seed>
<Component Alias="ActivityAssignedTo">
<Seed>
<Class Type="System.Domain.User">
<Property ID="Domain"/>
<Property ID="UserName"/>
</Class>
</Seed>
</Component>
</Component>

<code>

And now I needed to add a Review Activity, again this is just the simple information needed.

XML Code

</pre>
<code></code>

<Component Alias="Activity">
<Seed>
<Class Type="System.WorkItem.Activity.ReviewActivity">
<Property ID="Id"/>
<Property ID="SequenceId"/>
<Property ID="Title"/>
</Class>
</Seed>
<Component Alias="ActivityAssignedTo">
<Seed>
<Class Type="System.Domain.User">
<Property ID="Domain"/>
<Property ID="UserName"/>
</Class>
</Seed>
</Component>
</Component>

<code>

So, all the components together created the XML file for the Projection that I needed.

Ok, so this is ONE piece of the puzzle. In order for the CSV import to work within Service Manager, you need a CSV file as well. Building the CSV file is actually quite easy. Simple use all

   <Property ID =”..”> <code>


tags as CSV headers.

Populate the file as needed. Now lets import the file. By the way, I have noticed that if the file is created in Excel (2013 at least) and saved a .csv, Excle uses “;” and NOT “,”. So simple open the application in Notepad and do a search and replace.

Sample file (CSV)

Headers are based on Property values within the XML File.

CR{0},CR notes,CR Title 1,CR Description 1,CR Contact Method,ChangePriorityEnum.Medium,ChangeImpactEnum.Standard,ChangeRiskEnum.Medium,CR Reason 1,FK,fletcherk,MA{0},0,MA Title 1,fk,fletcherk,MA{0},1,MA Title 2,fk,fletcherk,MA{0},2,MA Title 3,fk,fletcherk,MA{0},3,MA Title 4,fk,fletcherk,MA{0},4,MA Title 5,fk,fletcherk,MA{0},5,MA Title 6,fk,fletcherk,MA{0},6,MA Title 7,fk,fletcherk,RA{0},7,RA Title 1,fk,fletcherk

Please note that the Sequence ID can be changed to sort you needs. Remember that the Sequence Number will start at 0. The MA is the prefix for the Manual Activities and the RA is the prefix for Review Activities, please adjust as needed.

Also, the {0} ensures id are incremented correctly.

image pl

Select XML file and CSV file

image

image

image

image

You can import as many Change Requests as you wish using this method.

(E-Mail me)

image_thumb_thumb_thumb_thumb_thumb

Follow me.

facebook-small322252222 Facebook (Personal)

twitter-small322252222Twitter (Personal & System Center)

scsmlogo25232 Twitter (System Center Focused)

MCC11_Logo_Horizontal_2-color_thumb_

So, just last night, we had an issue with our cluster.

One of our disks would not come online. We were seeing an error event ID: 1304 as below.

error1304

So, we began to investigate as all the Technet articles state that the drive is corrupted and Disk Signatures need to be changed, however we have had no major changes in the last little while. We were also able to view to CSV Volume from our SAN management tools. So, we were confused. The disk just would not come online.

1 . The Quorum was online, or else the cluster would not come online.

2. All the nodes were online.

3. All Networks were online

4. The cluster virtual name and IP was up.

However, only CSV would not come online. So, we began to dig a little bit deeper and discovered that the error message was a little misleading, it did however contain some useful information. From the error message we were able to glean the problem node (CSV Owner). We then hopped onto the problem node and after a little digging around, discovered that the volume had been set to OFFLINE. We set the volume to ONLINE and then tried to bring the resource ONLINE in Failover Cluster Manager and hey PRESTO!!! The volume can online and so did my VMs.

I have since created a PowerShell script which can be found here to help the troubleshooting process. All you need to do is provide the list of cluster/s to be checked.  It does the following.

1. Check if the nodes state is in an “UP” state. If the node is in an up state, it then checks the status of the Cluster Service and reports if it is up or down.

2. It then checks the CSV state and specifies the current owner of the CSV and then lets you know if the CSV online or reserved. This is good.

3. Should it get a state other than this, it then proceeds to run a list disk from diskpart on the reported CSV owners to help you find which CSV and which owner to start working on.

Follow me.

facebook-small3222522twitter-small3222522

MCC11_Logo_Horizontal_2-color_thumb_

 

So, now you are at the point where you need to present a CSV Volume to your cluster.

I have created this guide to make your life a little easier. Doing this is actually quite simple, but there are a few gotchas.

Gotcha 1 : If you create / carve the LUN and forget to allow multiple access, only the first IQN / WWN will be able to access it.

Gotcha 2 : If you present the disk to the “Storage” (highlighted below) portion of your cluster and do NOT create a simple volume, you will not be able to do this from Disk Management as the Cluster will have a lock on it. Simply delete the volume from the “Storage” and then create the simple volume.

image

Okay, so getting past that.

Step 1: Create / Carve the LUN from your Storage.

Step 2: Remember to allow multiple access to the volume.

Step 3: Present the Storage to all the Nodes in the Cluster.

Step 4: Bring the disk online

Step 5: Initialize the disk

Step 6: Create a simple volume, there is NO need to assign a drive letter.

Step 7: Open Failover Cluster Manager and Select “Storage” and Click “Add a disk”

image

Step 8: Add the Disk, you should see the newly presented disk, select the disk and click OK. The disk will show up as online under “Available Storage”. You can rename the disk according by double clicking the disk and changing the name in “Resource Name”

image

image

Step 9: Now it is time to make it a “CSV”, Click “Cluster Shared Volumes” and click “Add Storage”

image

Step 10: Select the Disk you want to add and click “OK”

image

Disk is now online and a CSV.

Follow me

facebook-small322252222 twitter-small322252222

MCC11_Logo_Horizontal_2-color_thumb_[2]