topic Re: search string query in Splunk Search

search string query

francly — Tue, 26 Sep 2017 02:37:11 GMT

Hi I can use the search string to get the statistics output

index=data sourcetype="data1" host=HOSTA | stats count by NAME | sort -count | head 3

Name Count
SRV1 800
SRV2 600
SRV6 700

Question is how I continue use string to query each of the output "Name" to display a new field "RULE" under "Name"

Example

index=data sourcetype="data1" host=HOSTA Name=SRV1 | stats count by RULE | sort -count

Re: search string query

gcusello — Tue, 26 Sep 2017 09:31:43 GMT

Hi francly,
I'm not sure to have understood you request: do you want to create a new query or use the same to have a subdivision of RULES by NAMEs?

If the first you already have the solution.

If the second you can use

index=data sourcetype="data1" host=HOSTA Name=SRV1 | stats count by NAME RULE | sort -count

and take the first 3.

Remeber that if you want a stats by one field (e.g. stats count by NAME) it's easier to use top command.

Bye.
Giuseppe

Re: search string query

francly — Tue, 26 Sep 2017 09:50:43 GMT

Hi Giuseppe,

I want to know based on my output

index=data sourcetype="data1" host=HOSTA | stats count by NAME | sort -count | head 3

Name Count
SRV1 800
SRV2 600
SRV6 700

it's any string of script will automatically take "Name" from the output in this 3 name or potentially more name to get the top "Rule"

Re: search string query

gcusello — Tue, 26 Sep 2017 11:23:17 GMT

Hi francly,
you can add values(RULE) AS RULE to have a list of all rules for each host, is it what you like?
something like this

index=data sourcetype="data1" host=HOSTA 
| stats values(RULE) AS RULE count by NAME 
| sort -count 
| head 3

Bye.
Giuseppe

Re: search string query

francly — Wed, 27 Sep 2017 03:34:05 GMT

Hi Giuseppe, it's possible to break down the count for all the rule? right now I'm only getting 1 rule per Name

Name Total Count
SRV1 800
Rule1 500
Rule2 200
Rule3 100

SRV2 600
SRV6 700

Re: search string query

gcusello — Wed, 27 Sep 2017 11:02:20 GMT

Hi francly,
try this

index=data sourcetype="data1" host=HOSTA 
| stats count by NAME, RULE
| search [ search 
   index=data sourcetype="data1" host=HOSTA 
   | stats count by NAME 
   | head 3 
   | fields NAME 
   ]
| eventstats sum(count) as rank by NAME
| appendpipe  [ stats values(rank) AS rank sum(count) AS Total by NAME ] 
| sort 0 -rank NAME -Total -count
| fields - rank
| eval NAME=if(Total>0,NAME,"")

Bye.
Giuseppe

Re: search string query

francly — Thu, 28 Sep 2017 03:32:30 GMT

Hi Giuseppe,

I getting this not relevant output

Re: search string query

DalJeanis — Thu, 28 Sep 2017 05:48:16 GMT

You can try something like this...

index=data sourcetype="data1" host=HOSTA Name=SRV1 
| stats count by NAME RULE 

| rename COMMENT as "Chew all the records up again to get the top 3 names"
| appendpipe [| stats sum(count) as totcount by NAME | sort 3 - totcount]

| rename COMMENT as "Roll the top 3 totcount onto all records with that NAME, then drop all records without totcount"
| eventstats values(totcount) as totcount by NAME
| where isnotnull(totcount)

Re: search string query

gcusello — Thu, 28 Sep 2017 07:44:41 GMT

Sorry I cannot see your output.
Bye.
Giuseppe