topic Re: How to format stats table results as rows of strings without column headers? in Splunk Search

How to format stats table results as rows of strings without column headers?

kmccowen — Tue, 30 Jun 2015 14:45:40 GMT

Search:

index=ctap host=sc58* sourcetype=gateway "CTIPOP CALL RECEIVED" acct="*" | stats count by acct | eventstats count as sum by count | fields sum,index,count,sourcetype| eval index="ACCOUNT'S HAD"| eval sourcetype="CALL POPS" |rename sum as "NUMBER OF ACCOUNT'S"| rename index as "INFO_1"| rename sourcetype as "INFO_2"|  rename count as "NUMBER OF CALLS"| dedup  "NUMBER OF CALLS" | sort  "NUMBER OF CALLS"

Current output:

     NUMBER OF ACCOUNT'S   INFO_1         NUMBER OF CALLS    INFO_2
1   48024                ACCOUNT'S HAD   1                CALL POPS
2   13460                ACCOUNT'S HAD   2                CALL POPS

I need to figure out a way to turn the above stats into a single string per line without headers. I'm being asked to create this panel to look like text so that each line reads like a complete sentence. I've been unable to figure anything out. Is this even possible?

Desired output:

1   48024  ACCOUNT'S HAD 1 CALL POPS
2   13460  ACCOUNT'S HAD 2 CALL POPS

Re: How to format stats table results as rows of strings without column headers?

woodcock — Tue, 30 Jun 2015 16:15:59 GMT

Like this:

index=ctap host=sc58* sourcetype=gateway "CTIPOP CALL RECEIVED" acct="*" | stats count by acct | eventstats count as sum by count | fields sum,index,count,sourcetype| eval index="ACCOUNT'S HAD"| eval sourcetype="CALL POPS" |rename sum as "NUMBER OF ACCOUNT'S"| rename index as "INFO_1"| rename sourcetype as "INFO_2"| rename count as "NUMBER OF CALLS"| dedup "NUMBER OF CALLS" | sort "NUMBER OF CALLS" | foreach * [eval combo=combo . " " . <<FIELD>>] | fields combo

Re: How to format stats table results as rows of strings without column headers?

kmccowen — Tue, 30 Jun 2015 17:18:19 GMT

