Splunk Search

Appendcols missing data in column

kmccowen
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

somesoni2
SplunkTrust
SplunkTrust

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

somesoni2
SplunkTrust
SplunkTrust

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
Path Finder

this works, thanks!

0 Karma

DalJeanis
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

kmccowen
Path Finder

Results:

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

0 Karma

kmccowen
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

somesoni2
SplunkTrust
SplunkTrust

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

kmccowen
Path Finder

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

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

0 Karma

kmccowen
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

kmccowen
Path Finder

So I'm needing to match this formatting

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...