Are you trying to perform the stats by _time also ? Just add your event_time into the stats command.  Change the event_time format to only Hour and Minute or just by hour ? index=* | eval event_time=strftime(_time, "%Y-%m-%d %H:%M:%S") | stats count by event_time, user, ip, action | iplocation ip | sort -count ... View more

I would say the first thing to look at is what are the different soucetype's in the index ? index=foo | stats count by sourcetype Then that will give you some kind of idea of what is being ingested for the index you have ? Then if the sourcetype is named that it indicated the sourcetype's log's you can then look at the sources ? index=foo | stats count by sourcetype,source  This would give you an idea of what is in the index ? ... View more

I have a follow up question.  What I am trying to figure out is how to introduce 'time' into the results so that I can create a graph showing each ranges calculated percentage and the relative time for that range. If each range has a min and max time how would I go about showing the results as: aRange(1), MaxTime (1), aPercentage(1) aRange(2), MaxTime (2), aPercentage(2) aRange(...), MaxTime (...), aPercentage(...) aRange(8), MaxTime (8), aPercentage(8) Here is the query im working with: index=anIndex sourcetype=aSourcetype aString1 OR aString2 earliest=-481m@m latest=-1m@m | addinfo | eval age=info_max_time - _time | eval age_ranges=split("1,6,11,31,61,91,121,241",",") | eval aRange=null() | eval aType = case(match(_raw,"aString1"), 0, match(_raw,"aString2"), 1, true(),9) | foreach 0 1 2 3 4 5 6 7 [ eval r=tonumber(mvindex(age_ranges, <<FIELD>>))*60, zone=if(age < 14400 + r AND age > r, <<FIELD>>, null()), aRange=mvappend(aRange, zone) ] | where isnotnull(aRange) | chart count over aRange by aType | eval aRange = aRange+1 | rename 0 AS A, 1 AS B | eval aPercentage = round((A / B) * 100) | table aRange, A,B, aPercentage I have tried putting an eventstats before the chart count and use the indexes 0 to 7 but have not been able to get it to produce the result im looking for ? ... View more

Thanks for all the little cleanup suggestions.  They were something I was going to get to after I got the first iteration working. I am going to put them into my notes for later... I have incorporated them along with getting rid of the appendcols (I was aware of a single search looking for both strings, then doing a if / case / match to determine the 'type' of event). I was not aware of the 'fix' to the range index # but like the adding of 1 to get rid of 0 indexing. Then, what I wanted was the % of the # of A / B, so at the end: | eval percentage{range} = round(A / B) * 100) | stats values(percentage*) AS percentage*   All tested for the first scenario.  I am working on one more enhancement where I have the same 8 'windows' but have 6 different sampling that range for 5 different 4 hour samples but using decreasing window sizes. (4Hr, 2Hr, 1.5Hr, 1Hr, 30Min)  then the last is still a 4 Hr sample but staring with a 5 min window then a 10Min, 15Min, 30Min, 60Min, 90Min, 120Min, 240Min window. Thanks for the help and suggestions ! ... View more

It does. I understand high level what its doing but will need to walk through the specifics although It does get me where I needed to be. Here is what I ended up with: index=anIndex sourcetype=aSourceType aString1 earliest=-481m@m latest=-1m@m | eval age=now() - _time | eval age_ranges=split("1,6,11,31,61,91,121,241",",") | foreach 0 1 2 3 4 5 6 7 [ eval r=tonumber(mvindex(age_ranges, <<FIELD>>))*60, zone=if(age < 14400 + r AND age > r, <<FIELD>>, null()), aString1Count=mvappend(aString1Count, zone) ] | stats count by aString1Count | transpose 8 header_field=aString1Count | rename 0 AS "string1Window1", 1 AS "string1Window2", 2 AS "string1Window3", 3 AS "string1Window4", 4 AS "string1Window5", 5 AS "string1Window6", 6 AS "string1Window7", 7 AS "string1Window8" | appendcols [search index=anIndex sourcetype=aSourceType aString2 earliest=-481m@m latest=-1m@m | eval age=now() - _time | eval age_ranges=split("1,6,11,31,61,91,121,241",",") | foreach 0 1 2 3 4 5 6 7 [ eval r=tonumber(mvindex(age_ranges, <<FIELD>>))*60, zone=if(age < 14400 + r AND age > r, <<FIELD>>, null()), aString2Count=mvappend(aString2Count, zone) ] | stats count by aString2Count | transpose 8 header_field=aString2Count | rename 0 AS "string2Window1", 1 AS "string2Window2", 2 AS "string2Window3", 3 AS "string2Window4", 4 AS "string2Window5", 5 AS "string2Window6", 6 AS "string2Window7", 7 AS "string2Window8" ] | table string1Window* string2Window* string1Window1 string1Window2 string1Window3 ... 44 42 40 ... ... View more

