What is the difference between pivot based on search and event?
Second: When I create a pivot based on search and the field name extraction is auto, if the field name have spaces, I have to rename them in the base search before I do extraction. Only then I can use them in column or row. If I rename them after auto extraction it doesn't work.
And If I create the object from an event, and if the field has spaces, it doesn't work if I rename after auto extraction.
So my question is - the Field Name - Rename after auto extraction doesn't work for both search and event based objects? Has anyone come across this?
The differences between event-based data model objects and search-based data model objects are defined in the documentation here (for a high-level overview) and here (for a more detailed discussion).
Essentially, root event objects are defined by "constraints" (simple searches, no "|" characters or complicated search commands), while root search objects are defined by full searches that can involve any kind of complex search construction. All child objects are defined by constraints, on top of whatever they've inherited from their parent objects.
In general, you should try to use event-based objects when possible, in large part because they can benefit from search acceleration while search base objects cannot. For more information read this.
As for field names (or in this case attribute names--fields are called "attributes" when used in data models), auto-extracted attribute names should never have spaces. The only characters recognized by Splunk for field names are a-z, A-Z, 0-9, or _. If you've configured field extractions in props.conf or transforms.conf where the field names include spaces, that will cause problems down the line. Usually a process called "key cleaning" corrects this by putting underscores in where spaces exist, but you may have it disabled for these particular fields for some reason. For details on field name syntax see this documentation.
Once you've got the fields extracted correctly with underscores instead of spaces you can rename them however you want in the Data Model editor or Pivot editor. For example, if you have an auto-extracted attribute named Emp_ID you can rename it as Emp ID in the attribute defintion. But you shouldn't have an auto-extracted attribute that is initially named Emp ID -- renaming it to Emp_ID in the attribute defintion won't help it to work in the Data Model Editor.