Is it possible to list out empty index

[chandana204]

Hi,

I am working on index="retail_ca", The problem with this index is some days the data is not ingesting in this index. I have created a query to calculate standard deviation on this index for every week. So the thing is, these empty index days are not adding in the calculations. I wanted to list out the empty indexes dates with count=0. Is it possible?

Updating my query as my time range is from Apr 1st to till date, why Splunk is listing March (2018-03-27) data?

You can see that in the below image.

Thanks,
Chandana

New Update 06/15/2018: So i have changed time rage as earliest=-2mon@-1w latest=now. With this time range most of the weekday dates are selecting properly except where month's first day and weekday matched.

Example: In the April month, first day started on Sunday. In this kind of scenario the time range picker is not selecting April 1st day data for weekday=sunday.

Please help me If anybody know how to overcome this issue.

Not a proper Solution: Finally, I found solution for this issue. Here is the solution: I gave time range as earliest=-2mon@-8d latest=now . That's it.

I thought the above time range is a proper solution but it's not.

Please help me to find a proper solution.

Thanks,
Chandana

[PowerPacked]

Adding as an answer as i can't upload a image in comments.

In missing tuesday's probably my local spunk was down

Thanks

[chandana204]

Thank you for resonse @PowerPacked. This is my actual query but it's not listing empty data dates. I wanted to include missing tuesday's count as 0 with date specification. For this if I have used timechart query (As you responded yesterday) i can see empty data dates including march data.

Please have a look at the image in my query. Hopefully you understand my issue.

Thanks,
Chandana

[PowerPacked]

Even am not sure, why its listing 27th data from the march

all i can think of is --- as we are doing a span of 1week, it might be pulling data from week starting in march and ending in April

Work Around:
if you do earliest as April 3rd you wont see the march 27th anymore.

Thanks

[chandana204]

Yeah, you're right but i am working on rel time data, I am making calculations on weekday basis for the last 2 months. The timeperiod will change for every new month. So it's not good idea to pick particular date.

Is there any other way to overcome this issue?

Thanks,
Chandana

[niketn]

@chandana24, for scenarios like this when you have to work on metadata fields, it is better to use commands like tstats, metadata, dbinspect or eventcount depending on what fits your needs.

Following is the command you should try:

| tstats count where index=retail_ca by _time span=1d

[chandana204]

Thank you for your response. But it's not working on my analysis. With the below code, i am able to list out all dates but it's including last week (tuesday) of march data.

index=retail_ca | timechart span=1w@w2 count by index

I just want to get rid of the march data from the table. Is that possible.
Why I am sticking to this query cause I can work on specific key words also as below

index=retail_ca "process" | timechart span=1d count by index

Interestingly I observed something i.e., if i select time range as last 1 month (first day of month) to till date. It's not listing April months data. But if select as last 2 month (first day of month) to till date, it's listing march data. I don't understand why it's considering March month data? Is there any problem with the Splunk time range picker?

Hope you understand my requirement. And it would be great if you help me on this.

Thanks,
Chandana

[niketn]

@chandana204, sorry for the delay in my response. I am afraid I am not able to grasp your issue. However, if you feel that correct events are not being picked up Time Picker, you should validate Timestamp in your raw event and see whether the same matches with _time or not. Also it would be good to check your account's Time Zone settings.

[chandana204]

That's okay @niketn. I cross checked everything. Everything is fine. I am thinking this problem is with timechart query.

I'll explain here exactly what kind of issue i am facing with timechart query.
When i use relative time range as last 2 months(start day of month) to now on a particular weekday, the _raw data is listing exactly as time range. after if i put this in timechart to see count in a table format, it's pulling 3rd month's last week data also with count=0. I don't understand how to resolve this issue. Below I am giving example:

Query: index="retail_ca" process="process" date_wday="monday" | timechart span=w@w1 count by process
Relative time Range: last 2 months (starting of the day) to now

To get more clarification you ca run above query as below
Query: index="_internal" "post" date_wday="monday" | timechart span=w@w1 count by index
Relative time Range: last 2 months (starting of the day) to now

Thanks,
Chandana

[niketn]