I have made some progress and this is where I am at. index=anIndex sourcetype=aSourceType aString earliest=-481m latest=-1m | eval aWindow = case ( (_time > relative_time(now(),"-241m@m") AND (_time < relative_time(now(),"-1m@m"))),1, (_time > relative_time(now(),"-246m@m") AND (_time < relative_time(now(),"-6m@m"))),2, (_time > relative_time(now(),"-251m@m") AND (_time < relative_time(now(),"-11m@m"))),3, (_time > relative_time(now(),"-271m@m") AND (_time < relative_time(now(),"-31m@m"))),4, (_time > relative_time(now(),"-301m@m") AND (_time < relative_time(now(),"-61m@m"))),5, (_time > relative_time(now(),"-331m@m") AND (_time < relative_time(now(),"-91m@m"))),6, (_time > relative_time(now(),"-361m@m") AND (_time < relative_time(now(),"-121m@m"))),7, (_time > relative_time(now(),"-481m@m") AND (_time < relative_time(now(),"-241m@m"))),8, true(),9) | stats count by aWindow but I have realized that using a case statement allows each log event to exist in one window, when the windows overlap and one log event can exist in more than one window ? I am working on a dashboard for 8 widgets that currently do the exact same query, just a different window. So I am trying to make one query that has all data for the calculation, then in the widget(s) previously mentioned use $query.1$ to retrieve the result from the base reusable query. So, how to I handle counting in these overlapping windows ? ... View more

A Couple of changes from your last image.  Notice the change in evaluating time variables.  now(), strptime instead of strftime: You could also remove the eval = aHostMatch... code if you are filtering the hosts in the initial TSTATS. | tstats count where index=cts-dcpsa-app sourcetype=app:dcpsa host_ip IN (xx.xx.xxx.xxx, xx.xx.xxx.xxx) by host | eval currTime = now() ```<- I was not getting a value when using _time with TSTATS ? ``` | eval excluded_start_time=strptime("2024-03-16 18:25:00", "%Y-%m-%d %H:%M") | eval excluded_stop_time=strptime("2024-03-16 18:30:00", "%Y-%m-%d %H:%M") | eval is_maintenance_window=if(currTime >= excluded_start_time AND currTime <= excluded_stop_time,1,0) | eval aHostMatch = case( match(host,"HOSTNAME1"),1, ```<- Case Sensitive``` match(host,"HOSTNAME2"),1, ```<- Case Sensitive``` true(),0) ```| where count == 0 AND is_maintenance_window == 1 AND aHostMatch ==1``` | table host count excluded_start_time, currTime, excluded_stop_time, is_maintenance_window, aHostMatch Also, if a host is not reporting data (down) you will not have a row returned from your initial query and no row for that host for when you check ( where a count == 0 ) TSTATS does not support multiple timeframes... Another approach is to not use tstats and use a stats count First query: (earliest=-30m@m latest=-15m@m) to count historical entries, then a second query to get current entries (earliest=-14m@m latest=-1m@m), then compare historical counts and current counts by host index=cts-dcpsa-app host=HOSTNAME1 OR host=HOSTNAME2 earliest=-30m@m latest=-15m@m | stats count AS aHistCount by host | appendcols [ search index = cts-dcpsa-app host=HOSTNAME1 OR host=HOSTNAME2 earliest=-14m@m latest=-1m@m | stats count AS aCurrCount by host | table host, aCurrCount ] | table host, aHistCount, aCurrCount ... View more

When your testing just keep in mind that this is the time from the log event. | eval current_time=_time While this is the current time now, when the alert is running. So, depending upon your lookback period (earliest= latest=) you might be picking up log events outside (prior or after) your outage window start time/end time.  | eval current_time=now() But, if you dont want any alerts during the outage window now() should be the correct time to be using for your triggering conditions ... View more

All I can say is use now() instead of _time to use in the evaluation on whether to trigger or not on the solution provided earlier ? Do you have any test data to show your attribute values to help figure out why its false triggering ? | eval current_time=now() | eval excluded_start_time=strptime("2024-04-14 21:00:00", "%Y-%m-%d %H:%M:%S") | eval excluded_end_time=strptime("2024-04-15 04:00:00", "%Y-%m-%d %H:%M:%S") | eval is_maintenance_window=if(current_time >= excluded_start_time AND current_time < excluded_end_time, 1, 0) ... View more

Your trigger condition is the same it was before? | where is_maintenance window = 0 AND is_server_down=1 Im assuming your maintenance window is on a specific day of the week ? April 23rd is a Tuesday, is your maintenance window is every Tuesday night/Wed morning ? Introduce a new attribute for day of the week: | tstats count where index=cts-dcpsa-app sourcetype=app:dcpsa host_ip IN (xx.xx.xxx.xxx, xx.xx.xxx.xxx) by host | eval current_time=strftime(now(), "%H%M") | eval aDayNumber = strftime(now(), "%w") | eval is_maintenance_window=if((aDayNumber = 2 AND current_time >= 2100) OR (aDayNumber = 3 AND current_time < 0400), 1, 0) | eval is_server_down=if(count == 0, 1, 0) | where is_maintenance window = 0 AND is_server_down=1   ... View more

