count null timechart spans

[mwcooley]

Hi,

I have the following search that displays a table with time as rows and conferenceID as columns. i only want to include conferenceIDs (columns) where the sum > 1 and there are at least 2 non-zero spans.

My search:

index=myindex sourcetype=mysourcetype
| timechart span=7d count by ConferenceID limit=0 where sum > 1

I want to keep column 1, but not column 2:

          1001     1002
wk1         1        2
wk2         0        0
wk3         1        0
wk4         0        0

[somesoni2 · Accepted solution]

Give this a try

index=myindex sourcetype=mysourcetype
| bucket span=7d _time
| stats count by _time ConferenceID
| eventstats dc(_time) as weeks by ConferenceID 
| where weeks>=2
| timechart span=7d sum(count) by ConferenceID limit=0 

[mwcooley]

Thanks somesoni2. works like a charm.