About ShagVT

ShagVT · ‎09-29-2021

This is EXACTLY what I needed. Thank you!

ShagVT · ‎09-29-2021

Hi folks, I'm trying to put together a dash that will help me find MISSING values. So I would like to put together a chart that has rows based on values from a CSV, even if the record has no value. So let's say the data are something simple like this: 06:25 foo 06:27 bar 06:27 foo 06:27 foo And then I want an output that looks like this: Label count bar 1 foo 3 blah 0 Notice that there is no "blah" row in my sample data. I would have a CSV with those three rows (in reality my file will have many rows). I've never used inputlookup for something like this, and I'm wondering if it is even possible. Any help is most welcome!

ShagVT · ‎05-24-2021

I'm trying put together a query to find some outlier events with very long values within a complex structure. index=myindex sourcetype=jsonfile | where len('x.y.z{}.field') > 20 | stats count by x.y.z{}.field This is my first stab at how to do this, but it doesn't return any values, even though I know they are there.

ShagVT · ‎04-23-2021

Your second query here is basically what I was looking for. To make it work as a timechart I ended up doing this: index=indexname event_name=A OR event_name=B OR event_name=C | stats values(event_name) as events earliest(_time) as start latest(_time) as end by session_id | where isnull(mvfind(events,"B")) | eval _time=start | timechart count THANK YOU!!!

ShagVT · ‎04-23-2021

Hey gang - searching for missing data is probably the weakest part of my Splunk skillset. I just have a hard time thinking through how to even write such a query. I have a transaction that usually goes event_name=A, event_name=B, event_name=C. But I'm trying to research a situation where B is missing sometimes. I'd like to be able to build a timechart that shows the number of transactions in which B is missing. The events in transaction are all linked by a common identifier that we'll call session_id. I've looked at the transaction command and I suppose I understand that. index=indexname event_name=A OR event_name=B OR event_name=C | transaction session_id startsWith=A endsWith=C maxspan=10m But I don't know where to go from here.

ShagVT · ‎08-31-2020

@niketn - I don't think subsearch limit is the problem. When I add up the number of rows my query is returning when I have it set for Previous Month for example (earliest=-mon@mon latest=@mon) I see it return 186,180,103 rows ... I'm certainly not running into any 50k limit. That's because my inner query is already running stats to produce the basic numbers coming out, so it's only returning ~15 rows of already aggregated data. Even so, the numbers are very different. It counts 186M rows, while using the "Previous Month" time picker on the outer query returns 1,180,519,727 ... 10x as many. Here's the strangest part. If I test looking at yesterday (Yesterday in the time picker, earliest=-d@d latest=@d for the inner query) the results are absolutely identical. So it seems like my dashboard is more or less correct. So I don't understand why the exact same logic doesn't work when i use a longer time period.

ShagVT · ‎08-31-2020

Well, my goal is to report over different time periods. I just wanted to check that the dashboard was working correctly so I tried setting the time periods to the same thing. I posted about this approach in two other tickets as well: https://community.splunk.com/t5/Splunk-Search/Compare-percentages-with-a-week-ago/m-p/513807#M144204 https://community.splunk.com/t5/Splunk-Search/Using-a-different-time-picker-on-an-inner-query/m-p/516048#M144939 What I'm trying to do doesn't seem like it would be such an edge case of functionality, but maybe it is. I think the multisearch is ultimately just going to make the root problem worse.

ShagVT · ‎08-31-2020

I don't know about that. I ran this just now. <basequery> | stats min(_time) as min, max(_time) as max, count | eval max=strftime(max, "%Y/%m/%d %T.%3Q") | eval min=strftime(min, "%Y/%m/%d %T.%3Q") | table min max count Results with "previous week" time picker 2020/08/23 00:00:00.078 2020/08/29 23:59:59.999 249617143 Results with "earliest=-w@w latest=@w" 2020/08/23 00:00:00.000 2020/08/29 23:59:59.999 263824202 So it's not a matter of the dates not lining up ... the dates appear to lineup *almost* precisely (just a difference of 0.078 seconds in the min). I don't understand why the exact same query would produce such different results in count.

ShagVT · ‎08-31-2020

Presets > Relative > Previous Week

ShagVT · ‎08-31-2020

I have a query trying to compare two different time periods, which I do with an inner search ( | append [search <identical query>] ). But I've run into a curious problem. If I search over the same period of time in both the inner and outer searches, I get different results. So if I set the time picker (ie, driving the outer query) to "last week" and my inner query to earliest=-w@w latest=@w, it seems like they should produce identical results since they are identical queries running over identical time periods in the past. Just looking at the results: Outer query Time picker = Last Week 152,286,377 records Inner query Query time params: earliest=-w@w latest=@w 144,081,130 records

