get process time and group by status

[hazemfarajallah]

Hello everyone
I'm using this query
`|eval Status = case (eventId="endProcess" ,"Completed" ,eventId="error","Terminated")
|stats earliest(when) AS startTime latest(when) AS endTime by mainprocessname ,resourceName
|eval startTime = strftime( strptime( startTime, "%Y-%m-%dT%H:%M:%S.%7NZ"), "%Y-%m-%d %H:%M:%S")
|eval endTime = strftime( strptime( endTime, "%Y-%m-%dT%H:%M:%S.%7NZ"), "%Y-%m-%d %H:%M:%S")

|table startTime, endTime , mainprocessname,Status ,resourceName
wondering how to set the status in the table? if i use it as by with Stats earliest, I will have only the result from the same event (it will effect startTime and endtime so its in event level )
any idea how to add the status to the table ? or should I change me eval status
OR how to get the status from the event with the same sessionid
here is sample of my data
currprocessid: b0bb7d67-aed1-4877-8340-65d1a4bdf87d
currprocessname: 17 - Dödsbo inkorg - Skapa kö
currprocesstype: 0
eventId: endSubSheetRef
mainprocessid: b0bb7d67-aed1-4877-8340-65d1a4bdf87d
mainprocessname: 17 - xxxxx
pageid: 00000000-0000-0000-0000-000000000000
pagename: Main Page
resourceName: HP20082212
sessionNumber: 3313
sessionid: 615d19b9-1f01-4a60-b524-fe7ac0c1b360
stageid: 6b293685-aea0-4394-bfc6-3e9147aa6775
stagename: xxSAP
when: 2019-08-16T09:44:08.1262931Z`

[gaurav_maniar · Accepted solution]

Hi,

The stats command produces a statistical summarization of data. The reason your Status field doesn't appear in your table command is because stats summarized your primary search into a smaller result set containing only a count for each value of fields provided with stats after by.

What you might do is use the values() stats function to include Status values in your result set.

|eval Status = case (eventId="endProcess" ,"Completed" ,eventId="error","Terminated")
|stats earliest(when) AS startTime, latest(when) AS endTime, values(Status) AS Status by mainprocessname ,resourceName
|eval startTime = strftime( strptime( startTime, "%Y-%m-%dT%H:%M:%S.%7NZ"), "%Y-%m-%d %H:%M:%S")
|eval endTime = strftime( strptime( endTime, "%Y-%m-%dT%H:%M:%S.%7NZ"), "%Y-%m-%d %H:%M:%S")
|table startTime, endTime , mainprocessname, Status, resourceName

Accept & Upvote the answer if it helps.

Happy splunking............!!!

[hazemfarajallah]

Hi,
thanks for answering,
its works only if the right status on the same event where is Start time or endTime,

is there is any way to check the event during the All the process from start to finish?
like loop through them then add to the table ?

Thanks in advance .

[gaurav_maniar]

Hi,

I didn't understand your question.
I guess you want group events by starttime and endtime.

Instead of using stats, you can group your events by transaction command.
After the transaction command apply your status logic and use it in table.

For example - https://docs.splunk.com/Documentation/Splunk/7.3.1/SearchReference/Transaction
https://docs.splunk.com/Documentation/SplunkCloud/7.2.6/Search/Identifyandgroupeventsintotransaction...

Accept & Upvote the answer if it helps.

[hazemfarajallah]

Thanks, i managed with Stats having one problem left
during the process running |eval Status = case (eventId="endProcess" ,"Completed" ,eventId="error","Terminated")'
how to set default value?
i tried to use not equal but then there is many not equal and the Status cell will have to value ex. ´Completed then under 'Running ´ cant remove running from the cell after an update .

[gaurav_maniar]

|eval Status = case (eventId="endProcess" ,"Completed" ,eventId="error","Terminated", 1=1, "your_default_value")

[hazemfarajallah]

hello Gaurav, i tried 1=1, but the problem is cell value not overwritten,, it show two rows Running completed.