Wildcard field used in table, return only fields with selected values.

[JDukeSplunk]

We're pulling in a JSON from an API call. I'd like to setup an alert that only shows when field state is NOT active. The problem is that there is a stupid number of fields created by the input and they change names (but always end in *state). So I can't just list them in the table.
Here's the search I muddled out. It works, the alert fires, and the renames lop off some of the noise. However it's not ideal.

index=application sourcetype=json_no_timestamp DOWN OR RECOVERING OR RECOVERY_FAILED 
| rename cluster.collections.* as *
| rename *shard1.replicas.core_node* as *node*
| rename *shards.* as **
| table host *state

It returns this. (This is an small slice of the screen). It would make the alert less cluttered if "active" nodes didn't show. It feels like this should be a subsearch / where, but I can't make that work with the wildcard *state.

Here's an example of the _raw if you would like it.

{"responseHeader":{"status":0,"QTime":4},"cluster":{"collections":{"CPTCodes":{"replicationFactor":"1","shards":{"shard1":{"range":"80000000-7fffffff","state":"active","replicas":{"core_node1":{"core":"CPTCodes_shard1_replica1","base_url":"http://ATLAPDSOLR02:9093/solr","node_name":"ATLAPDSOLR02:9093_solr","state":"down"},"core_node2":{"core":"CPTCodes_shard1_replica2","base_url":"http://ATLAPDSOLR02:8983/solr","node_name":"ATLAPDSOLR02:8983_solr","state":"active","leader":"true"}}}},"router":{"name":"compositeId"},"maxShardsPerNode":"1","autoAddReplicas":"false","znodeVersion":197,"configName":"CPTCodes"},"Q4LParticipants":{"replicationFactor":"1","shards":{"shard1":{"range":"80000000-7fffffff","state":"active","replicas":{"core_node1":{"core":"Q4LParticipants_shard1_replica1","base_url":"http://ATLAPDSOLR02:9093/solr","node_name":"ATLAPDSOLR02:9093_solr","state":"down"},"core_node2":{"core":"Q4LParticipants_shard1_replica2","base_url":"http://ATLAPDSOLR02:8983/solr","node_name":"ATLAPDSOLR02:8983_solr","state":"active","leader":"true"}}}},"router":{"name":"compositeId"},"maxShardsPerNode":"1","autoAddReplicas":"false","znodeVersion":568,"configName":"Q4LParticipants"},"nextgen":{"replicationFactor":"1","shards":{"shard1":{"range":"80000000-7fffffff","state":"active","replicas":{"core_node1":{"core":"nextgen_shard1_replica1","base_url":"http://ATLAPDSOLR02:8983/solr","node_name":"ATLAPDSOLR02:8983_solr","state":"active","leader":"true"},"core_node2":{"core":"nextgen_shard1_replica2","base_url":"http://ATLAPDSOLR02:9093/solr","node_name":"ATLAPDSOLR02:9093_solr","state":"down"}}}},"router":{"name":"compositeId"},"maxShardsPerNode":"1","autoAddReplicas":"false","znodeVersion":479,"configName":"nextgen"},"NamesDictionary":{"replicationFactor":"1","shards":{"shard1":{"range":"80000000-7fffffff","state":"active","replicas":{"core_node2":{"core":"NamesDictionary_shard1_replica2","base_url":"http://ATLAPDSOLR02:8983/solr","node_name":"ATLAPDSOLR02:8983_solr","state":"active","leader":"true"},"core_node3":{"core":"NamesDictionary_shard1_replica1","base_url":"http://ATLAPDSOLR02:9093/solr","node_name":"ATLAPDSOLR02:9093_solr","state":"down"}}}},"router":{"name":"compositeId"},"maxShardsPerNode":"1","autoAddReplicas":"false","znodeVersion":377,"configName":"NamesDictionary"},"Providers":{"replicationFactor":"1","shards":{"shard1":{"range":"80000000-7fffffff","state":"active","replicas":{"core_node1":{"core":"Providers_shard1_replica1","base_url":"http://ATLAPDSOLR02:9093/solr","node_name":"ATLAPDSOLR02:9093_solr","state":"down"},"core_node2":{"core":"Providers_shard1_replica2","base_url":"http://ATLAPDSOLR02:8983/solr","node_name":"ATLAPDSOLR02:8983_solr","state":"active","leader":"true"}}}},"router":{"name":"compositeId"},"maxShardsPerNode":"1","autoAddReplicas":"false","znodeVersion":569,"configName":"Providers"},"Q4LClientData":{"replicationFactor":"1","shards":{"shard1":{"range":"80000000-7fffffff","state":"active","replicas":{"core_node1":{"core":"Q4LClientData_shard1_replica1","base_url":"http://ATLAPDSOLR02:9093/solr","node_name":"ATLAPDSOLR02:9093_solr","state":"down"},"core_node2":{"core":"Q4LClientData_shard1_replica2","base_url":"http://ATLAPDSOLR02:8983/solr","node_name":"ATLAPDSOLR02:8983_solr","state":"active","leader":"true"}}}},"router":{"name":"compositeId"},"maxShardsPerNode":"1","autoAddReplicas":"false","znodeVersion":415,"configName":"Q4LClientData"},"nextgencloud":{"replicationFactor":"1","shards":{"shard1":{"range":"80000000-7fffffff","state":"active","replicas":{"core_node1":{"core":"nextgencloud_shard1_replica1","base_url":"http://ATLAPDSOLR02:9093/solr","node_name":"ATLAPDSOLR02:9093_solr","state":"down"},"core_node2":{"core":"nextgencloud_shard1_replica2","base_url":"http://ATLAPDSOLR02:8983/solr","node_name":"ATLAPDSOLR02:8983_solr","state":"active","leader":"true"}}}},"router":{"name":"compositeId"},"maxShardsPerNode":"1","autoAddReplicas":"false","znodeVersion":494,"configName":"nextgencloud"},"workbaskets":{"replicationFactor":"1","shards":{"shard1":{"range":"80000000-7fffffff","state":"active","replicas":{"core_node1":{"core":"workbaskets_shard1_replica1","base_url":"http://ATLAPDSOLR02:9093/solr","node_name":"ATLAPDSOLR02:9093_solr","state":"down"},"core_node2":{"core":"workbaskets_shard1_replica2","base_url":"http://ATLAPDSOLR02:8983/solr","node_name":"ATLAPDSOLR02:8983_solr","state":"active","leader":"true"}}}},"router":{"name":"compositeId"},"maxShardsPerNode":"1","autoAddReplicas":"false","znodeVersion":341,"configName":"workbaskets"},"operators":{"replicationFactor":"1","shards":{"shard1":{"range":"80000000-7fffffff","state":"active","replicas":{"core_node1":{"core":"operators_shard1_replica1","base_url":"http://ATLAPDSOLR02:9093/solr","node_name":"ATLAPDSOLR02:9093_solr","state":"down"},"core_node2":{"core":"operators_shard1_replica2","base_url":"http://ATLAPDSOLR02:8983/solr","node_name":"ATLAPDSOLR02:8983_solr","state":"active","leader":"true"}}}},"router":{"name":"compositeId"},"maxShardsPerNode":"1","autoAddReplicas":"false","znodeVersion":479,"configName":"operators"},"4DParticipant":{"replicationFactor":"1","shards":{"shard1":{"range":"80000000-7fffffff","state":"active","replicas":{"core_node1":{"core":"4DParticipant_shard1_replica1","base_url":"http://ATLAPDSOLR02:8983/solr","node_name":"ATLAPDSOLR02:8983_solr","state":"active","leader":"true"},"core_node2":{"core":"4DParticipant_shard1_replica2","base_url":"http://ATLAPDSOLR02:9093/solr","node_name":"ATLAPDSOLR02:9093_solr","state":"down"}}}},"router":{"name":"compositeId"},"maxShardsPerNode":"1","autoAddReplicas":"false","znodeVersion":394,"configName":"4DParticipant"},"medications":{"replicationFactor":"1","shards":{"shard1":{"range":"80000000-7fffffff","state":"active","replicas":{"core_node1":{"core":"medications_shard1_replica1","base_url":"http://ATLAPDSOLR02:9093/solr","node_name":"ATLAPDSOLR02:9093_solr","state":"down"},"core_node2":{"core":"medications_shard1_replica2","base_url":"http://ATLAPDSOLR02:8983/solr","node_name":"ATLAPDSOLR02:8983_solr","state":"active","leader":"true"}}}},"router":{"name":"compositeId"},"maxShardsPerNode":"1","autoAddReplicas":"false","znodeVersion":564,"configName":"medications"},"SearchAllParticipants":{"replicationFactor":"1","shards":{"shard1":{"range":"80000000-7fffffff","state":"active","replicas":{"core_node1":{"core":"SearchAllParticipants_shard1_replica1","base_url":"http://ATLAPDSOLR02:9093/solr","node_name":"ATLAPDSOLR02:9093_solr","state":"down"},"core_node2":{"core":"SearchAllParticipants_shard1_replica2","base_url":"http://ATLAPDSOLR02:8983/solr","node_name":"ATLAPDSOLR02:8983_solr","state":"active","leader":"true"}}}},"router":{"name":"compositeId"},"maxShardsPerNode":"1","autoAddReplicas":"false","znodeVersion":635,"configName":"SearchAllParticipants"},"conditions":{"replicationFactor":"1","shards":{"shard1":{"range":"80000000-7fffffff","state":"active","replicas":{"core_node1":{"core":"conditions_shard1_replica1","base_url":"http://ATLAPDSOLR02:8983/solr","node_name":"ATLAPDSOLR02:8983_solr","state":"active","leader":"true"},"core_node2":{"core":"conditions_shard1_replica2","base_url":"http://ATLAPDSOLR02:9093/solr","node_name":"ATLAPDSOLR02:9093_solr","state":"down"}}}},"router":{"name":"compositeId"},"maxShardsPerNode":"1","autoAddReplicas":"false","znodeVersion":474,"configName":"conditions"},"Q4LProviders":{"replicationFactor":"2","shards":{"shard1":{"range":"80000000-7fffffff","state":"active","replicas":{"core_node1":{"core":"Q4LProviders_shard1_replica1","base_url":"http://ATLAPDSOLR02:8983/solr","node_name":"ATLAPDSOLR02:8983_solr","state":"active","leader":"true"},"core_node2":{"core":"Q4LProviders_shard1_replica2","base_url":"http://ATLAPDSOLR02:9093/solr","node_name":"ATLAPDSOLR02:9093_solr","state":"down"}}}},"router":{"name":"compositeId"},"maxShardsPerNode":"1","autoAddReplicas":"false","znodeVersion":181,"configName":"Q4LProviders"}},"live_nodes":["ATLAPDSOLR02:8983_solr"]}}