ShagVT · ‎08-25-2020

@isoutamo - thanks for the link. I don't see how to make that work. How would I connect the time picker only to the inner search? In that example, it looks like there are two separate queries in which the entire query is driven by its own picker, so I think that is solving a different problem.

ShagVT · ‎08-25-2020

I'm working on dashboard in which I would like to compare data across two different time periods. (I posted a previous question here: https://community.splunk.com/t5/Splunk-Search/Compare-percentages-with-a-week-ago/m-p/513799#M144200) I would like to have two time pickers on my dashboard. The first would be for time period 1 and the second would be for time period 2. I have much of this worked out conceptually ... but I don't see how to have the second time picker work for the inner query. In its simplest form it would look something like this: <base query> | append [search <base query> $timePicker2$] | <collate data> The question is how to make that timePicker2 actually work. I have this working with just a dropdown that includes a handful of preset values like earliest=-169h@h latest=-168h@h to be "same hour last week" but if i wanted to make it more flexible with a time picker, I don't understand how to make that work.

ShagVT · ‎08-25-2020

For other intrepid developers, here is the approach I have worked out which seems to have worked pretty well. Again, event_status and event_name are my own fields. The results are a little more complex as well ... they show both the absolute counts as well as the percentages. Also, having the time controls on the inner search allowed me to create a dropdown on a dashboard that included a number of predefined selections for timing by creating a field that contains values like "earliest=-169h@h latest=-168h@h" which I can then insert as a simple variable like $previousTimeWindow$ in place of those fields in the inner query. <base query> | fields + event_name, event_status, _time | fields - _raw | stats count(eval(event_status=="SUCCESS")) as SuccessCount, count(eval(event_status=="FAILED")) as FailedCount by event_name | eval "Percent Success" = round(SuccessCount/(SuccessCount+FailedCount)*100, 1)."%" | eval ReportKey="Current" | append [ search <base query> earliest=-169h@h latest=-168h@h | fields + event_name, event_status, _time | fields - _raw | stats count(eval(event_status=="SUCCESS")) as SuccessCount, count(eval(event_status=="FAILED")) as FailedCount by event_name | eval "Percent Success" = round(SuccessCount/(SuccessCount+FailedCount)*100, 1)."%" | eval ReportKey="Previous"] | chart values(SuccessCount), values(FailedCount), values("Percent Success") by event_name, ReportKey | rename "values(FailedCount): Current" as "Current Failed", "values(FailedCount): Previous" as "Previous Failed", "values(Percent Success): Current" as "Current Success Rate", "values(Percent Success): Previous" as "Previous Success Rate", "values(SuccessCount): Current" as "Current Success", "values(SuccessCount): Previous" as "Previous Success", "event_name" as "Event Name" | fields "Event Name", "Current Success", "Current Failed", "Current Success Rate", "Previous Success", "Previous Failed", "Previous Success Rate"

ShagVT · ‎08-12-2020

I saw this. Unfortunately in our locked down corporate environment I don't think I can install apps like this.

ShagVT · ‎08-12-2020

Hello all, I'm trying to put together a dashboard that - among other things - compares the success rate of various transactions over the last hour with the same hour a week ago. My base search results in rows that have two fields I particularly care about: event_name and event_status. My desired outcome would look something like this: event_name Last hour Last Week event1 95% 96% event2 85% 41% event3 72% 100% event4 25% 69% Here is the current query i have, which seems to basically work. <base query> earliest=-169h@h latest=now | fields + event_name, event_status, _time | fields - _raw | eval weekAgoHour = relative_time(now(), "-168h@h") | eval lastHour = relative_time(now(), "-1h@h") | eval ReportKey = "omit" | eval ReportKey = case(_time < weekAgoHour,"Last Week", _time > lastHour,"Last Hour") | where ReportKey != "omit" | eventstats count(eval(event_status=="FAILED")) as FailedCount, count(eval(event_status=="SUCCESS")) as SuccessCount by event_name, ReportKey | eval pctSuccess = round(SuccessCount/(SuccessCount+FailedCount)*100, 1)."%" | chart values(pctSuccess) by event_name, ReportKey The problem here is that it has to look at hundreds of millions of irrelevant rows ... everything that has happened in the last 169 hours. Surely there must be a more efficient way to do this? Maybe with multisearch?

ShagVT · ‎02-19-2020

I have a bunch of sourcetypes which are supposed to contain only valid JSON data. I've been asked to verify that in fact they do contain only json. Is there an easy/elegant way to search to find records which were not well-formed JSON? (ie, records that Splunk can automatically format as a JSON tree.)

