How to find difference of date in same table as another column?

[nikhil29]

I have 1 table having current date when in maintenance and another is last date when it started and looking new value with difference of 2 date without Join.

[PickleRick]

There is surely more than one approach to this. I'd probably firstly split the date between two different fields

<your search>
| eval maintenance_date=if(role_status="MAINTENANCE_MODE",date,null())
| eval start_date=if(role_status="STARTED",date,null())

Then you can join several rows (depending on your use case it might be with transaction or some stats) and have separate fields on which you can easily calculate difference.

[pstout2]

You want to use the delta search command. First, invert the sort order otherwise your maintenance mode entry will have nothing against which it can compare.

This assumes the dates in epoch time as your example suggests.

Then,  use the delta command a=on the date field. Remember, because you inverted the order these numbers will now be negative.

Finally, search for anything older than 2 days (86400s * 2) and not 0. 

Here's a straw man search:

| index=<your_index> sourcetype=<your_sourcetype>
| sort - date
| delta date AS date_diff
| search date_diff < 172800 AND date_diff != 0

[nikhil29]

I tried your work around but i want services is in maintenance mode from how many days when it was STARTED and then it went to MAINTENANCE_MODE that time difference.

But here first column reflecting blank and days calculating wrong as well

It would be really appreciate if someone can help me please

[nikhil29]

I have one more doubt how splunk will and  calculate the date difference if it will sort properly like Maintenance Started alternative.

please see the blow screenshot and help me.

[nikhil29]

will not sort alternative*

[nikhil29]

Actually I want to know from how long role is in maintenance mode like from last 2 days or more than that.

[nikhil29]

Would someone please help me ?