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

[manibattula]

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.

[manibattula]

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.

[manibattula]

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?

[to4kawa]

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

[manibattula]

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

[to4kawa]

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

[manibattula]

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.

[to4kawa]

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

good luck.

[to4kawa · Accepted solution]

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

[manibattula]

Hello to4kawa,

It is working thanks much.

[to4kawa]

I see, please accept my answer

[manibattula]

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

[to4kawa]

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

[manibattula]

Sorry,Not clear with above statment.

kindly rewrite the entire query again.