topic How to get the dates which are not present in output for every ID? in Splunk Search

How to get the dates which are not present in output for every ID?

shrutikale19 — Thu, 11 Feb 2016 00:34:59 GMT

Hi,

I have ID and dates in my output. (consider this is the data from 02-07-2016 to 02-10-2016)
e.g
ID Ingestion_Date
1 2-10-2016
1 2-09-2016
1 2-07-2016
2 2-10-2016
2 2-08-2016

Now, I want to find the dates when these IDs did not get ingested. eg: Output should be:
ID Ingestion_Date
1 2-08-2016
2 2-09-2016
2 2-07-2016

How can we achieve this Splunk?

Thanks in advance!

Re: How to get the dates which are not present in output for every ID?

somesoni2 — Thu, 11 Feb 2016 02:39:06 GMT

Is the date range fixed OR values for ID fixed??

Re: How to get the dates which are not present in output for every ID?

shrutikale19 — Thu, 11 Feb 2016 16:39:14 GMT

Date range will be passed to query from splunk input (dropdown) (mostly it wont exceed more than last 60-30 days) and values of IDs are coming from one table.

Re: How to get the dates which are not present in output for every ID?

somesoni2 — Thu, 11 Feb 2016 17:20:48 GMT

Assuming you're using timerange picker to select date range, try something like this (replace everything before "| appendpipe" with your current search)

| gentimes start=-1 | eval temp="1,2-10-2016 1,2-09-2016 1,2-06-2016 2,2-10-2016 2,2-08-2016" | makemv temp | table temp | mvexpand temp | makemv temp delim="," | eval ID=mvindex(temp,0) | eval Ingestion_Date=mvindex(temp,-1) | fields - temp 
 | appendpipe [| stats count by ID | addinfo | eval Ingestion_Date=strftime(info_min_time,"%m-%d-%Y")." ".strftime(info_max_time,"%m-%d-%Y") | makemv Ingestion_Date | mvexpand Ingestion_Date | table ID Ingestion_Date  ]| eval ingestion_epoch=strptime(Ingestion_Date,"%m-%d-%Y") | sort 0 ID ingestion_epoch | streamstats current=f window=1 values(ingestion_epoch) as prev by ID | eval Missing=mvrange(prev+86400,ingestion_epoch,86400) | eval Missing=strftime(Missing,"%m-%d-%Y") | where isnotnull(Missing) | table ID Missing

Optionally add "| mvexpand Missing" in the end to get the Missing dates separate rows

Re: How to get the dates which are not present in output for every ID?

shrutikale19 — Tue, 29 Sep 2020 08:47:50 GMT

Hi,

I tried adding this in my query but i am not getting correct result.
e.g consider i am running this for last 7 days ( from 4th Feb to 11th Feb), then for all the IDs I am getting missing dates as all the dates from 5th Feb-10th Feb.
This is how it is working:
1. In the step where you are adding info_min_time and info_max_time, it is adding 4th Feb (min time) and 11th Feb (max time) for every ID.
2. In the step where you are finding ingestion_epoch, it is getting calculated only for the above dates (4th Feb and 11th Feb). For the rest of the dates, ingestion_epoch is coming blank.
3. In the sorting step, for every ID its sorting like this- 4th Feb, 11th Feb, other dates present for that ID (as ingestion_epoch is blank for them).
4. Then in the streamstats, i am getting previous result values like this->
ID ingestion_date Prev
1 4th Feb

1 11th Feb 4th Feb
1 5th Feb 11th Feb
etc.
5. So, when mvrange function is used, its giving all the dates from (5th Feb-11th Feb) for the record with ID=1 and prev=4th Feb from the above table.
6. with where isnotnull(Missing), final result is only picking row from above and showing it as result.

Please let me know where it is going wrong.

Thanks again!

Re: How to get the dates which are not present in output for every ID?

shrutikale19 — Fri, 12 Feb 2016 00:15:29 GMT

Hi,
I also tried running your query and it gives that ID 2 was not ingested only on 9th Feb. Though, as your time range starts from 6th Feb here, ideally it should show that, ID 2 was not ingested on 6th, 7th and 9th Feb. This is what I am trying to achieve here.
Please let me know if you have any idea on how can we do this in Splunk.
Thanks!