Splunk Search

Sequence the order of 6 sepearte Charts in one Column Chart

kmccowen
Path Finder
index=ctap host=sc58* sourcetype=gateway "CTIPOP CALL RECEIVED"  |  chart  count as "Total" by sourcetype
|  appendcols [search index=ctap host=sc58* sourcetype=gateway "CTIPOP CALL RECEIVED" acct="*" | chart count as "Acct Found"]
| appendcols [search index=ctap host=sc58* sourcetype=gateway "CTIPOP CALL RECEIVED" NOT acct="*" | chart count as "Acct not Found"]
| appendcols [search  index=ctap host=sc58* sourcetype=gateway "CTIPOP CALL RECEIVED" | stats dc(acct) as "Unique Acct Pop"]
| appendcols [search index=ctap host=sc58* sourcetype=gateway "ACCOUNT VERIFIED"  acct="*" | chart count as "Acct Verified"]
| appendcols [search index=ctap host=sc58* sourcetype=gateway "ACCOUNT VERIFIED"  acct="*" | stats dc(acct) as "Unique Acct Verified" | sort  count | reverse]

I have several queries appended together to create 6 seperate charts. I'm unable to figure out a way to sort the charts in the order I've been asked to create.

The order of the charts should be:
Total, Account Found, Account Not Found, Unique Acct Pop, Acct Verified, Unique Acct Verified.

Any ideas?

Tags (3)
0 Karma
1 Solution

woodcock
Esteemed Legend

It alphabetizes them so you will have to pre-pad the names of the fields with something that sorts better like this:

index=ctap host=sc58* sourcetype=gateway "CTIPOP CALL RECEIVED"  |  chart  count as "Total" by sourcetype
|  appendcols [search index=ctap host=sc58* sourcetype=gateway "CTIPOP CALL RECEIVED" acct="*" | chart count as "    Acct Found"]
| appendcols [search index=ctap host=sc58* sourcetype=gateway "CTIPOP CALL RECEIVED" NOT acct="*" | chart count as "   Acct not Found"]
| appendcols [search  index=ctap host=sc58* sourcetype=gateway "CTIPOP CALL RECEIVED" | stats dc(acct) as "  Unique Acct Pop"]
| appendcols [search index=ctap host=sc58* sourcetype=gateway "ACCOUNT VERIFIED"  acct="*" | chart count as " Acct Verified"]
| appendcols [search index=ctap host=sc58* sourcetype=gateway "ACCOUNT VERIFIED"  acct="*" | stats dc(acct) as "Unique Acct Verified" | sort  count | reverse]

View solution in original post

kmccowen
Path Finder

I could not get the numbers to match up using the Eval statements so I went with my original query and used the padding to sort the charts.

0 Karma

alacercogitatus
SplunkTrust
SplunkTrust

Do you have some sample data I could see? I'd still like to get it to work, since using 6 searches is highly inefficient.

0 Karma

alacercogitatus
SplunkTrust
SplunkTrust

Wow, these searches are inefficient. Appending columns like that isn't a best practice. Let's combine them, yeah?

index=ctap host=sc58* sourcetype=gateway "CTIPOP CALL RECEIVED" OR "ACCOUNT VERIFIED"
  | eventstats count as "Total" 
  | eval acct_found = if(isnotnull(acct),"yes","no") 
  | eval acct_verified = if(match(_raw,"ACCOUNT VERIFIED"),"yes","no") 
  | eventstats latest(acct_found) as dcacctf latest(acct_verified) as dcacctv by acct 
  | fillnull acct value="not_found" | streamstats count(eval(if(acct=="not_found",acct,NULL))) as acct_found by acct
  | stats latest(Total) as Total count(eval(match(dcacctf,"yes"))) as "Account Found" latest(acct_found) as "Account Not Found" dc(acct) as "Unique Acct Pop" count(eval(match(dcacctv,"yes"))) as "Acct Verified" dc(eval(if(dcacctv=="yes",acct,NULL))) as "Unique Acct Verified"

So this is long, but it uses a SINGLE search to pull the information and then a series of eventstats ans stats to do the counting.
If you have some sample data, and this search doesn't work, post it as a comment, and I'll refine it on my own instance to make it work in this manner. The additional benefit? Ordered columns!!!

kmccowen
Path Finder

I'm still working on this. The evals are not working properly as my "account Not found" is always coming up zero. Here's where I'm at.

index=ctap host=sc58* sourcetype=gateway "CTIPOP CALL RECEIVED" | eventstats count as "Total" | eval acct_found = if(isnull(acct),"yes","no") | eventstats latest(acct_found) as dcacctf | stats latest(Total) as Total count(eval(match(dcacctf,"yes"))) as "Account Found" count(eval(match(dcacctf,"no"))) as "Account Not Found"

output:
Total Account Found Account Not Found
92795 0 92795

0 Karma

alacercogitatus
SplunkTrust
SplunkTrust

Ah, I know why. Because I'm doing a "by" clause, it will only actually match if acct is defined. If it is not defined, then we have a problem. I've updated the answer to provide for this.

0 Karma

alacercogitatus
SplunkTrust
SplunkTrust

If this answers the question, please mark it accepted. Thanks!

0 Karma

kmccowen
Path Finder

I was wondering just that..thanks for expaining. There were some missing ")" that I added into your query to make it run but the results are returning zero for "account not found" which I know is not correct. Here is the output:

query:
index=ctap host=sc58* sourcetype=gateway "CTIPOP CALL RECEIVED" OR "ACCOUNT VERIFIED"| eventstats count as "Total" | eval acct_found = if(isnotnull(acct),"yes","no") | eval acct_verified = if(match(_raw,"ACCOUNT VERIFIED"),"yes","no") | eventstats latest(acct_found) as dcacctf latest(acct_verified) as dcacctv by acct | stats latest(Total) as Total count(eval(match(dcacctf,"yes"))) as "Account Found" count(eval(match(dcacctf,"no"))) as "Account Not Found" dc(acct) as "Unique Acct Pop" count(eval(match(dcacctv,"yes"))) as "Acct Verified" dc(eval(if(dcacctv=="yes",acct,NULL))) as "Unique Acct Verified"

Output:

Total Account Found Account Not Found Unique Acct Pop Acct Verified Unique Acct Verified
125200 112255 0 63131 56196 25252

0 Karma

woodcock
Esteemed Legend

It alphabetizes them so you will have to pre-pad the names of the fields with something that sorts better like this:

index=ctap host=sc58* sourcetype=gateway "CTIPOP CALL RECEIVED"  |  chart  count as "Total" by sourcetype
|  appendcols [search index=ctap host=sc58* sourcetype=gateway "CTIPOP CALL RECEIVED" acct="*" | chart count as "    Acct Found"]
| appendcols [search index=ctap host=sc58* sourcetype=gateway "CTIPOP CALL RECEIVED" NOT acct="*" | chart count as "   Acct not Found"]
| appendcols [search  index=ctap host=sc58* sourcetype=gateway "CTIPOP CALL RECEIVED" | stats dc(acct) as "  Unique Acct Pop"]
| appendcols [search index=ctap host=sc58* sourcetype=gateway "ACCOUNT VERIFIED"  acct="*" | chart count as " Acct Verified"]
| appendcols [search index=ctap host=sc58* sourcetype=gateway "ACCOUNT VERIFIED"  acct="*" | stats dc(acct) as "Unique Acct Verified" | sort  count | reverse]

kmccowen
Path Finder

This worked! Thanks!

0 Karma
Get Updates on the Splunk Community!

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 ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...