Appendcols missing data in column

[kmccowen]

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?

[somesoni2 · Accepted solution]

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

[kmccowen]

this works, thanks!

[DalJeanis]

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"

[kmccowen]

Results:

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

[kmccowen]

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

[somesoni2]

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.

[kmccowen]

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

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

[kmccowen]

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

[kmccowen]

So I'm needing to match this formatting