Splunk Search

Appendcols missing data in column

Path Finder

Here is my query:

index search "INFO: ZIP_SEARCH" | stats count as "Uses" by cat_userid cat_role | appendcols[index search "INFO: COMPARE" | stats count as "Compare" by cat_userid cat_role]| appendcols[index search "INFO: COMPETITIVE_INFO" | stats count as "GetGlossary" by cat_userid cat_role]

My Results are missing GetGlossary value of 2

Results:
LTAPIA CARERETENTION 70 3

If I add the USERID LTAPIA to the queries

index search "INFO: ZIP_SEARCH" LTAPIA | stats count as "Uses" by cat_userid cat_role | appendcols[index search "INFO: COMPARE" LTAPIA | stats count as "Compare" by cat_userid cat_role]| appendcols[index search "INFO: COMPETITIVE_INFO" LTAPIA | stats count as "GetGlossary" by cat_userid cat_role]

My results are complete.
LTAPIA CARERETENTION 70 3 2

How can this be?

Tags (1)
0 Karma
1 Solution

Revered Legend

Give this a try

index=cat sourcetype=ctap host=sc58lcatp* source="*.out" ("INFO: ZIP_SEARCH" OR "INFO: COMPARE" OR "INFO: COMPETITIVE_INFO")
| eval Uses=if(searchmatch("INFO: ZIP_SEARCH"),1,0)
| eval Compare=if(searchmatch("INFO: COMPARE"),1,0)
| eval GetGlossary=if(searchmatch("INFO: COMPETITIVE_INFO"),1,0)
| stats sum(Uses) as Uses sum(Compare) as Compare sum(GetGlossary) as GetGlossary by cat_userid cat_role

View solution in original post

Revered Legend

Give this a try

index=cat sourcetype=ctap host=sc58lcatp* source="*.out" ("INFO: ZIP_SEARCH" OR "INFO: COMPARE" OR "INFO: COMPETITIVE_INFO")
| eval Uses=if(searchmatch("INFO: ZIP_SEARCH"),1,0)
| eval Compare=if(searchmatch("INFO: COMPARE"),1,0)
| eval GetGlossary=if(searchmatch("INFO: COMPETITIVE_INFO"),1,0)
| stats sum(Uses) as Uses sum(Compare) as Compare sum(GetGlossary) as GetGlossary by cat_userid cat_role

View solution in original post

Path Finder

this works, thanks!

0 Karma

SplunkTrust
SplunkTrust

Run this and see what we learn. Instead of appending columns, this will create a single record for each of your searches. You'll be able to see whether each search is returning data or not.

index search "INFO: ZIP_SEARCH"  
| stats count as "Uses" by cat_userid cat_role 
| eval test="No LTAPIA", counttype="1 Uses"
| append
   [index search "INFO: COMPARE"  
   | stats count as "Compare" by cat_userid cat_role
   | eval test="No LTAPIA",counttype="2 Compare"
   ]
| append
   [index search "INFO: COMPETITIVE_INFO" 
   | stats count as "GetGlossary" by cat_userid cat_role
   | eval test="No LTAPIA",counttype="3 GetGlossary"
   ]
| append
   [index search "INFO: ZIP_SEARCH" LTAPIA 
   | stats count as "Uses" by cat_userid cat_role 
   | eval test="With LTAPIA", counttype="1 Uses"
   ]
| append
   [index search "INFO: COMPARE" LTAPIA 
   | stats count as "Compare" by cat_userid cat_role
   | eval test="With LTAPIA",counttype="2 Compare"
   ]
| append
   [index search "INFO: COMPETITIVE_INFO" LTAPIA 
   | stats count as "GetGlossary" by cat_userid cat_role
   | eval test="With LTAPIA",counttype="3 GetGlossary" 
   ]
| sort test, counttype, cat_userid, cat_role
| fillnull value="(empty)" "Uses", "Compare", "GetGlossary"
| table test, counttype, cat_userid, cat_role, "Uses", "Compare", "GetGlossary"
0 Karma

Path Finder

Results:

No LTAPIA 1 Uses LTAPIA CARERETENTION 168 (empty) (empty)

0 Karma

Path Finder

index=cat sourcetype=ctap host=sc58lcatp* source="*.out" "INFO: COMPETITIVE_INFO" LTAPIA | stats count as "GetGlossary" by cat_userid cat_role

Results:
LTAPIA CARERETENTION 2

0 Karma

Revered Legend

Do all three search done on same data (index/sourcetype is same but searching different strings)? If yes, can you share the base search portion? You may be able to avoid the appendcols altogether and the query should perform better as well.

0 Karma

Path Finder

Yes, it's the same base query for all three.

index=cat sourcetype=ctap host=sc58lcatp* source="*.out"

0 Karma

Path Finder

I'm trying to recreate a report in Splunk from another application and it's formatted like this.

Parameter Value

StartDate 1/15/2017

EndDate 1/25/2017

UserID SalesChannel Uses Compare GetGlossary
ltapia Careretention 69 2 2
mmslagle Careretention 68 2 23
mpsutter Careretention 64 5 0
tdewey Careretention 46 23 0
akcarpenter Careretention 42 10 0
sklompstra Careretention 42 6 0
khaselhuhn Careretention 37 2 10
aneuens Careretention 36 5 0

0 Karma

Path Finder

So I'm needing to match this formatting

0 Karma
Don’t Miss Global Splunk
User Groups Week!

Free LIVE events worldwide 2/8-2/12
Connect, learn, and collect rad prizes
and swag!