Splunk Search
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Ask a Question

How to convert check-ins index into date then calculate desync for each day?

Sanjana
Explorer
‎09-26-2022 06:48 AM

Hello,

I have data like below. 

{"property":"XYZ", "period":{ "start":"2022-09-16", "end":"2022-10-02" }, "nb-day":17, "nb-rate-plans":518, "nb-products":16, "total":{ "avail":48, "price":0 }, "filtered":{ "avail":0, "price":0 }, "rate-plans":{ "IWU35":{ "avail":16, "price":0 }, "IWU30":{ "avail":16, "price":0 }, "IWU40":{ "avail":16, "price":0 } }, "check-ins":{ "0":{ "avail":3, "price":0 }, "1":{ "avail":3, "price":0 }, "2":{ "avail":3, "price":0 }, "3":{ "avail":3, "price":0 }, "4":{ "avail":3, "price":0 }, "5":{ "avail":3, "price":0 }, "6":{ "avail":3, "price":0 }, "7":{ "avail":3, "price":0 }, "8":{ "avail":3, "price":0 }, "9":{ "avail":3, "price":0 }, "10":{ "avail":3, "price":0 }, "11":{ "avail":3, "price":0 }, "12":{ "avail":3, "price":0 }, "13":{ "avail":3, "price":0 }, "14":{ "avail":3, "price":0 }, "15":{ "avail":3, "price":0 } } }

{ "property":"ABC", "period":{ "start":"2022-09-16", "end":"2022-10-02" }, "nb-day":17, "nb-rate-plans":518, "nb-products":16, "total":{ "avail":48, "price":0 }, "filtered":{ "avail":0, "price":0 }, "rate-plans":{ "IWU35":{ "avail":16, "price":0 }, "IWU30":{ "avail":16, "price":0 }, "IWU40":{ "avail":16, "price":0 } }, "check-ins":{ "0":{ "avail":3, "price":0 }, "1":{ "avail":3, "price":0 }, "2":{ "avail":3, "price":0 }, "3":{ "avail":3, "price":0 }, "4":{ "avail":3, "price":0 }, "5":{ "avail":3, "price":0 }, "6":{ "avail":3, "price":0 }, "7":{ "avail":3, "price":0 }, "8":{ "avail":3, "price":0 }, "9":{ "avail":3, "price":0 }, "10":{ "avail":3, "price":0 }, "11":{ "avail":3, "price":0 }, "12":{ "avail":3, "price":0 }, "13":{ "avail":3, "price":0 }, "14":{ "avail":3, "price":0 }, "15":{ "avail":3, "price":0 } } }

1. Need to calculate date based on below example->

start : 2022-09-16 

"check-ins":{ "0":{ "avail":3, "price":0 }, "1":{ "avail":3, "price":0 }, "2":{ "avail":3, "price":0 }, "3":{ "avail":3, "price":0 }, "4":{ "avail":3, "price":0 }, "5":{ "avail":3, "price":0 }, "6":{ "avail":3, "price":0 }, "7":{ "avail":3, "price":0 }, "8":{ "avail":3, "price":0 }, "9":{ "avail":3, "price":0 }, "10":{ "avail":3, "price":0 }, "11":{ "avail":3, "price":0 }, "12":{ "avail":3, "price":0 }, "13":{ "avail":3, "price":0 }, "14":{ "avail":3, "price":0 }, "15":{ "avail":3, "price":0 } }

Index from check-ins need to be added in start-date

Date:                                                                          desync

2022-09-16  + 0 = 2022-09-16                   avail+price(3+0)

2022-09-16  + 1 =2022-09-17

2022-09-16  + 2= 2022-09-18

2022-09-16  + 15 = 2022-10-01

I need to convert check-ins index into date and then calculate desync for each day

Thanks in advance!!

 

Labels (5)
Labels
0 Karma
Reply

yuanliu
SplunkTrust
SplunkTrust
‎09-26-2022 02:35 PM

I assume that the JSON data is already extracted into "property", "period.start", "check-ins.0.avail", "check-ins.15.price", etc.  If not, use spath to do so.

 

| eval period_start = strptime('period.start', "%F"), period_end = strptime('period.end', "%F")
| eval days_in_period = round((period_end - period_start) / 86400)
| foreach check-ins.*.avail
    [eval desync = mvappend(desync, 'period.start' . " + " . <<MATCHSTR>> . ":avail+price(" . '<<FIELD>>' . "+" . 'check-ins.<<MATCHSTR>>.price' . ")")]
| mvexpand desync
| eval desync = split(desync, ":")
| eval Date = mvindex(desync, 0), desync = mvindex(desync, 1)
| table property Date desync

 

