Splunk Search

filtering in search also that filter value display one of the filed of stats or table column

manibattula
New Member

I have query like below

index="us_west_prod_power_platform" sourcetype="spark:metric" metricName="HRTBT_LHIST_METRIC_DD" host="emr-prod-distributor" osm_zone_id |timechart span=10m count | eval ds_count = if(count >= "1","0","1") |timechart span=10m values(ds_count)

In that "osm_zone_id " is filter ,I want that osm_zone_id is one of the field of search ,something like below.

index="us_west_prod_power_platform" sourcetype="spark:metric" metricName="HRTBT_LHIST_METRIC_DD" host="emr-prod-distributor" osm_zone_id |timechart span=10m count | eval ds_count = if(count >= "1","0","1") |timechart span=10m values(ds_count)| table osm_zone_id,time,ds_count

Kindly suggest us.

Labels (2)
Tags (1)
0 Karma
1 Solution

to4kawa
Ultra Champion
index="us_west_prod_power_platform" sourcetype="spark:metric" metricName="HRTBT_LHIST_METRIC_DD" host="emr-prod-distributor" osm_zone_id=*
| bin span=10m _time
| stats count by _time osm_zone_id
| eval ds_count = if(count >= 1,"0","1") 
| eval time=strftime(_time,"%F %T")
| table osm_zone_id,time,ds_count

View solution in original post

0 Karma

manibattula
New Member

let me explain clearly

index="us_west_prod_power_platform" sourcetype="spark:metric" metricName="HRTBT_LHIST_METRIC_DD" host="emr-prod-distributor" osm_zone_id*
|spath output=osm_zone_id path=dimensions{2}.value
|bin span=10m _time
|stats count by _time,osm_zone_id
| eval ds_count = if(count >= 1,"0","1")
| eval time=strftime(_time,"%F %T")
| table osm_zone_id,time,ds_count

Result:-

Osm_zone_id _time ds_count
00af8f04-88fc-4dc0-b338-42d2e7e9c163 2020-04-29 21:20:00 0
00af8f04-88fc-4dc0-b338-42d2e7e9c163 2020-04-29 22:00:00 0
00af8f04-88fc-4dc0-b338-42d2e7e9c163 2020-04-29 22:20:00 0
00af8f04-88fc-4dc0-b338-42d2e7e9c163 2020-04-29 22:40:00 0

expected result should be
Osm_zone_id _time ds_count
00af8f04-88fc-4dc0-b338-42d2e7e9c163 2020-04-29 21:20:00 0
00af8f04-88fc-4dc0-b338-42d2e7e9c163 2020-04-29 22:00:00 0
00af8f04-88fc-4dc0-b338-42d2e7e9c163 2020-04-29 22:10:00 1
00af8f04-88fc-4dc0-b338-42d2e7e9c163 2020-04-29 22:20:00 0
00af8f04-88fc-4dc0-b338-42d2e7e9c163 2020-04-29 22:40:00 0

Which are empty osm_zone_id also I want that time bucket

I am trying with cross join also

s | bin _time span=1h | fields _time | join max=0 [search index="us_west_prod_power_platform" sourcetype="spark:metric" metricName="HRTBT_LHIST_METRIC_DD" host="emr-prod-distributor" | spath output=osm_zone_id path=dimensions{2}.value | dedup osm_zone_id | fields osm_zone_id] | table _time, osm_zone_id

Here, _time and osm_zone_id should be null ,even there is no osm_zone_id I want to make that count is 0

Please verify.

0 Karma

manibattula
New Member

One more question,

index="us_west_prod_power_platform" sourcetype="spark:metric" metricName="HRTBT_LHIST_METRIC_DD" host="emr-prod-distributor" osm_zone_id=*
| bin span=10m _time
| stats count by _time osm_zone_id
| eval ds_count = if(count >= 1,"0","1")
| eval time=strftime(_time,"%F %T")
| table osm_zone_id,time,ds_count

So here I am only getting each 10 minutes span which are having osm_zone_id > 0,I want include which are having zero also,is that possible?

0 Karma

to4kawa
Ultra Champion

I don't know your result.
osm_zone_id > 0 , 1
just simply, osm_zone_id >=0 ?

