Splunk Search

Only include certain rows in appendcol- need help building search

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

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=itsnowcallkiosklogsweekly datemonth=August dateyear=2017 (assignmentgroup="") | 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 contacttype | join overwrite=false contacttype [search index=itsnowcallkiosklogsweekly earliest="6/01/2017:00:00:00" latest="12/31/2017:24:00:00" (assignmentgroup="") | 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 contacttype time | stats avg(Tickets) as Baseline by contacttype | eval Baseline = round(Baseline,0)] | eval "Baseline Variance" = Tickets - Baseline | join overwrite=false contacttype [search index=itsnowcallkiosklogsweekly earliest=-3mon@mon (assignmentgroup="") | 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 contacttype time | stats avg(Tickets) as Average by contacttype | 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

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

Path Finder

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

0 Karma

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