ShagVT · ‎08-13-2019

Thanks - I never think of the foreach function. I modified slightly to end up with X since that's a little easier to spot when the chart has more values: | foreach * [|eval "<<FIELD>>"=if("<<FIELD>>" != "host" AND '<<FIELD>>'=0, "", '<<FIELD>>')] | foreach * [|eval "<<FIELD>>"=if('<<FIELD>>' = 1, "X", '<<FIELD>>')]

ShagVT · ‎08-13-2019

I have a search that will produce a pretty basic table like this: index=myindex | chart count by host, partition host partition1 partition2 serverA 453 0 serverB 23 23 serverC 0 231 I'm trying to make a dashboard, and for my dashboard, the specific values are not really important. host partition1 partition2 serverA X serverB X X serverC X I would be fine with something like this, or a graphical checkmark or something? I'm just looking to show where the non-zero values are. Anything additional I can do to bring attention to those through color-coding etc would be gravy. Any suggestions?

ShagVT · ‎05-29-2019

Also - is there anyway to designate in my query that I want everything in pacific time, rather than having to change my account settings?

ShagVT · ‎05-29-2019

So changing my account to pacific time, removing the eval line, and using date_hour gave me the results I was looking for, which is good. Do you mind explaining what span=1h@h does? I'm not familiar with that notation.

ShagVT · ‎05-29-2019

I've been asked to produce a report with typical hourly volumes for our application on Fridays. So I put together this snappy search. index=prod event_name="LOGIN" date_wday=friday | eval hour=strftime(_time, "%H") | timechart cont=false span=1d limit=0 useother=false count by hour I ran this year to date. Things are close but not quite right because my data are collected in pacific time but I'm in eastern time. I would like to generate the report in pacific time (which the data were written/collected in.) It seems like all three lines of my search might be wrong: - Does date_wday look at Friday pacific time or Friday eastern time? - I'm pretty sure the eval is printing the hour in eastern time - Do I need to change my timechart with something like aligntime=@d-3h? When I run my search I see hours 3-23 showing up on Friday and hours 0-2 showing up on Saturday.

ShagVT · ‎04-12-2019

Yes! Thank you! I guess I've done timestamp parsing so long in Java and SQL that something like %YYYY just came out automatically. And great catch on the %Z for timestampB.

ShagVT · ‎04-12-2019

It looks like you copied Vijeta's bad format with H:S:M? Even fixing that format though, I still get the wrong answer? I think Harshpatel missed the second important detail ... the trailing Z on timestampB also needs a % to get the timezone correct. Otherwise is it just using my local timezone? (EDT) That seems to make sense of the result ... since EDT is 4 hours behind GMT. 60*60*4 = 14400, the amount the answer is off.

ShagVT · ‎04-12-2019

harshpatel has a working solution below which is very similar to yours. I think the problem is your timestamp format ... you have H:S:M instead of H:M:S?

ShagVT · ‎04-12-2019

timestampA and timestampB were created neither by fieldformat nor eval ... I'm extracting them using rex. I'm not sure how that works under the covers. When I'm looking at the results of your query, it seems to end up with the same problem as the previous answer ... it comes up with estrdiff = -14399.929000. But from simply looking at the timestamps, it clear that the correct answer is 71 ms, so I'm not sure where the disconnect is?

Posts	36
Solutions	0
Karma Given	12
Karma Received	0
Member Since	‎01-21-2016

Online Status	Offline
Date Last Visited	‎09-29-2021 04:56 PM

Create dash with predefined row labels

Filter events by length of json field

Counting sessions with missing events

Inner/outer search results don't match

Using a different time picker on an inner query

Compare percentages with a week ago

How to find non-json records

How to format a table

How to produce hourly stats by day of the week in ...

Trouble comparing timestamps

Re: Create dash with predefined row labels

Create dash with predefined row labels

Filter events by length of json field

Re: Counting sessions with missing events

Counting sessions with missing events

Re: Inner/outer search results don't match

Re: Inner/outer search results don't match

Re: Inner/outer search results don't match

Re: Inner/outer search results don't match

Inner/outer search results don't match

Re: Using a different time picker on an inner quer...

Using a different time picker on an inner query

Re: Compare percentages with a week ago

Re: Compare percentages with a week ago

Compare percentages with a week ago

How to find non-json records

Re: How to format a table

How to format a table

Re: How to produce hourly stats by day of the week...

Re: How to produce hourly stats by day of the week...

How to produce hourly stats by day of the week in ...

Re: Trouble comparing timestamps

Re: Trouble comparing timestamps

Re: Trouble comparing timestamps

Re: Trouble comparing timestamps