This will give you

 
propertyDatedesync
XYZ2022-09-16 + 0avail+price(3+0)
XYZ2022-09-16 + 1avail+price(3+0)
XYZ2022-09-16 + 10avail+price(3+0)
XYZ2022-09-16 + 11avail+price(3+0)
XYZ2022-09-16 + 12avail+price(3+0)
XYZ2022-09-16 + 13avail+price(3+0)
XYZ2022-09-16 + 14avail+price(3+0)
XYZ2022-09-16 + 15avail+price(3+0)
XYZ2022-09-16 + 2avail+price(3+0)
XYZ2022-09-16 + 3avail+price(3+0)
XYZ2022-09-16 + 4avail+price(3+0)
XYZ2022-09-16 + 5avail+price(3+0)
XYZ2022-09-16 + 6avail+price(3+0)
XYZ2022-09-16 + 7avail+price(3+0)
XYZ2022-09-16 + 8avail+price(3+0)
XYZ2022-09-16 + 9avail+price(3+0)
ABC2022-09-16 + 0avail+price(3+0)
ABC2022-09-16 + 1avail+price(3+0)
ABC2022-09-16 + 10avail+price(3+0)
ABC2022-09-16 + 11avail+price(3+0)
ABC2022-09-16 + 12avail+price(3+0)
ABC2022-09-16 + 13avail+price(3+0)
ABC2022-09-16 + 14avail+price(3+0)
ABC2022-09-16 + 15avail+price(3+0)
ABC2022-09-16 + 2avail+price(3+0)
ABC2022-09-16 + 3avail+price(3+0)
ABC2022-09-16 + 4avail+price(3+0)
ABC2022-09-16 + 5avail+price(3+0)
ABC2022-09-16 + 6avail+price(3+0)
ABC2022-09-16 + 7avail+price(3+0)
ABC2022-09-16 + 8avail+price(3+0)
ABC2022-09-16 + 9avail+price(3+0)
0 Karma
Reply

Sanjana
Explorer
‎09-26-2022 09:21 PM

Hey @yuanliu 

 

Thanks for the quick response.

But I think there is some miscommunication about the expected result.

Could you please help me with actual result after calculation. The calculation part (avai+price) not needed to display in result. just the final answer. Total value of after adding avail and price value should come with corresponding date

propertyDatedesync
XYZ2022-09-16 3
Tags (1)
0 Karma
Reply

yuanliu
SplunkTrust
SplunkTrust
‎09-26-2022 10:01 PM

I thought I was missing something from the description as it didn't feel natural😃.  Calculating values is simpler than tripping over those strings, actually.

 

| eval period_start = strptime('period.start', "%F"), period_end = strptime('period.end', "%F")
| eval days_in_period = round((period_end - period_start) / 86400)
| foreach check-ins.*.avail
    [eval desync = mvappend(desync, period_start + <<MATCHSTR>> * 86400 . ":" . '<<FIELD>>' + 'check-ins.<<MATCHSTR>>.price')]
| eval desync = mvsort(desync)
| mvexpand desync
| eval desync = split(desync, ":")
| eval Date = strftime(mvindex(desync, 0), "%F"), desync = mvindex(desync, 1)
| table property Date desync

 

Output would be

 
propertyDatedesync
XYZ2022-09-1630
XYZ2022-09-1730
XYZ2022-09-1830
XYZ2022-09-1930
XYZ2022-09-2030
XYZ2022-09-2130
XYZ2022-09-2230
XYZ2022-09-2330
XYZ2022-09-2430
XYZ2022-09-2530
XYZ2022-09-2630
XYZ2022-09-2730
XYZ2022-09-2830
XYZ2022-09-2930
XYZ2022-09-3030
XYZ2022-10-0130
ABC2022-09-1630
ABC2022-09-1730
ABC2022-09-1830
ABC2022-09-1930
ABC2022-09-2030
ABC2022-09-2130
ABC2022-09-2230
ABC2022-09-2330
ABC2022-09-2430
ABC2022-09-2530
ABC2022-09-2630
ABC2022-09-2730
ABC2022-09-2830
ABC2022-09-2930
ABC2022-09-3030
ABC2022-10-0130

