I have ID and dates in my output. (consider this is the data from 02-07-2016 to 02-10-2016)
Now, I want to find the dates when these IDs did not get ingested. eg: Output should be:
How can we achieve this Splunk?
Thanks in advance!
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
"| mvexpand Missing" in the end to get the Missing dates separate rows
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 infomintime and infomaxtime, it is adding 4th Feb (min time) and 11th Feb (max time) for every ID.
2. In the step where you are finding ingestionepoch, it is getting calculated only for the above dates (4th Feb and 11th Feb). For the rest of the dates, ingestionepoch 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 ingestionepoch is blank for them).
4. Then in the streamstats, i am getting previous result values like this->
ID ingestiondate Prev
1 4th Feb
1 11th Feb 4th Feb
1 5th Feb 11th Feb
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.
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.