0 Karma

manibattula
New Member

So here I am only getting each 10 minutes span which are having osm_zone_id > 0 , but I need

osm_zone_id = 0 results too.

The query only gives osm_zone_id is more than zero records,I want to include osm_zone_id equal to zero results too

0 Karma

to4kawa
Ultra Champion

In you query, limitation of osm_zone_id is only osm_zone_id=* , not osm_zone_id > 0

0 Karma

manibattula
New Member

Thanks for immediate response

let me explain clearly

index="us_west_prod_power_platform" sourcetype="spark:metric" metricName="HRTBT_LHIST_METRIC_DD" host="emr-prod-distributor" osm_zone_id*
|spath output=osm_zone_id path=dimensions{2}.value
|bin span=10m _time
|stats count by _time,osm_zone_id
| eval ds_count = if(count >= 1,"0","1")
| eval time=strftime(_time,"%F %T")
| table osm_zone_id,time,ds_count

Result:-

Osm_zone_id _time ds_count
00af8f04-88fc-4dc0-b338-42d2e7e9c163 2020-04-29 21:20:00 0
00af8f04-88fc-4dc0-b338-42d2e7e9c163 2020-04-29 22:00:00 0
00af8f04-88fc-4dc0-b338-42d2e7e9c163 2020-04-29 22:20:00 0
00af8f04-88fc-4dc0-b338-42d2e7e9c163 2020-04-29 22:40:00 0

expected result should be
Osm_zone_id _time ds_count
00af8f04-88fc-4dc0-b338-42d2e7e9c163 2020-04-29 21:20:00 0
00af8f04-88fc-4dc0-b338-42d2e7e9c163 2020-04-29 22:00:00 0
00af8f04-88fc-4dc0-b338-42d2e7e9c163 2020-04-29 22:10:00 1
00af8f04-88fc-4dc0-b338-42d2e7e9c163 2020-04-29 22:20:00 0
00af8f04-88fc-4dc0-b338-42d2e7e9c163 2020-04-29 22:40:00 0

Which are empty osm_zone_id also I want that time bucket

I am trying with cross join also

s | bin _time span=1h | fields _time | join max=0 [search index="us_west_prod_power_platform" sourcetype="spark:metric" metricName="HRTBT_LHIST_METRIC_DD" host="emr-prod-distributor" | spath output=osm_zone_id path=dimensions{2}.value | dedup osm_zone_id | fields osm_zone_id] | table _time, osm_zone_id

Here, _time and osm_zone_id should be null ,even there is no osm_zone_id I want to make that count is 0

Please verify.

0 Karma

to4kawa
Ultra Champion

This should be needed timechart
I can't make the query without logs.

good luck.

0 Karma

to4kawa
Ultra Champion
index="us_west_prod_power_platform" sourcetype="spark:metric" metricName="HRTBT_LHIST_METRIC_DD" host="emr-prod-distributor" osm_zone_id=*
| bin span=10m _time
| stats count by _time osm_zone_id
| eval ds_count = if(count >= 1,"0","1") 
| eval time=strftime(_time,"%F %T")
| table osm_zone_id,time,ds_count
0 Karma

manibattula
New Member

Hello to4kawa,

It is working thanks much.

0 Karma

to4kawa
Ultra Champion

I see, please accept my answer

0 Karma

manibattula
New Member

I think the above is failing at stats statement

index="us_west_prod_power_platform" sourcetype="spark:metric" metricName="HRTBT_LHIST_METRIC_DD" host="emr-prod-distributor" osm_zone_id=*
| bin span=10m _time
| stats count by _time osm_zone_id

Here there are multiple osm_zone_id's will be appeared in single event timestamp.

Kindly suggest

0 Karma

to4kawa
Ultra Champion

Here there are multiple osm_zone_id's will be appeared in single event timestamp.
yes, add where or search

0 Karma

manibattula
New Member

Sorry,Not clear with above statment.

kindly rewrite the entire query again.

0 Karma
Get Updates on the Splunk Community!

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...

Introducing the Splunk Community Dashboard Challenge!

Welcome to Splunk Community Dashboard Challenge! This is your chance to showcase your skills in creating ...