CODEWITHSUNDEEP
splunksplunk-query
Clement Ros
Clement Ros

Reputation: 329

Splunk search - How to loop on multi values field

My use case is analysing ticket in order to attribute a state regarding all the status of a specific ticket.

Raw data look like this :

Id Version Status Event Time
0001 1 New 2021-01-07T09:14:00Z
0001 1 Completed - Action Performed 2021-01-07T09:38:00Z

Data looks like this after transaction command:

Id Version Status Event Time state
0001, 0001 1, 1 New, Completed - Action Performed 2021-01-07T09:14:00Z, 2021-01-07T09:38:00Z Acknowlegdement, Work

I'm using transcation command in order to calculate the duration of acknnowlegdement and resolution of the ticket.

I have predefine rule to choose the correct state. This rules compare the n-1 status (New), and the current status (Completed - Action Performed) to choose the state.

Issue

Each ticket has a different number of status. We can not know in advance the max status number. I can not write a static search comparing each value of the Status field.

Expected Solution

I have a field that inform me the number of index on the status (number of status of a ticket) field.

I want to use a loop (Why not a loop for), to iterate on each index of the field Status and compare the value i-1 and i.

I can not find how to do this. Is this possible ?

Thank you

Upvotes: 0

Views: 2313

Answers (1)

warren
warren

Reputation: 33473

Update to reflect more details

Here's a method with streamstats that should get you towards an answer:

index=ndx sourcetype=srctp Id=* Version=* Status=* EventTime=* state=*
| eval phash=sha256(Version.Status)
| sort 0 _time
| streamstats current=f last(phash) as chash by Id state
| fillnull value="noprev"
| eval changed=if(chash!=phash OR chash="noprev","true","false")
| search NOT changed="false"
| table *

original answer

Something like the following should work to get the most-recent status:

index=ndx sourcetype=srctp Id=* Version=* Status=* EventTime=* state=*
| stats latest(Status) as Status latest(Version) as Version latest(state) state latest(EventTime) as "Event Time" by Id

edit in light of mentioning g the transaction command

Don't use transaction unless you really really really need to.

99% of the time, stats will accomplish what transaction does faster and more efficiently.

For example:

index=ndx sourcetype=srctp Id=* Version=* Status=* EventTime=* state=*
| stats earliest(Status) as eStatus latest(Status) as lStatus earliest(Version) as eVersion latest(Version) as lVersion earliest(status) as estate latest(state) lstate earliest(EventTime) as Opened latest(EventTime) as MostRecent by Id

Will yield a table you can then manipulate further with eval and such. Eg (presuming the time format is subtractable (ie still in Unix epoch format)):

| eval ticketAge=MostRecent-Opened
| eval Versions=eVersion+" - "+lVersion
| eval Statuses=eStatus+" - "+lStatus
| eval State=estate+", ",lstate
| eval Opened=strftime(Opened,"%c"), MostRecent=strftime(MostRecent,"%c")
| eval D=if(ticketAge>86400,round(ticketAge/86400),0)
| eval ticketAge=if(D>0,round(ticketAge-(D*86400)),ticketAge)
| eval H=if(ticketAge>3600,round(ticketAge/3600),0)
| eval ticketAge=if(H>0,round(ticketAge-(H*3600)),ticketAge)
| eval M=if(ticketAge>60,round(ticketAge/60),0)
| eval ticketAge=if(M>0,round(ticketAge-(M*60)),ticketAge)
| rename ticketAge as S
| eval Age=D+" days "+H+" hours"+M+" minutes"+S+" seconds"
| table Id Versions Statuses Opened MostRecent State Age
| rename MostRecent as "Most Recent"

Note: I may have gotten the conversion from raw seconds into days, hours, minutes, seconds off - but it should be close

Upvotes: 1

Related Questions