topic Re: Appendcols missing data in column in Splunk Search

Appendcols missing data in column

kmccowen — Tue, 29 Sep 2020 12:38:31 GMT

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?

Re: Appendcols missing data in column

somesoni2 — Tue, 31 Jan 2017 22:35:00 GMT

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.

Re: Appendcols missing data in column

DalJeanis — Wed, 01 Feb 2017 03:34:38 GMT

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"

Re: Appendcols missing data in column

kmccowen — Wed, 01 Feb 2017 13:18:12 GMT

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

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

Re: Appendcols missing data in column

kmccowen — Wed, 01 Feb 2017 13:25:10 GMT

Results:

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

Re: Appendcols missing data in column

kmccowen — Tue, 29 Sep 2020 12:38:52 GMT

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

Re: Appendcols missing data in column

kmccowen — Wed, 01 Feb 2017 13:30:46 GMT

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

Re: Appendcols missing data in column

kmccowen — Wed, 01 Feb 2017 13:32:24 GMT

So I'm needing to match this formatting

Re: Appendcols missing data in column

somesoni2 — Wed, 01 Feb 2017 16:53:45 GMT

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

Re: Appendcols missing data in column

kmccowen — Wed, 01 Feb 2017 17:01:47 GMT

this works, thanks!