Why am I getting "Error in PivotCell" using NOT operators to apply a filter with pivots and drop-downs in a dynamic dashboard?

[scottb61]

Hi All,

I'm looking to create a dynamic dashboard where the search results get updated by way of a Drop-down selector that changes the parameter of the hostname field within all panels.

I've managed to create this setup and I'm in the process of tweaking it a little to be more specific.

However, i found an issue with NOT Operators and Pivots that is giving me problems and which seems to work fine with search.

Essentially, the drop-down selector will allow a user to select a "site". The site selected changes the search to search for hosts beginning with the "unique" ID for that site. For example Li* for Liverpool or Lo* for London.

This works really well, however on one site, they aren't as unique as they should be so, as an example, Li* is also picking up Livermore which i want to exclude.

Now i know in the example above i could just do Liverp* but this is an example and its a bit more complicated than that in real life.

Essentially what i wish to do is

Li* NOT Liverm*

So that i see everything beginning with Li* except Liverm*

In my drop-down selector, under static options, i specify Liverpool as Li* NOT Liverm* and make the token SitetoGet

Within the search i have:

sourcetype=syslog host=$SitetoGet$

And this works very well. The problem comes with Pivots when i apply a FILTER

If i run a picot and filter it as follows:-

FILTER host is $SitetoGet$

I get

Error in PivotCell the Object 'Example_Object' has no field 'NOT'.

Any suggestions as to how i can get around this issue? i have also tried the ! operator with no success.

Thanks

[jeffland]

Just a hunch, but could you maybe use event types or macros? I'm thinking of something like eventtype=Liverpool in your search that gets handed to the pivot, with the definition of that event type as index = foo | Li* NOT Liverm*. While this is quite static (you'll have to create an event type for every search you need), it might already do the trick. Macros would give you even more options to use existing inputs or fields.

In any case, I haven't tried whether they work with pivot, so it's up to you to find out. I'd be interested to see if it works (I imagine it only works if the actual search behind the event type or maco is not replaced before the search is handed to your pivot).

For reference, see event types and macros.

[jeffland]

Yeah, apparently event types are not exactly the right way to go, macros are what you want to use - see here for example.

[scottb61]

Sorry - that wont work either - all a Macro appears to do is exactly the same as the search except it makes it easier to reference. As a result I get the same error message as if I type it out fully, that is:-

Error in PivotCell the Object 'Example_Object' has no field 'NOT'.

For example, if my Macro, Liverpool, is:-

FILTER host is Li* NOT Liverm*

and my Pivot is:-

| pivot Cisco_IOS_Event Device count(Device) AS "Count of events" SPLITROW host AS host Liverpool TOP 100 count(Device)

I get the above error, if I change the Macro to just

FILTER host is Li*

It works fine but its not what I need as I need to get the NOT operator in there as well to exclude Liverm*

[jeffland]

That's a shame. Well then I suppose you need to edit your data model that powers your pivot, as described here.

[scottb61]

Hi,
Thanks for the answer, if i use the following example search

| pivot Cisco_IOS_Event Device count(Device) AS "Count of events" SPLITROW host AS host FILTER eventtype=Liverpool TOP 100 count(Device)

Eventtype Liverpool is as follows:-

host=Li* NOT Liverm*

I get

Error in PivotUtil: The Object Device has no field eventtype=Liverpool

So it seems to treat the Eventtype as a Field as opposed to a search unfortunately.