Splunk Search

Only include certain rows in appendcol- need help building search

katzr
Path Finder

So i am trying to convert some of my searches from joins to appendcol to improve performance but I am running into some problems.

I can't figure out how to create a table in this question- so just read the first row like Field=A, Baseline=100, Week1=103, Week2=105

Field Baseline Week1 Week2
A 100 103 105
B 50 54 56
C 20

Originally- I was using join to pull in baseline because baseline is based on historical trends, while week1 and week2 are the most recent weeks. To improve performance- I want to change to using something else, but when I use appendcol- field value C is pulled in based on the historical baseline even when they don't have values in week1 and week2.

I only want to display the baseline for A and B because they have data in week1/2. Is there anyway to control this with appendcols?

This is a very simplified example so I need a somewhat generic solution or idea.

Thanks for the help!

0 Karma

katzr
Path Finder

Here is the query below- for @DalJeanis. It looks pretty messy and the reason I have weird things going on with my location information is because for any location that does not match my lookup- location, Country, and Region to be filled as Unknown. And unfortunately, I was getting a weird error with using a lookup table and setting a default value so I had to do it manually.

Are there any ways to improve the efficiency of this search and make it run quicker?

index=it_snow_call_kiosk_logs_weekly date_month=August date_year=2017 (assignment_group="") | dedup number | fillnull value="UNKNOWN" location | eval regionblank= "UNKNOWN" | eval countryblank= "UNKNOWN" | eval locationblank="UNKNOWN" | lookup CurrentSiteInfo.csv location| eval site=coalesce(location2,locationblank) | eval Region=coalesce(Region,regionblank)| eval Country=coalesce(Country,countryblank) | search ((Region="") (Country="") (site="")) |stats count as Tickets by contact_type | join overwrite=false contact_type [search index=it_snow_call_kiosk_logs_weekly earliest="6/01/2017:00:00:00" latest="12/31/2017:24:00:00" (assignment_group="") | dedup number | fillnull value="UNKNOWN" location | eval regionblank= "UNKNOWN" | eval countryblank= "UNKNOWN" | eval locationblank="UNKNOWN" | lookup CurrentSiteInfo.csv location| eval site=coalesce(location2,locationblank) | eval Region=coalesce(Region,regionblank)| eval Country=coalesce(Country,countryblank) | search ((Region="") (Country="") (site=""))| bucket _time span=1mon | stats count as Tickets by contact_type _time | stats avg(Tickets) as Baseline by contact_type | eval Baseline = round(Baseline,0)] | eval "Baseline Variance" = Tickets - Baseline | join overwrite=false contact_type [search index=it_snow_call_kiosk_logs_weekly earliest=-3mon@mon (assignment_group="") | dedup number | fillnull value="UNKNOWN" location | eval regionblank= "UNKNOWN" | eval countryblank= "UNKNOWN" | eval locationblank="UNKNOWN" | lookup CurrentSiteInfo.csv location| eval site=coalesce(location2,locationblank) | eval Region=coalesce(Region,regionblank)| eval Country=coalesce(Country,countryblank) | search ((Region="") (Country="") (site="")) | bucket _time span=1mon | stats count as Tickets by contact_type _time | stats avg(Tickets) as Average by contact_type | eval Average = round(Average,0)] | eval "Average Variance" = Tickets - Average | table contact_type Tickets Baseline "Baseline Variance" Average "Average Variance" | addcoltotals | sort 0 Tickets

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

Appendcols is almost never the answer to any reasonable question.

You would be much better off, if your query is slow, posting the query up here for the community to critique and offer suggestions. Most joins can be switched to another format and get runtime improvements.

A second good strategy would be creating a summary index that holds only the information you need, so that you aren't running the query repeatedly across all time.

Either of those strategies is more likely to bear fruit than

0 Karma

katzr
Path Finder

I added my query above- could you look at it when you get a chance? Thanks for the help!

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

@katzr - I put my comments on the other question you posted the search on. It can be simplified fairly easily
to a single join. The earliest/lastest and the sections with ... need to be coded to meet your specifications for Baseline and Average.

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...