Splunk Search

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

JDukeSplunk
Builder

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.

alt text

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"]}}
0 Karma
1 Solution

elliotproebstel
Champion

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.

View solution in original post

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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

0 Karma

JDukeSplunk
Builder

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.

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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).

0 Karma

elliotproebstel
Champion

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.

0 Karma

JDukeSplunk
Builder

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

0 Karma

elliotproebstel
Champion

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).

0 Karma

JDukeSplunk
Builder

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'
0 Karma

elliotproebstel
Champion

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
0 Karma

elliotproebstel
Champion

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
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...