How to find the no of days different between indexed date time and the field exists date in my search?

[karunagaraprabh]

Hi, i am new to splunk, i need to find the number of days different between indexed time date and the field exists date , i first converted my field to epoc and finding the difference between printedA_epoch and _indextime (as it return epoc by default) but am getting return data as blank .

and i need to assign a var to 1 if it is grater than 0 

the printedtimestrampA data is "2020-06-20T01:23:23.693-0700"

| eval printedA_epoch=strptime(printedtimestrampA,"%Y-%m-%dT%H:%M:%S.%Q")
| eval indextime=_indextime
| eval fdata=round(((_indextime-printedA_epoch)/86400),0)
| eval daysA= if(fdata>0,1,0)
| table _indextime,printedA_epoch,fdata

[gcusello]

Hi @karunagaraprabh ,

try something like this:

index=_internal | head 1
| eval printedtimestrampA="2020-06-20T01:23:23.693-0700"
| eval printedA_epoch=strptime(printedtimestrampA,"%Y-%m-%dT%H:%M:%S.%Q")
| eval indextime=_indextime
| eval fdata=round(((indextime-printedA_epoch)/86400),0)
| eval daysA= if(fdata>0,1,0)
| table indextime printedA_epoch fdata daysA

You cannot put in table command _indextime.

If you want indextime in human readbale add at the end and eval command:

| eval indextime=strftime(indextime,"%d/%m/%Y %H:%M:%S")

Ciao.

Giuseppe

[karunagaraprabh]

Hi @gcusello ,

Thanks for the reply, even i get blank data for the below query 

eval fdata=round(((_indextime-printedA_epoch)/86400),0)

[gcusello]

Hi @karunagaraprabh ,

did you tried:

| eval indextime=_indextime
| eval fdata=round(((indextime-printedA_epoch)/86400),0)

?

Ciao.

Giuseppe

[karunagaraprabh]

Hi @gcusello ,

Yes i tried the same but i doesn't get any data apart from blank. even difference of this (indextime-printedA_epoch) also blank

Thanks,

Karunagaraprabhu

[gcusello]

could you share your search?

[karunagaraprabh]

Hi @gcusello , thanks for your kind support,Please find the snap shot 

Thanks 

Karuna

[gcusello]

Hi @karunagaraprabh ,

As I said you cannot use _indextime in the calculation, so in your search replace

| eval fdata=round(((_indextime-printedA_epoch)/86400),0)
| eval diffdata=(_indextime-printedA_epoch)
| table _indextime ...

with 

| eval indextime=_indextime
| eval fdata=round(((indextime-printedA_epoch)/86400),0)
| eval diffdata=indextime-printedA_epoch
| table indextime ...

in few words, you cannot use _indextime in commands, use indextime (after the eval command).

In addition you can use only one eval command, dividing the calculations with comma ",".

Ciao.

Giuseppe

[karunagaraprabh]

Hi @gcusello ,

When i try to find the difference i still get blank as difference, am not aware what mistake am doing

Thanks,

Karuna

[gcusello]

Hi @karunagaraprabh,

why do you have many values in printedA_epoch?

this is the reason because diff is empty; you have to take a number to calculate a difference.

You could use rex command

| rex field=printedA_epoch "^(?<printedA_epoch>\d+)"

to have something like this:

your_search
| rename ...
| rex field=printedA_epoch "^(?<printedA_epoch>\d+)"
| eval indextime=_indextime
| eval fdata=round(((indextime-printedA_epoch)/86400),0)
| eval diffdata=indextime-printedA_epoch
| table indextime ...

Ciao.

Giuseppe

[karunagaraprabh]

Hi @gcusello ,

This is my sample data there will be n number of jobs for each customer, i need to filter the Roll-Id,customer data and the different time stamp which falls under the indexed time 

condition for timestamp for start range A & B and end range A & B

if(round(((indextime-SideA-printedtimestamp)/86400),0)=0),1,0)

Roll-Id	Customer	start-range- SideA timestamp	start-range- SideB timestamp	end-range-SideA timestamp	end-range-SideB timestamp
Roll-Id1	Customer1	1	1	0	1
Roll-Id2	Customer 2	1	1	0	0

So when i filter the data i get start range and end range of side A & B are multi-valued

thanks,

Karuna

[gcusello]

Hi @karunagaraprabh ,

using the rex command you can extract the value to use in calculations.

Ciao.

Giuseppe

[karunagaraprabh]

Hi @gcusello @to4kawa 

the above solution didnt worked for me, am  using splunk7.0.1 and i just started learning splunk


I need to filter the data based on printed timestamp where i need to find the difference of days with indexed timestamp of Xml file and display the columns as 0 if it falls on same day else 1 if it days difference is more than or equal to 1

