Splunk Search
Highlighted

Issue With Date Range

Explorer

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.

0 Karma
Highlighted

Re: Issue With Date Range

Builder

can you post a sample of your Json

0 Karma
Highlighted

Re: Issue With Date Range

Explorer

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

0 Karma
Highlighted

Re: Issue With Date Range

Explorer

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

0 Karma
Highlighted

Re: Issue With Date Range

Legend

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




| eval message="Happy Splunking!!!"


0 Karma
Highlighted

Re: Issue With Date Range

Explorer

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"
}

0 Karma
Highlighted

Re: Issue With Date Range

Legend

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.




| eval message="Happy Splunking!!!"


Highlighted

Re: Issue With Date Range

Explorer

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.

0 Karma
Highlighted

Re: Issue With Date Range

Legend

@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




| eval message="Happy Splunking!!!"


Highlighted

Re: Issue With Date Range

Explorer

I will check it out. Thanks for the info.

0 Karma