[somesoni2]

Try this for your alert search
Updated

index=application sourcetype=json_no_timestamp DOWN OR RECOVERING OR RECOVERY_FAILED 
 | rename cluster.collections.* as *
 | rename *shard1.replicas.core_node* as *node*
 | rename *shards.* as **
 | table host *state
 | eval shouldAlert="N"
 | foreach *state [eval shouldAlert=if('<<FIELD>>'!="active","Y",shouldAlert)]
 | where shouldAlert="Y" 

This will return result when at least one of the *statecolumns have value != "active". Your alert condition should be number of events greater than 0

[JDukeSplunk]

Nice try, but it fails completely. We have a subset of data where a few nodes were downed, and searching in that time range returns no results.
If I lop off the |where statement and add the field "shouldAlert" to the table I see it, but it only contains the word "shouldAlert".

I tried retooling the eval thinking that the != was throwing it. To this.

| foreach *state [eval shouldAlert=if('<<FIELD>>'=="down","DOWN","UP")]

Now shouldAlert appears once per line, and is showing as UP.

[somesoni2]

I did some typo. Corrected it now. The foreach should set the value of shouldAlert="Y" (so that alert can be fired) when one or more fields don't have state as active.
You can either use '<<FIELD>>'!="active" or '<<FIELD>>'="down" (if down is the only value that means state is not active).