so i provided the timestramp condtion as
if(round(((indextime-SideA-printedtimestamp)/86400),0)=0),1,0)

i need the output like this

RollID	customer-job-id	start-range-Side-A-printed-timestamp	start-range-Side-B-printed-timestamp	end-range-Side-A-printed-timestamp	end-range-Side-B-printed-timestamp
RollId-1	Customer-1	based on FilterCondition it should be 1 or 0	based on FilterCondition it should be 1 or 0	based on FilterCondition it should be 1 or 0	based on FilterCondition it should be 1 or 0
RollId-1	Customer-2	0	1	1	1

the difference of epoc dates shows me blank even for converted timestamp

eval  diff=(printedtimestrampB-printedtimestrampA)
something am doing in wrong way  

i tried the below query to find the result.

(index=*) "jobs.job.job-manifest.start-range.side-a.printed-timestamp"="*" "jobs.job.job-manifest.start-range.side-b.printed-timestamp"="*"

| rename jobs.job.job-manifest.start-range.side-a.printed-timestamp as "printedtimestrampA" ,jobs.job.job-manifest.start-range.side-b.printed-timestamp As "printedtimestrampB"

| eval printedA_epoch=strptime(printedtimestrampA,"%Y-%m-%dT%H:%M:%S.%Q"),printedB_epoch=strptime(printedtimestrampB,"%Y-%m-%dT%H:%M:%S.%Q")
| eval indextime =_indextime

| eval diffA=indextime-printedA_epoch, diffB=indextime-printedB_epoch
| eval daysA= round((diffA/86400),0) , daysB= round((diffB/86400),0)
|eval  diff=(printedtimestrampB-printedtimestrampA)

| table host as customer,printedA_epoch,printedB_epoch,indextime,diffA,diffB,daysA,daysB,diff

below is the screen shot for reference,

even i tried using rex but could get proper result.

[gcusello]

Hi @karunagaraprabh ,
as I said, if you have a multivalue field, you cannot do calculations, so you have to define if you want to take for calculation one of the epochtimes (e.g. the first one) or split your event in as many events as the number of values in printedA_epoch.

In the first case use something like my above hint:

your_search
| rename ...
| rex field=printedA_epoch "^(?<printedA_epoch>\d+)"
| eval indextime=_indextime
| eval fdata=round(((indextime-printedA_epoch)/86400),0)
| eval diffdata=indextime-printedA_epoch
| table indextime ...

in the second case, try something like this:

your_search
| spath
| rename jobs.job.job-manifest.end-range.side-a.printed-timestamp AS printedA_timestamp
| eval printedA_epoch=strptime(printedA_timestamp,"%Y-%m-%dT%H:%M:%S.%3N-%z")
| mvexpand printedA_epoch
| eval indextime=_indextime
| eval fdata=round(((indextime-printedA_epoch)/86400),0)
| eval diffdata=indextime-printedA_epoch
| table indextime printedA_epoch fdata diffdata

Ciao.

Giuseppe

[to4kawa]

|makeresults
| eval _raw="<roll-summary>
<output-roll-id>a0065584</output-roll-id>
</roll-summary>
<jobs>
<job>
<customer-job-id>customerJob1</customer-job-id>
<printed-on-roll>complete</printed-on-roll>
<job-manifest>
<start-range>
<sequence>0</sequence>
<side-a>
<printed-timestamp>2020-06-17T05:06:07.157-0400</printed-timestamp>
</side-a>
<side-b>
<printed-timestamp>2020-06-17T05:06:22.291-0400</printed-timestamp>
</side-b>
</start-range>
<end-range>
<sequence>1</sequence>
<side-a>
<printed-timestamp>2020-06-17T05:06:07.492-0400</printed-timestamp>
</side-a>
<side-b>
<printed-timestamp>2020-06-17T05:06:22.797-0400</printed-timestamp>
</side-b>
</end-range>
</job-manifest>
<content-metadata/>
</job>
<job>
<customer-job-id>startup-calibration</customer-job-id>
<printed-on-roll>complete</printed-on-roll>
<job-manifest>
<start-range>
<sequence>0</sequence>
<side-a>
<printed-timestamp>2020-06-17T05:06:07.633-0400</printed-timestamp>
</side-a>
<side-b>
<printed-timestamp>2020-06-17T05:06:22.818-0400</printed-timestamp>
</side-b>
</start-range>
<end-range>
<sequence>1</sequence>
<side-a>
<printed-timestamp>2020-06-17T05:06:19.146-0400</printed-timestamp>
</side-a>
<side-b>
<printed-timestamp>2020-06-17T05:06:36.658-0400</printed-timestamp>
</side-b>
</end-range>
</job-manifest>
<content-metadata/>
</job>
</jobs>"
| spath jobs.job output=job
| stats count by job
| spath input=job
| fields - job count