Issue With Date Range

[liberty5]

I am having a problem using a date range.

If I run the search below it returns 2 events and a count of 496

index="test2" (cRecords{}.bDate = "05/16/2019" OR cRecords{}.bDate = "05/17/2019") | stats count(cRecords{}.bDate)

If I run the search below it returns 0 events and a count of 0

index="test2" | spath output=bDate path=cRecords{}.bDate | eval beginDate = strptime("05/16/2019","%m/%d/%Y") | eval endDate = strptime("05/17/2019","%m/%d/%Y") | eval thisDate = strptime(bDate, "%m/%d/%Y") | where thisDate >= beginDate AND thisDate <= endDate | stats count(bDate)

I think these searches should return the same results. I know the first search is correct. Why is the second search returning nothing when I attempt to use a date range? Thank you for any information you could provide.

[anmolpatel · Accepted solution]

@liberty5 here is your main issue -
When you run the spath command, it extracts all the cRecords{}.Date into a multi valued field. This is expected. Then upon running the eval command, you're attempting to strptime on a multivalued field. This is also fine. Where is fails is doing the comparison using the | where thisDate >= beginDate AND thisDate<= endDate. Cannot carry out comparison on a multi valued field like this.
What you need is an | spath output=bDate path=cRecords{}.bDate| mvexpand bDate

Before mvexpand:

After mvexpand:

Then upon execution of the query, you'll get the desired outcome for the above posted JSON.

| spath output=bDate path="cRecords{}.bDate" 
| mvexpand bDate 
| eval beginDate = "05/16/2019", endDate = "05/18/2019" 
| eval beginDate = strptime(beginDate,"%m/%d/%Y"),
    endDate = strptime(endDate,"%m/%d/%Y"), 
    thisDate = strptime(bDate, "%m/%d/%Y") 
| where thisDate >= beginDate AND thisDate <= endDate 
| stats count(bDate)

count(bDate)
4

[liberty5]

Thank you, everything is working fine now. Thank you for letting me know about that that and for the quick response.

[niketn]

@liberty5 since the remaining part of your query seem to be fine and you are getting 0 count, you must check the spath pipe to ensure that you are getting bDate field and that the conversion to epoch time for all three are working fine. Please sample only one row and instead of filtering using the where condition just manually compare the three epoch dates.

Not sure if the following helps but try putting path in double quotes

| spath output=bDate path="cRecords{}.bDate"

Following is a run anywhere example as per your question, which generated bDate from 05/15 to 05/18 as a JSON with root node as cRecords and bDate as nested array JSON and reports the count of dates that fall between 05/16 and 05/17.

| makeresults 
| eval _raw="{
   \"cRecords\":[
      {\"bDate\":\"05\/15\/2019\"}
   ]
}"
| append [| makeresults 
| eval _raw="{
   \"cRecords\":[
      {\"bDate\":\"05\/16\/2019\"}
   ]
}"]
| append [| makeresults 
| eval _raw="{
   \"cRecords\":[
      {\"bDate\":\"05\/17\/2019\"}
   ]
}"]
| append [| makeresults 
| eval _raw="{
   \"cRecords\":[
      {\"bDate\":\"05\/18\/2019\"}
   ]
}"]
| spath path="cRecords{}.bDate" output=bDate
| eval beginDate = strptime("05/16/2019","%m/%d/%Y"),
       endDate = strptime("05/17/2019","%m/%d/%Y"), 
       thisDate = strptime(bDate, "%m/%d/%Y") 
| where thisDate >= beginDate AND thisDate <= endDate 
| stats count(bDate)

If the spath is not working for you then please re-evaluate the JSON structure and correct spath.

[liberty5]

Thank you for the feedback.

I have create a new index with a very small subset of the data and I am able to reproduce the issue. I have included the JSON below so maybe someone can see if they can reproduce it and if not tell me what is wrong with the query and/or data. Thank you.

This query works

index="test3" | spath output=bDate path=cRecords{}.bDate | eval beginDate = strptime("05/16/2019","%m/%d/%Y") | eval endDate = strptime("05/17/2019","%m/%d/%Y") | eval thisDate = strptime(bDate, "%m/%d/%Y") | where thisDate = beginDate OR thisDate = endDate | stats count(bDate)

This query does not

index="test3" | spath output=bDate path=cRecords{}.bDate | eval beginDate = strptime("05/16/2019","%m/%d/%Y") | eval endDate = strptime("05/17/2019","%m/%d/%Y") | eval thisDate = strptime(bDate, "%m/%d/%Y") | where thisDate >= beginDate AND thisDate <= endDate | stats count(bDate)

Here is the JSON

{
"cRecords": [
{
"bDate": "05/16/2019",
"count" : "10",
"charge": "100.10",
"type": "T",
"flag": "Y"
},
{
"bDate": "05/16/2019",
"count": "100",
"charge": "10000.01",
"type": "P",
"flag": "X"
}
],
"batch": "1"
}

{
"cRecords": [
{
"bDate": "05/17/2019",
"count" : "1",
"charge": "1.00",
"type": "P",
"flag": "Y"
},
{
"bDate": "05/17/2019",
"count": "5",
"charge": "25.25",
"type": "T",
"flag": "X"
}
],
"batch": "2"
}

[niketn]

If each of cRecords is your event you should be considering props and transforms configuration so that each cRecords section logs in as an individual event rather than multiple cRecords in one event. As you can see currently you will end up the path of multivalued field and multi-value commands which would eventually turn out to be resource consuming. If you are under the process of Data Ingestion and initial setting up of the system ensure that data is ingested correctly. For example bDate should be the timestamp (_time) of each event and each event should be a cRecords nested JSON.

[liberty5]

Hi niketnilay. Thank you for the information. Unfortunately I am new to Splunk. For efficiency it sound like we want to stay away from multi-valued fields once the data is in Splunk. So we would use props.conf or transforms.conf to get the multi-valued JSON data into Splunk where each multi-value would be a single event so in essence we are “flattening” the data, so to speak. Is that correct? Can you recommend some links that discuss the best way to handle this type of data in Splunk? Sorry, I am new to all this. Thank you for any information you could provide regarding this.

[niketn]

@liberty5 refer to Splunk Docs you can pass specific part of events which match regex

https://docs.splunk.com/Documentation/Splunk/latest/Forwarding/Routeandfilterdatad#Keep_specific_eve...

For example in your case try out with this regex (may change as per your exact data):
https://regex101.com/r/yaUNdY/1

[liberty5]

I will check it out. Thanks for the info.

[anmolpatel]

can you post a sample of your Json

[liberty5]

Hi Moderator, sorry I accidentally posted a comment as an answer. Please disregard the answer post and post the comment. Thank you.

[liberty5]

Thanks for all the feedback. I am going over it. What I can tell you is that if I try this it works:

index="test2" | spath output=bDate path=cRecords{}.bDate | eval beginDate = strptime("05/16/2019","%m/%d/%Y") | eval endDate = strptime("05/17/2019","%m/%d/%Y") | eval thisDate = strptime(bDate, "%m/%d/%Y") | where thisDate = beginDate | stats count(bDate)

But if I use >= (instead of 😃 it returns 0 events and a count of 0 so it seems there is an issue maybe with thisDate being considered as a string instead of a date? Also, if I try where thisDate = beginDate OR thisDate = endDate I also get 2 results and a count of 496.

Putting double quotes around cRecords{}.bDate has no effetc, i.e.
using this: | spath output=bDate path="cRecords{}.bDate"
instead of this: | spath output=bDate path=cRecords{}.bDate
does not resolve the issue