About sk

sk · ‎03-05-2021

Hi @scelikok , I was missing the "earliest=-8d" but no difference in the result. I had to use global=t since the error says false can only be used with windows not time_window's. I'll continue to play around. Thanks for your help!

sk · ‎03-04-2021

@scelikok Thanks for the info about streamstats. This reports the duration and weekly_avg as the same value so it's as if the time_window is being ignored, for some reason. Any clue?

sk · ‎03-03-2021

Hi, I am trying to compare a field (Job duration) with its weekly average. Something is wrong with my join. It is returning only the first row's values from the main search. Here is the query: index="n" | stats values(Job) by Date, Duration, Status | join lower(Job) max=0 [ search index="n" earliest=-8d | stats count(eval(if( Date>relative_time(now(),"-d"),NULL,1))) as weekly_total, sum(eval(if(Date>relative_time(now(),"-d"), 0,Duration))) AS total_duration by Job | eval Weekly_Avg=(total_duration/weekly_total) ] | table Job, Duration, Weekly_Avg, Status | dedup Job Data: Job Duration Date Status A 5 2021-03-03 Success B 9 2021-03-03 Failed A 5 2021-03-02 Success B 8 2021-03-02 Success A 6 2021-03-01 Success B 7 2021-03-01 Success What I want: Job Duration Weekly Avg Status A 5 5.5 Success B 9 7.5 Failed What I get: Job Duration Weekly Avg Status A 5 5.5 Success B 5 (from JobA) 7.5 Success (from Job A) ** Edit: I am finding there are duplicate rows in my data (exactly same data) which is also not helping. TIA

sk · ‎01-13-2021

Thank you @bowesmana ! I realized the line breaks in some text was throwing me off so I added the following as well: | eval query = replace (query, "[\n\r]"," ")

sk · ‎01-12-2021

@bowesmana Appreciate your help. Query I have below. Note that I will now check for "create or replace table" not "drop table". index="x" host=x sourcetype = x | search Query="*create or replace table*" | eval split_queries=split(Query,";") | eval found=mvfilter(match(split_queries,"(?i)create or replace table ((?!_tmp|temp).)*$")) | table _time Query Email Data sample: 4 rows: Query column: 1) CREATE OR REPLACE TABLE 'aa_temp.tablename as select * from tableA 2) DECLARE var1='text'; create or replace table 'dataset.table_tmp' as select * from tableB; 3) Create or replace table dataset_no_delete.table1 as select * from tableC 4) CREATE OR REPLACE TABLE 'aa_temp.tablename as select * from tableA; Create or replace table dataset_no_delete.table1 as select * from tableC I want to return a table with 2 rows (#3 and 4 from above): 1/12/21 1 pm Create or replace table dataset_no_delete.table1 as select * from tableC email@company.com 1/12/21 2 pm CREATE OR REPLACE TABLE 'aa_temp.tablename as select * from tableA; Create or replace table dataset_no_delete.table1 as select * from tableC email@company.com so that we can monitor users/queries who are using "create or replace table" in a certain database.

sk · ‎01-11-2021

We are monitoring users who are deleting tables in our system. We have a field "user_query" which I want to parse by ";". Then for each split result, I want to check for the text "Drop table *". In addition, they are allowed to drop tables that are from a "temp" dataset or have _tmp in the table name. The reason I want to parse each statement by ";" is that if I do a -(*temp.*) or -(*_tmp), it will not return the whole "user_query" but in latter statements, the user may have written "Drop table [tablename_they_should_not_drop]". So basically, I want to split "user_query" by ";" then go through each split result and check for the text "Drop table *" -(*temp.*) or -(*_tmp). If any instances, I want to return the full "user_query" in a table in a Splunk alert. I do not know if this can be done using regex or I need multiple steps to split and then loop through each result. Help please! TIA

sk · ‎01-11-2021

@bowesmana Thank you kindly! This works great!

sk · ‎01-08-2021

Thanks for your information @bowesmana I have tried 1) index="x" host=x source=x sourcetype = x | rename "protoPayload.serviceData.jobCompletedEvent.job.jobStatistics.endTime" as End, "protoPayload.serviceData.jobCompletedEvent.job.jobStatistics.startTime" as Start | spath input=protoPayload.serviceData.jobCompletedEvent.job.jobConfiguration.query.query output=Query path=protoPayload.serviceData.jobCompletedEvent.job.jobConfiguration.query.query | eval Query_Duration=strptime(End,"%Y-%m-%dT%H:%M:%S.%N")-strptime(Start,"%Y-%m-%dT%H:%M:%S.%N") | sort -Query_Duration limit=10 | table Start End "Query" Query_Duration 2) index="x" host=x source=x sourcetype = x | rename "protoPayload.serviceData.jobCompletedEvent.job.jobStatistics.endTime" as End, "protoPayload.serviceData.jobCompletedEvent.job.jobStatistics.startTime" as Start | spath input=protoPayload.serviceData.jobCompletedEvent.job.jobConfiguration.query.query path=Query | eval Query_Duration=strptime(End,"%Y-%m-%dT%H:%M:%S.%N")-strptime(Start,"%Y-%m-%dT%H:%M:%S.%N") | sort -Query_Duration limit=10 | table Start End "Query" Query_Duration But now the Query column on the table is null for all rows. Am I misunderstanding the spath parameters?

sk · ‎01-07-2021

Also, the query value actually ends with " number,\n ...(string is too long)" in the source system logs.

sk · ‎01-07-2021

Query (with some data blocked out): index="x" host=x source=x sourcetype = x | rename "protoPayload.serviceData.jobCompletedEvent.job.jobStatistics.endTime" as End, "protoPayload.serviceData.jobCompletedEvent.job.jobStatistics.startTime" as Start, "protoPayload.serviceData.jobCompletedEvent.job.jobConfiguration.query.query" as "Query" | eval Query_Duration=strptime(End,"%Y-%m-%dT%H:%M:%S.%N")-strptime(Start,"%Y-%m-%dT%H:%M:%S.%N") | sort -Query_Duration limit=5 | table Start End "Query" Query_Duration In the raw data on the search results "protoPayload.serviceData.jobCompletedEvent.job.jobConfiguration.query.query" starts with "MERGE INTO\n `project.dataset.table`... " But in the table format, "Query" ends up as null (only for some rows!) Is the whole "Query" field required? If so I'll have to block out quite a bit of info. Is there anything I should look for in this "Query" field to troubleshoot?

sk · ‎01-07-2021

I am searching for queries that are running over a certain amount of time and displaying start/end time and query in a table. For some rows, the query is null but when I look at the event, the field has a value. The value is very long though. Is there a limit on the number of characters that can be displayed in a field on a table? I have tried using substring to bring back only a few characters. This will work for the values already being displayed but the value I want to see is still null. TIA

Posts	12
Solutions	0
Karma Given	6
Karma Received	1
Member Since	‎01-07-2021

Online Status	Offline
Date Last Visited	‎03-05-2021 09:44 PM

Comparing field with weekly average

Splitting field then searching each result

Field appearing as null in table when too long

Re: Comparing field with weekly average

Re: Comparing field with weekly average

Comparing field with weekly average

Re: Splitting field then searching each result

Re: Splitting field then searching each result

Splitting field then searching each result

Re: Field appearing as null in table when too long

Re: Field appearing as null in table when too long

Re: Field appearing as null in table when too long

Re: Field appearing as null in table when too long

Field appearing as null in table when too long