[elliotproebstel · Accepted solution]

How about appending this to the end of your search (Updated, per comments below ):

| foreach *state [eval <<FIELD>>=if('<<FIELD>>'="active", NULL, '<<FIELD>>') ] 
| stats values(*state) AS *state BY host

The first line will make the contents of each field NULL if it was previously "active". The second line will list non-null values for each *state column, removing columns that only contain NULL values.

[JDukeSplunk]

Your corrected search got it! Thanks!

Final
index=application sourcetype=json_no_timestamp DOWN OR RECOVERING OR RECOVERY_FAILED
| rename cluster.collections.* as *
| rename shard1.replicas.core_node as node
| rename shards. as **
| foreach *state [eval <>=if('<>'="active", NULL, '<>') ]
| stats values(*state) as *state by host _time

[elliotproebstel]

Glad we were able to nail it down! FYI, the post ate your <<FIELD>> tags, because they weren't wrapped in the 101010 code tags. But I updated my first answer to make the solution easier for others to find (so they don't have to wade through the comments for it).

[JDukeSplunk]

I see where your going with this, but the search resulted in nasty errors and only returned the first three *state fields. Oddly enough, two of them were "active"

5 errors occurred while the search was executing. Therefore, search results might be incomplete. Hide errors.
[atlitpsplnk1_ind] Failed to parse templatized search for field '4DParticipant.node1.state'
[atlitpsplnk1_ind] Failed to parse templatized search for field '4DParticipant.node2.state'
[atlitpsplnk1_ind] Failed to parse templatized search for field '4DParticipant.node3.state'
[atlitpsplnk1_ind] Failed to parse templatized search for field '4DParticipant.node4.state'
[atlitpsplnk1_ind] Failed to parse templatized search for field '4DParticipant.shard1.state'

[elliotproebstel]

Hmm, I think this is because of the periods in the name of the field. If all column headers have two or zero periods, then this might do it:

...
| rename *.*.* AS *_*_* 
| foreach *state [eval <<FIELD>>=if(<<FIELD>>="active", NULL, <<FIELD>>) ] 
| stats values(*state) AS *state BY host

[elliotproebstel]

Oh, I'm a doof and forgot to wrap the <<FIELD>> tags in single quotes within the subsearch. I think that would also fix it. So like this:

| foreach *state [eval <<FIELD>>=if('<<FIELD>>'="active", NULL, '<<FIELD>>') ] 
| stats values(*state) AS *state BY host