(Now that bowesmana helped me figure out https://community.splunk.com/t5/Splunk-Search/Why-did-Splunk-9-0-1-quot-Fail-to-parse-templatized-se... I am working on a semantically clearer code.  But this should work.)

0 Karma
Reply

Sanjana
Explorer
‎09-26-2022 10:15 PM

Hello @yuanliu 

I am getting error  Field 'desync' does not exist in the data, using above query.

I think desync used in mvappend function is not in my data. 

[eval desync = mvappend(desync, period_start + <<MATCHSTR>> * 86400 . ":" . '<<FIELD>>' + 'check-ins.<<MATCHSTR>>.price')]

 

0 Karma
Reply

yuanliu
SplunkTrust
SplunkTrust
‎09-26-2022 10:21 PM

@Sanjana In Splunk, any undefined variable is initialized as null().  So, desync starts with value null(), then with each <<FIELD>> iteration, mvappend adds a non-null value to the array.  You can test this with

| makeresults
| eval f = mvappend(null(), "a", "b")

Resultant f should contain two values, ("a", "b").

Here is my full test code with simulated data as you illustrated:

| makeresults
| eval data=mvappend("{\"property\":\"XYZ\", \"period\":{ \"start\":\"2022-09-16\", \"end\":\"2022-10-02\" }, \"nb-day\":17, \"nb-rate-plans\":518, \"nb-products\":16, \"total\":{ \"avail\":48, \"price\":0 }, \"filtered\":{ \"avail\":0, \"price\":0 }, \"rate-plans\":{ \"IWU35\":{ \"avail\":16, \"price\":0 }, \"IWU30\":{ \"avail\":16, \"price\":0 }, \"IWU40\":{ \"avail\":16, \"price\":0 } }, \"check-ins\":{ \"0\":{ \"avail\":3, \"price\":0 }, \"1\":{ \"avail\":3, \"price\":0 }, \"2\":{ \"avail\":3, \"price\":0 }, \"3\":{ \"avail\":3, \"price\":0 }, \"4\":{ \"avail\":3, \"price\":0 }, \"5\":{ \"avail\":3, \"price\":0 }, \"6\":{ \"avail\":3, \"price\":0 }, \"7\":{ \"avail\":3, \"price\":0 }, \"8\":{ \"avail\":3, \"price\":0 }, \"9\":{ \"avail\":3, \"price\":0 }, \"10\":{ \"avail\":3, \"price\":0 }, \"11\":{ \"avail\":3, \"price\":0 }, \"12\":{ \"avail\":3, \"price\":0 }, \"13\":{ \"avail\":3, \"price\":0 }, \"14\":{ \"avail\":3, \"price\":0 }, \"15\":{ \"avail\":3, \"price\":0 } } }",
"{ \"property\":\"ABC\", \"period\":{ \"start\":\"2022-09-16\", \"end\":\"2022-10-02\" }, \"nb-day\":17, \"nb-rate-plans\":518, \"nb-products\":16, \"total\":{ \"avail\":48, \"price\":0 }, \"filtered\":{ \"avail\":0, \"price\":0 }, \"rate-plans\":{ \"IWU35\":{ \"avail\":16, \"price\":0 }, \"IWU30\":{ \"avail\":16, \"price\":0 }, \"IWU40\":{ \"avail\":16, \"price\":0 } }, \"check-ins\":{ \"0\":{ \"avail\":3, \"price\":0 }, \"1\":{ \"avail\":3, \"price\":0 }, \"2\":{ \"avail\":3, \"price\":0 }, \"3\":{ \"avail\":3, \"price\":0 }, \"4\":{ \"avail\":3, \"price\":0 }, \"5\":{ \"avail\":3, \"price\":0 }, \"6\":{ \"avail\":3, \"price\":0 }, \"7\":{ \"avail\":3, \"price\":0 }, \"8\":{ \"avail\":3, \"price\":0 }, \"9\":{ \"avail\":3, \"price\":0 }, \"10\":{ \"avail\":3, \"price\":0 }, \"11\":{ \"avail\":3, \"price\":0 }, \"12\":{ \"avail\":3, \"price\":0 }, \"13\":{ \"avail\":3, \"price\":0 }, \"14\":{ \"avail\":3, \"price\":0 }, \"15\":{ \"avail\":3, \"price\":0 } } }")
| mvexpand data
| spath input=data
| eval period_start = strptime('period.start', "%F"), period_end = strptime('period.end', "%F")
| eval days_in_period = round((period_end - period_start) / 86400)
| foreach check-ins.*.avail
    [eval desync = mvappend(desync, period_start + <<MATCHSTR>> * 86400 . ":" . '<<FIELD>>' + 'check-ins.<<MATCHSTR>>.price')]
| mvexpand desync
| eval desync = split(desync, ":")
| eval Date = strftime(mvindex(desync, 0), "%F"), desync = mvindex(desync, 1)
| table property Date desync
0 Karma
Reply

Sanjana
Explorer
‎09-27-2022 01:42 AM

Hey @yuanliu 

Yes correct.

With sample data output is coming as expected. But when I am using on my actual data then error comes as desync does not exist.

Not sure why................

 

 

0 Karma
Reply

yuanliu
SplunkTrust
SplunkTrust
‎09-27-2022 09:51 AM

I examined your updates in the other thread.  It seems that your actual data may include some outer keys that envelopes the JSON object posted above, such as "event" and "kIndexKey_EventMessage".  Is this correct?  Each envelope requires an additional path.

Suppose your actual data structure is like

{"event": {"kIndexKey_EventMessage": {<the posted JSON}}

your code needs to provide the full paths.  If you do not, the foreach command will produce no output, therefore you get the Field 'desync' does not exist error in mvexpand command.

One method to resolve path is as proposed by kamlesh, use path=event.kIndexKey_EventMessage in spath.  If your raw data is already parsed, however, you can also simply reference full path in the filters, e.g.,

 

| eval period_start = strptime('content.kIndexKey_EventMessage.period.start', "%F"), period_end = strptime('content.kIndexKey_EventMessage.period.end', "%F")
| foreach content.kIndexKey_EventMessage.check-ins.*.avail
    [eval desync = mvappend(desync, period_start + <<MATCHSTR>> * 86400 . ":" . '<<FIELD>>' + 'content.kIndexKey_EventMessage.check-ins.<<MATCHSTR>>.price')]
| eval desync = mvsort(desync)
| mvexpand desync
| eval desync = split(desync, ":")
| eval Date = strftime(mvindex(desync, 0), "%F"), desync = mvindex(desync, 1)
| table content.kIndexKey_EventMessage.property Date desync

 

 
content.kIndexKey_EventMessage.propertyDatedesync
XYZ2022-09-1630
XYZ2022-09-1730
XYZ2022-09-1830
XYZ2022-09-1930
XYZ2022-09-2030
XYZ2022-09-2130
XYZ2022-09-2230
XYZ2022-09-2330
XYZ2022-09-2430
XYZ2022-09-2530
XYZ2022-09-2630
XYZ2022-09-2730
XYZ2022-09-2830
XYZ2022-09-2930
XYZ2022-09-3030
XYZ2022-10-0130
ABC2022-09-1630
ABC2022-09-1730
ABC2022-09-1830
ABC2022-09-1930
ABC2022-09-2030
ABC2022-09-2130
ABC2022-09-2230
ABC2022-09-2330
ABC2022-09-2430
ABC2022-09-2530
ABC2022-09-2630
ABC2022-09-2730
ABC2022-09-2830
ABC2022-09-2930
ABC2022-09-3030
ABC2022-10-0130

Here is my data simulation

 

 makeresults
| eval data=mvappend("{\"content\":
{\"kIndexKey_EventMessage\":
{\"property\":\"XYZ\", \"period\":{ \"start\":\"2022-09-16\", \"end\":\"2022-10-02\" }, \"nb-day\":17, \"nb-rate-plans\":518, \"nb-products\":16, \"total\":{ \"avail\":48, \"price\":0 }, \"filtered\":{ \"avail\":0, \"price\":0 }, \"rate-plans\":{ \"IWU35\":{ \"avail\":16, \"price\":0 }, \"IWU30\":{ \"avail\":16, \"price\":0 }, \"IWU40\":{ \"avail\":16, \"price\":0 } }, \"check-ins\":{ \"0\":{ \"avail\":3, \"price\":0 }, \"1\":{ \"avail\":3, \"price\":0 }, \"2\":{ \"avail\":3, \"price\":0 }, \"3\":{ \"avail\":3, \"price\":0 }, \"4\":{ \"avail\":3, \"price\":0 }, \"5\":{ \"avail\":3, \"price\":0 }, \"6\":{ \"avail\":3, \"price\":0 }, \"7\":{ \"avail\":3, \"price\":0 }, \"8\":{ \"avail\":3, \"price\":0 }, \"9\":{ \"avail\":3, \"price\":0 }, \"10\":{ \"avail\":3, \"price\":0 }, \"11\":{ \"avail\":3, \"price\":0 }, \"12\":{ \"avail\":3, \"price\":0 }, \"13\":{ \"avail\":3, \"price\":0 }, \"14\":{ \"avail\":3, \"price\":0 }, \"15\":{ \"avail\":3, \"price\":0 } } }
}}",
"{\"content\":
{\"kIndexKey_EventMessage\":
{ \"property\":\"ABC\", \"period\":{ \"start\":\"2022-09-16\", \"end\":\"2022-10-02\" }, \"nb-day\":17, \"nb-rate-plans\":518, \"nb-products\":16, \"total\":{ \"avail\":48, \"price\":0 }, \"filtered\":{ \"avail\":0, \"price\":0 }, \"rate-plans\":{ \"IWU35\":{ \"avail\":16, \"price\":0 }, \"IWU30\":{ \"avail\":16, \"price\":0 }, \"IWU40\":{ \"avail\":16, \"price\":0 } }, \"check-ins\":{ \"0\":{ \"avail\":3, \"price\":0 }, \"1\":{ \"avail\":3, \"price\":0 }, \"2\":{ \"avail\":3, \"price\":0 }, \"3\":{ \"avail\":3, \"price\":0 }, \"4\":{ \"avail\":3, \"price\":0 }, \"5\":{ \"avail\":3, \"price\":0 }, \"6\":{ \"avail\":3, \"price\":0 }, \"7\":{ \"avail\":3, \"price\":0 }, \"8\":{ \"avail\":3, \"price\":0 }, \"9\":{ \"avail\":3, \"price\":0 }, \"10\":{ \"avail\":3, \"price\":0 }, \"11\":{ \"avail\":3, \"price\":0 }, \"12\":{ \"avail\":3, \"price\":0 }, \"13\":{ \"avail\":3, \"price\":0 }, \"14\":{ \"avail\":3, \"price\":0 }, \"15\":{ \"avail\":3, \"price\":0 } } }
}}")
| mvexpand data
| rename data AS _raw

 

_raw
{"content": {"kIndexKey_EventMessage": {"property":"XYZ", "period":{ "start":"2022-09-16", "end":"2022-10-02" }, "nb-day":17, "nb-rate-plans":518, "nb-products":16, "total":{ "avail":48, "price":0 }, "filtered":{ "avail":0, "price":0 }, "rate-plans":{ "IWU35":{ "avail":16, "price":0 }, "IWU30":{ "avail":16, "price":0 }, "IWU40":{ "avail":16, "price":0 } }, "check-ins":{ "0":{ "avail":3, "price":0 }, "1":{ "avail":3, "price":0 }, "2":{ "avail":3, "price":0 }, "3":{ "avail":3, "price":0 }, "4":{ "avail":3, "price":0 }, "5":{ "avail":3, "price":0 }, "6":{ "avail":3, "price":0 }, "7":{ "avail":3, "price":0 }, "8":{ "avail":3, "price":0 }, "9":{ "avail":3, "price":0 }, "10":{ "avail":3, "price":0 }, "11":{ "avail":3, "price":0 }, "12":{ "avail":3, "price":0 }, "13":{ "avail":3, "price":0 }, "14":{ "avail":3, "price":0 }, "15":{ "avail":3, "price":0 } } } }}
{"content": {"kIndexKey_EventMessage": { "property":"ABC", "period":{ "start":"2022-09-16", "end":"2022-10-02" }, "nb-day":17, "nb-rate-plans":518, "nb-products":16, "total":{ "avail":48, "price":0 }, "filtered":{ "avail":0, "price":0 }, "rate-plans":{ "IWU35":{ "avail":16, "price":0 }, "IWU30":{ "avail":16, "price":0 }, "IWU40":{ "avail":16, "price":0 } }, "check-ins":{ "0":{ "avail":3, "price":0 }, "1":{ "avail":3, "price":0 }, "2":{ "avail":3, "price":0 }, "3":{ "avail":3, "price":0 }, "4":{ "avail":3, "price":0 }, "5":{ "avail":3, "price":0 }, "6":{ "avail":3, "price":0 }, "7":{ "avail":3, "price":0 }, "8":{ "avail":3, "price":0 }, "9":{ "avail":3, "price":0 }, "10":{ "avail":3, "price":0 }, "11":{ "avail":3, "price":0 }, "12":{ "avail":3, "price":0 }, "13":{ "avail":3, "price":0 }, "14":{ "avail":3, "price":0 }, "15":{ "avail":3, "price":0 } } } }}

 

The key point here is: Code must match the actual path to data.

0 Karma
Reply
Get Updates on the Splunk Community!

Enterprise Security Content Update (ESCU) | New Releases

In December, the Splunk Threat Research Team had 1 release of new security content via the Enterprise Security ...

Why am I not seeing the finding in Splunk Enterprise Security Analyst Queue?

(This is the first of a series of 2 blogs). Splunk Enterprise Security is a fantastic tool that offers robust ...

Index This | What are the 12 Days of Splunk-mas?

December 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...