Try this: | tstats count where index=cts-dcpsa-app sourcetype=app:dcpsa host_ip IN (xx.xx.xxx.xxx, xx.xx.xxx.xxx) by host | eval current_time=strftime(now(), "%H%M") | eval is_maintenance_window=if(current_time >= 2100 AND current_time < 0400, 1, 0) | eval is_server_down=if(count == 0, 1, 0) | where is_maintenance window = 0 AND is_server_down=1 ... View more

Find out what the current time is then compare to you window times: | eval timeNow = strftime(now(), "%H%M") | where (timeNow < 2350 AND timeNow > 0015) ```Outside of main. window```   ... View more

That makes sense and I was wanting to create some additional fields for the output and was getting hung up on the usage of | stats and had to switch it to | eventstats to retain _raw data for the rest of the code after the stats/eventstats. You have helped me before PickleRick and always provide good info !!! Works like a charm, thanks again ! | rex field=_raw "Batch::(?<aJobName>[^\s]*)" | eval aStatus=case( searchmatch("START of script"), "Start", searchmatch("COMPLETED OK"), "End", searchmatch("ABORTED, exiting with status"), "End", true(),null() ) | eventstats values(aStatus) as aStateList by aJobName | where aStateList != "End" |........ ... View more

I was stuck on trying to get Transaction to work 😉  It was on my list of things to do, to write it similar to the way you did but had not had the time to get to it.  I ran a few tests and appears to solve the 'issue' I dont know the specifics but I guess trying to 'alter' _time really does not change the underlying value ? ... View more

Sure.. So, here it goes.. I have a dashboard that is tracking 'jobs'...  Completed jobs and this particular widget is tracking 'running' jobs (start but no end).  I might be tracking around 80 jobs but there should not be more than 5 or 6 'running' at any particular time.  So, not creating 80 transactions. Everything is working as designed but this one job that starts and ends at the same time showed up in my 'running' jobs widget and then is missing from my completed jobs widget. Once I run my initial 'search' for log events here is what im doing. index=anIndex sourcetype=aSourcetype (aJob1 OR aJob2 OR aJob3) AND ("START of script" OR "COMPLETED OK" OR "ABORTED, exiting with status" ) | rex field=_raw "Batch::(?<aJobName>[^\s]*)" | transaction keeporphans=true host aJobName startswith=("START of script") endswith=("COMPLETED OK" OR "ABORTED, exiting with status") | eval closed_txn = if ( isnull(closed_txn),0,closed_txn) | search closed_txn=0 | sort _time | eval aDay = strftime(_time, "%a. %b. %e, %Y") | eval aStartTime=strftime(_time, "%H:%M:%S %p") | eval aDuration=tostring((now()-_time), "duration") | eval aEndTime = "--- Running ---" | table aHostName aDay aJobName aStartTime aEndTime aDuration But, this one job is causing me issues as Transaction is not picking up the start/end that have the same _time ... View more

Putting maxspan option does work for the one particular event where the start/stop events happen at the same time.   The next issue that comes up is that there are around 80 "transactions" that I am monitoring that can have a duration of over an hour. The only way I can think of making this work is to have two different transaction creation lines that are inside of a case statement?   One with the maxspan and one without depending upon a job name that I am extracting earlier in my code... Is that possible or do you have any other ideas/suggestions ? ... View more

I know I tried latest(...) but like you mentioned I removed anAction from the split and am now seeing only the latest action for each user with no duplicated user ID in the results. Thanks!!! ... View more

I had to re-think the way I was showing my job info in the UI as a orphaned Transaction could mean two different things.  i.e. 'running' start with no end message or multiple starts along with modifying my initial SPL to look for an aborted message when a failure occurred. It was quite a learning experience is I went through all different permutations of keeporphans=true/false keepevicted=true/false and then look at the ending transaction data. I did notice that closed_txn defaults to null and not 0 and had to write some code to make it default to 0. eval closed_txn = if ( isnull(closed_txn,0, closed_txn) search closed_txn=0 to find those transactions that only has a START log event... Thanks for all you input as it gave me a different perspective from the way I was looking at it.  I was also to rewrite some of my existing SPL to have fewer lines. ... View more

Ok, my SPL is getting smaller.. But here is what is happening with the current scenario im testing with. I have 3 jobs.  Two ran with no issues, each has a start & complete log event (No issues) The 3rd job has two start events and one complete event. Since it failed once, was restarted and then completed. When I use:   | Transaction  keeporphans=true host batchName startwith="start" endswith="completed" I end up with three transaction where closed_txn=1 and one where closed_txn is not defined or 0... for a total of 4 transactions. But, what I want to see in the resulting table is all jobs that have run to completion (closed_txn=1) and then any jobs that are currently running (closed_txn=0) or not defined. How would I go about eliminating from my results the 4th transaction when I have another transaction with the same jobName that has closed_txn=1 and then not remove it (since its still running) when I dont have a tranaction that has closed_txn=1 ? My Events: aJobName1 START aJobName2 START aJobName1 COMPLETE aJobName3 START aJobName2 START aJobName3 COMPLETE aJobName2 COMPLETE ... View more