topic Re: get process time and group by status in Splunk Search

get process time and group by status

hazemfarajallah — Fri, 16 Aug 2019 10:17:13 GMT

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 ,resourceNamewondering 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`

Re: get process time and group by status

gaurav_maniar — Fri, 16 Aug 2019 14:24:28 GMT

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............!!!

Re: get process time and group by status

hazemfarajallah — Mon, 19 Aug 2019 06:55:38 GMT

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 .

Re: get process time and group by status

gaurav_maniar — Mon, 19 Aug 2019 09:40:46 GMT

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/Identifyandgroupeventsintotransactions

Accept & Upvote the answer if it helps.

Re: get process time and group by status

hazemfarajallah — Mon, 19 Aug 2019 13:44:16 GMT

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 .

Re: get process time and group by status

gaurav_maniar — Mon, 19 Aug 2019 14:46:57 GMT

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

Re: get process time and group by status

hazemfarajallah — Tue, 20 Aug 2019 06:58:22 GMT

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