Try one of the following:

1) Manually remove first row. For example using streamstats as below:

index="_internal" "post" date_wday="monday" 
| timechart span=w@w1 count by index
| streamstats count as sno
| search sno!=1
| fields - sno

2) Ensure that time picked is for specific week you are interested in using stats with Week of the Year Time format.

index="_internal" "post" date_wday="monday" 
| eval WeekOfYear=strftime(_time,"%Y - %U") 
| stats count min(_time) as MinTime by WeekOfYear 
| eval MinTime=strftime(MinTime,"%m/%d/%Y") 
| eval WeekDateRange=strftime(relative_time(strptime(MinTime,"%m/%d/%Y"),"-0w@w"),"%m/%d/%Y")." - ".strftime(relative_time(strptime(MinTime,"%m/%d/%Y"),"+1w@w-1d"),"%m/%d/%Y")

Refer to answer: https://answers.splunk.com/answers/637244/time-range-to-display-count-of-weekly.html

[chandana204]

Thank you @niketnilay. I can use streamstats to remove first column.

I got one more question: I have created another column with yesterday date. Now i want to compare _time column with yesterday's date column but it's not comparing. Do you know how to make it happen?

I tried as "where _time=yesterday"

Thanks,
Chandana

[niketn]

If both _time and yesterday are epoch you would need use a range i.e. _time>=yesterday AND _time<yesterday+86400. Where yesterday should represent -1d@d or midnight.

If yesterday is string time, it should be of format YYYY-mm-dd and you would need to perform strftime(_time,"YYYY-mm-dd") to compare both dates as string time.

However, best option would be to fetch only the results from yesterday using base search filter i.e. either Time Picker or earliest=-1d@d latest=-0d@d-1s.

Please add more context for us to assist you with your issue, if one of the above does not fit the needs. Possibly ask a new question 😉

[chandana204]

Yup i got solution for time conversion.

Thanks,
Chandana

[sushantmhatre]

I hope your index list is fixed. You can create lookup with all the index names then append that into your search results. Further, join to find out empty indexes

[PowerPacked]

Hi @chandana204

Give this search a try

index=retail_ca | timechart span=1d count by index | search retail_ca=0

Thanks

[chandana204]

Why I can't see my comment here?

[chandana204]

Anyways, I tried the above query it is working fine for daily basis. It's not working exactly for weekday.

Let's say I want to do calculations on "Tuesday" from Apr 1 st to current day. I modified above query as span=1w@w. the timechart is listing dates from Thursday days. So I gone through document and i found i that i can mention the day as span=1w@w2. Now timechart listed all tuesday data but it included march last week data also.

Query:
index=retail_ca date_wday=tuesday | timechart span=1w@w2 count by index

Why it's listing March data when i put time range from Apr to till?

Thanks,
Chandana

[chandana204]

Can anybody solve this issue?

Thanks in advance,
Chandana

[ssadanala1]

what is your time frame looks like for the search ?

Thanks

[chandana204]

This is working fine. But if i want to work on a particular week day this query is not affecting.

Let's say I want to pull data for "Tuesday" from Apr1st to till now. If i use above query it's pulling all other week day count as 0. So, to get only weekday info i modified span=1w but the date is not accurate.

Actual tuesday dates from April to now as: 04/ 03/2018; 04/10/2018; 04/17/2018; 04/24/2018; 05/1/2018; 05/08/2018; 05/15/2018; 05/22/2018; 05/29/2018; 06/05/2018; 06/12/2018

But in the timechart it's listing _time as: 04/01/2018; 04/08/2018; 04/15/2018; 04/22/2018; 04/29/2018; 05/06/2018; 05/13/2018; 05/20/2018; 05/27/2018; 06/03/2018; 06/10/2018

Why it is not listing actual date in the chart? Is there any other query to manipulate it to actual date?

Thanks,
Chandana

[chandana204]

I got answer for why timechart is listing dates as 04/01/2018; 04/08/2018; 04/15/2018; 04/22/2018; 04/29/2018; 05/06/2018; 05/13/2018; 05/20/2018; 05/27/2018; 06/03/2018; 06/10/2018