topic Re: Is it possible to list out empty index in Getting Data In

Is it possible to list out empty index

chandana204 — Wed, 13 Jun 2018 17:20:12 GMT

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

Re: Is it possible to list out empty index

PowerPacked — Tue, 29 Sep 2020 19:54:37 GMT

Hi @chandana204

Give this search a try

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

Thanks

Re: Is it possible to list out empty index

chandana204 — Wed, 13 Jun 2018 19:03:29 GMT

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

Re: Is it possible to list out empty index

chandana204 — Wed, 13 Jun 2018 20:43:19 GMT

Why I can't see my comment here?

Re: Is it possible to list out empty index

chandana204 — Tue, 29 Sep 2020 19:54:45 GMT

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

Re: Is it possible to list out empty index

sushantmhatre — Wed, 13 Jun 2018 20:55:34 GMT

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

Re: Is it possible to list out empty index

chandana204 — Wed, 13 Jun 2018 20:55:39 GMT

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

Re: Is it possible to list out empty index

chandana204 — Thu, 14 Jun 2018 14:41:47 GMT

Can anybody solve this issue?

Thanks in advance,
Chandana

Re: Is it possible to list out empty index

ssadanala1 — Thu, 14 Jun 2018 14:58:19 GMT

what is your time frame looks like for the search ?

Thanks

Re: Is it possible to list out empty index

niketn — Thu, 14 Jun 2018 15:06:59 GMT

@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

Re: Is it possible to list out empty index

chandana204 — Thu, 14 Jun 2018 16:07:31 GMT

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

Re: Is it possible to list out empty index

PowerPacked — Thu, 14 Jun 2018 16:31:28 GMT

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

In missing tuesday's probably my local spunk was down

Thanks

Re: Is it possible to list out empty index

chandana204 — Thu, 14 Jun 2018 16:54:55 GMT

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

Re: Is it possible to list out empty index

PowerPacked — Thu, 14 Jun 2018 17:57:48 GMT

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

Re: Is it possible to list out empty index

chandana204 — Thu, 14 Jun 2018 18:08:04 GMT

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

Re: Is it possible to list out empty index

niketn — Mon, 18 Jun 2018 09:07:23 GMT

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

Re: Is it possible to list out empty index

chandana204 — Tue, 29 Sep 2020 20:02:03 GMT

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

Re: Is it possible to list out empty index

niketn — Tue, 19 Jun 2018 17:54:06 GMT

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

Re: Is it possible to list out empty index

chandana204 — Wed, 20 Jun 2018 20:24:15 GMT

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

Re: Is it possible to list out empty index

chandana204 — Wed, 20 Jun 2018 20:49:37 GMT

Yup i got solution for time conversion.

Thanks,
Chandana