Woodcock,
When I run this I get a blank set of results with a header of Combo :(. Maybe i'm on a different version of splunk? I'm on 6.2.2

Re: How to format stats table results as rows of strings without column headers?

woodcock — Tue, 30 Jun 2015 18:01:42 GMT

You are correct; it should work but doesn't; try this variation:

index=ctap host=sc58* sourcetype=gateway "CTIPOP CALL RECEIVED" acct="*" | stats count by acct | eventstats count as sum by count | fields sum,index,count,sourcetype| eval index="ACCOUNT'S HAD"| eval sourcetype="CALL POPS" |rename sum as "NUMBER OF ACCOUNT'S"| rename index as "INFO_1"| rename sourcetype as "INFO_2"| rename count as "NUMBER OF CALLS"| dedup "NUMBER OF CALLS" | sort "NUMBER OF CALLS" | foreach * [eval combo = mvappend(combo, $<<FIELD>>$)] | eval combo=mvjoin(combo," ") | table combo

Re: How to format stats table results as rows of strings without column headers?

kmccowen — Tue, 30 Jun 2015 18:11:42 GMT

Much better but the count and sums are out of sequence. Below is the output i'm getting.

combo
ACCOUNT'S HAD CALL POPS 48024 1
ACCOUNT'S HAD CALL POPS 13460 2
ACCOUNT'S HAD CALL POPS 2883 3
ACCOUNT'S HAD CALL POPS 914 4

Re: How to format stats table results as rows of strings without column headers?

woodcock — Tue, 30 Jun 2015 18:22:46 GMT

Also, to further "vaguify" your table, you can rename combo to something that looks like a divider by adding this to the end:

... | rename combo AS "---------------------------------------------------------"

Re: How to format stats table results as rows of strings without column headers?

woodcock — Tue, 30 Jun 2015 18:30:04 GMT

OK, try this:

index=ctap host=sc58* sourcetype=gateway "CTIPOP CALL RECEIVED" acct="*" | stats count by acct | eventstats count as sum by count | fields sum,index,count,sourcetype| eval index="ACCOUNT'S HAD"| eval sourcetype="CALL POPS" |rename sum as "NUMBER OF ACCOUNT'S"| rename index as "INFO_1"| rename sourcetype as "INFO_2"| rename count as "NUMBER OF CALLS"| dedup "NUMBER OF CALLS" | sort "NUMBER OF CALLS" | foreach * [eval combo = mvappend(combo, $<<FIELD>>$)] | eval combo2=mvjoin(combo," ") | table combo2 | rename combo2 AS "---------------------------------------------------------"

Re: How to format stats table results as rows of strings without column headers?

kmccowen — Tue, 30 Jun 2015 18:34:09 GMT

I get the error "Error in 'foreach' command: arguments must contain at least one field specifier"

I added an * after the foreach command but then I get "no results found"

Re: How to format stats table results as rows of strings without column headers?

woodcock — Tue, 30 Jun 2015 18:38:21 GMT

ARGH! Retry (markdown got me but I re-edited it).

Re: How to format stats table results as rows of strings without column headers?

kmccowen — Mon, 28 Sep 2020 20:25:45 GMT

Getting "no results found" 😞

Re: How to format stats table results as rows of strings without column headers?

kmccowen — Mon, 28 Sep 2020 20:25:50 GMT

If I remove the table command at the end, I get the same results I was getting before with the headers.

NUMBER OF ACCOUNT'S INFO_1 NUMBER OF CALLS INFO_2
34866 ACCOUNT'S HAD 1 CALL POPS
8805 ACCOUNT'S HAD 2 CALL POPS
1782 ACCOUNT'S HAD 3 CALL POPS

Re: How to format stats table results as rows of strings without column headers?

woodcock — Tue, 30 Jun 2015 21:18:21 GMT

The comment chain is getting pretty deep so I am pulling back out to a fresh answer:

index=ctap host=sc58* sourcetype=gateway "CTIPOP CALL RECEIVED" acct="*" | stats count by acct | eventstats count as sum by count | fields sum,index,count,sourcetype| eval index="ACCOUNT'S HAD"| eval sourcetype="CALL POPS" |rename sum as "NUMBER OF ACCOUNT'S"| rename index as "INFO_1"| rename sourcetype as "INFO_2"| rename count as "NUMBER OF CALLS"| dedup "NUMBER OF CALLS" | sort "NUMBER OF CALLS" | foreach * [eval combo = mvappend(combo, $<<FIELD>>$)] | eval combo2=mvjoin(combo," ") | table combo2 | rename combo2 AS "---------------------------------------------------------"

The difference is that when I use a single field combo for both input and output, Splunk is sorting the mvappend but when I do it with 2 fields with combo as input and combo2 as output, the mvappend is not sorted. So the above should work for you (it does for me).

Re: How to format stats table results as rows of strings without column headers?

kmccowen — Wed, 01 Jul 2015 19:34:32 GMT

I'm getting these results so it's still out order for me for some reason.

ACCOUNT'S HAD CALL POPS 36906 1
ACCOUNT'S HAD CALL POPS 8714 2
ACCOUNT'S HAD CALL POPS 1729 3
ACCOUNT'S HAD CALL POPS 512 4
ACCOUNT'S HAD CALL POPS 155 5
ACCOUNT'S HAD CALL POPS 57 6

Re: How to format stats table results as rows of strings without column headers?

acharlieh — Wed, 01 Jul 2015 19:35:46 GMT

I think you could use a much simpler query

index=ctap host=sc58* sourcetype=gateway "CTIPOP CALL RECEIVED" acct="*" 
| stats count by acct | stats count as sum by count
| eval message=sum." ACCOUNTS HAD ".count." CALL POPS"
| sort count | table message

The first two lines, are your original base query to calculate the number of accounts that called each of how many times.
The third line we use eval to concatenate the fields together into a message
The fourth line we sort our result lines based on count (assuming this is the sort you want), and then throw away all fields other than our built message.

Re: How to format stats table results as rows of strings without column headers?

kmccowen — Wed, 01 Jul 2015 20:31:58 GMT

You were right, this worked! Thanks!

Re: How to format stats table results as rows of strings without column headers?

acharlieh — Wed, 01 Jul 2015 20:41:54 GMT

Glad I could help @kmccowen! Also not sure if you're aware of this, but instead of using the "award points" link (which deducts from your answers karma) if you feel an answer, question, or comment is particularly good, you can use the upvote button (^) instead. Doing so awards karma to whomever helped you out, while keeping your karma intact, and helps with rankings of content too. I've awarded you the 20 karma that you gave away in this manner.

Re: How to format stats table results as rows of strings without column headers?

acharlieh — Wed, 01 Jul 2015 20:47:27 GMT

For everyone's benefit here... the reason the fields are coming out in the wrong order here, and for the previous attempts is the foreach * [] part. Listing a number of fields using * means the fields are processed in alphabetical order. With the fields that you had at that point, this means that you would append to the combo field in order the fields INFO_1 INFO_2 NUMBER OF ACCOUNT'S NUMBER OF CALLS. Now yes you could explicitly list the fields in the order you want, but if you're going through that much trouble, it'd becomes easier to just explicitly append the fields together.