topic Re: spl qquery in Splunk Search

spl qquery

nithys — Thu, 13 Mar 2025 23:27:19 GMT

Need help in finding DistinctAdminUserCount and DistinctAdminUserNames of each associated Name inside test or prod object

{"prod":{},"test":{"DistinctAdminUser":["streaming","Create","","Application.","App.","App.","obi","Users","platform",],"TotalSinkAdminUsers":33,"TotalNSP3Count":11,"TotalSourceAdminUsers":10,"DistinctAdminUserCount":11,"TotalStreamAdminUsers":12,"TotalAdminUser":55,"nsp3s":[{"StreamAdminUserNames":["App."],"SourceAdminUserNames":["preprod"],"DistinctAdminUserCount":5,"SinkAdminUserCount":5,"SourceAdminUserCount":1,"DistinctAdminUserNames”:[“Technology”,”2”,3””,”4”,”5”],”StreamAdminUserCount":1,"TotalAdminUserCount":7,"SinkAdminUserNames":["obi"],"Name”:”hi-cost-test-sample“},{“StreamAdminUserNames":["preprod"],"SourceAdminUserNames":["admin.preprod"],"DistinctAdminUserCount":3,"SinkAdminUserCount":3,"SourceAdminUserCount":1,"DistinctAdminUserNames":["preprod”,2”,3””,”4”,”5”],”StreamAdminUserCount":1,"TotalAdminUserCount":5,"SinkAdminUserNames":["ops-tform"],"Name”:”hi-cost-test-name”},”subscriberId":"NSP3"}

index="*" source="*" | spath test.nsps{} output=nsps | mvexpand nsps | spath input=nsps Name output=Name | spath input=nsps ReadOnlyConsumerNames{} output=ReadOnlyConsumerNames | search Name="" | stats values(ReadOnlyConsumerNames) as ReadOnlyConsumerNames by Name | rename Name as EntityName | table EntityName ReadOnlyConsumerNames

Need

Re: spl qquery

gcusello — Fri, 14 Mar 2025 07:00:26 GMT

Hi @nithys ,

what are the results of your search?

what is your issue?

You shared a search that seems to be correct, does it give you results?

Ciao.

Giuseppe

Re: spl qquery

nithys — Fri, 14 Mar 2025 12:43:42 GMT

I am looking to have three column one for Name(EntityName) another for DistinctAdminUserNames and third one for DistinctAdminUserCount of each associated Name inside test or prod object.can you modify query in order to fetch like below

Name

DistinctAdminUserNames

DistinctAdminUserCount

hi-cost-test-sample

“Technology”,”2”,3””,”4”,”5”

hi-cost-test-name

getting error as shown in picture
[idx-i-010ab3eb96c032aa1.om,idx-i-021ad0bda97800234.nike.splunkcloud.com,idx-i-0267c01401cb1d8ed..com,idx-i-06c8fc857ab0b62ea.nike.splunkcloud.com,idx-i-0804ced14f96f4517.kcloud.com,idx-i-0a6073abaabb5902b.nike.splunkcloud.com,idx-i-0ea40148068176b7f.noud.com] Field 'nsp3s' does not exist in the data.

query

index="*" source="**" sourcetype="*" | spath test.nsp3s{} output=nsps3s | mvexpand nsp3s | spath input=nsp3s Name output=Name | spath input=nsp3s DistinctAdminUserNames{} output=DistinctAdminUserNames | search Name="hi-cost-test-sampleDistinctAdminUserNamesDistinctAdminUserNamesDistinctAdminUserNamesDistinctAdminUserNames" | stats values(DistinctAdminUserNames) as DistinctAdminUserNames by Name | rename Name as EntityName | table EntityName DistinctAdminUserNames

splunk data

Re: spl qquery

nithys — Fri, 14 Mar 2025 12:49:52 GMT

Help to modify existing query to add all Name,DistinctAdminUserNames,DistinctAdminUserCount

when trying with search = "hi-cost-test-sample" OR "hi-cost-test-name" .It didnt work

Name

DistinctAdminUserNames

DistinctAdminUserCount

hi-cost-test-sample

“Technology”,”2”,3””,”4”,”5”

hi-cost-test-name

"preprod”,2”,3””,”4”,”5”

index="*" source="**" sourcetype="*" | spath test.nsp3s{} output=nsps3s | mvexpand nsp3s | spath input=nsp3s Name output=Name | spath input=nsp3s DistinctAdminUserNames{} output=DistinctAdminUserNames | search Name="hi-cost-test-sample" OR "hi-cost-test-name" | stats values(DistinctAdminUserNames) as DistinctAdminUserNames by Name | rename Name as EntityName | table EntityName DistinctAdminUserNames

Re: spl qquery

PickleRick — Fri, 14 Mar 2025 14:44:41 GMT

OK. Several things here...

1. You have a "stars galore" in your initial search. Even if your role limits your access to just one kind of data, searching through all indexes, all sourcetypes and all sources is not a very good practice. You should be as specific as possible to make your searches fast. Especially if you use heavy commands like spath.

2. You use inconsistent field names (nsps3s vs nsp3s) - check if it's in the original search or is it just a typo here.

3. Your data sample is not a valid json.

4. Two separate spath commands will create two separate multivalued fields. Splunk keeps no "relation" between values in those commands.

5. Your search should say

| search Name="hi-cost-test-sample" OR Name="hi-cost-test-name"

(still - see p.4)

6. When you get your DistinctAdminUserNames correctly you can get DistinctAdminUserCount by using mvcount() function.

Re: spl qquery

nithys — Sat, 15 Mar 2025 16:15:57 GMT

Hi @PickleRick @gcusello

In below dashboard
I want To fetch the DistinctAdminUserCount for a specific Name (e.g., "-target")
DistinctAdminUserCount

Name -target 3

Name -product 2

Problem 1:with below query it is working if i manually add searchName either test or prod.But if want to work from dashboard the search is not substituting for entityTokennsp
Problem2:search Name can be test or prod based on env selection that also need to be fixed
if it is test,search Name="*-test-target"
if it is pro,search Name="*-prod-target"

Appraoch:

Use the entityToken token to store the dropdown value (e.g., target).
Dynamically derive the required values (target or *-test-targetfob) directly in the queries using the eval and case commands.

entityToken is correctly substituted in dashboard
entityTokennsp is not substituted under below query
Query used in Dashboard:

<query>index="np" source="**" sourcetype="n" | spath path=test.nsp3s{} output=nsp3s | mvexpand nsp3s | spath input=nsp3s path=Name output=Name | spath input=nsp3s path=DistinctAdminUserCount output=DistinctAdminUserCount | search Name=$entityTokennsp$ | sort -_time | head 1 | fields DistinctAdminUserCount</query>

splunk query working in search separately if i given name manually:

index="np" source="**" sourcetype="n" | spath path=test.nsp3s{} output=nsp3s | mvexpand nsp3s | spath input=nsp3s path=Name output=Name | spath input=nsp3s path=DistinctAdminUserCount output=DistinctAdminUserCount | search Name="-test-target" | sort -_time | head 1 | fields DistinctAdminUserCount

Das

<form version="1.1" theme="dark"> <label>Stats </label> <fieldset submitButton="true"> <input type="dropdown" token="indexToken1" searchWhenChanged="false"> <label>Environment</label> <choice value="prod,prod">PROD</choice> <choice value="np,test">TEST</choice> <change> <eval token="stageToken">mvindex(split($value$,","),1)</eval> <eval token="indexToken">mvindex(split($value$,","),0)</eval> </change> <default>np,test</default> </input> <input type="dropdown" token="entityToken" searchWhenChanged="false"> <label>Data Entity</label> <choice value="targetFO">Target FO</choice> <choice value="productFO">Product FO</choice> <change> <eval token="entityToken">case( $value$=="targetFO", "Target", $value$=="productFO", "Product", true(), "" )</eval>  <eval token="entityTokennsp">case( "entityToken"=="targetFO", "test-target", "entityToken"=="productFO", "test-product", true(), "" )</eval> </change> </input> </fieldset> <row> <panel> <single> <title>Distinct Consumer Coun</title> <search> <query>index="np" source="**" sourcetype="n" | spath path=test.nsp3s{} output=nsp3s | mvexpand nsp3s | spath input=nsp3s path=Name output=Name | spath input=nsp3s path=DistinctAdminUserCount output=DistinctAdminUserCount | search Name=$entityTokennsp$ | sort -_time | head 1 | fields DistinctAdminUserCount</query> <earliest>$timeToken.earliest$</earliest> <latest>$timeToken.latest$</latest> </search> <option name="drilldown">none</option> <option name="refresh.display">progressbar</option> </single> </panel> </row> <row> <panel> <title>Total Request :</title> <single> <search> <query>index=np source IN ("*-$stageToken$-$entityToken$") msg="data:invoke" | stats count</query> <earliest>$timeToken.earliest$</earliest> <latest>$timeToken.latest$</latest> <refresh>60m</refresh> <refreshType>delay</refreshType> </search> <option name="colorMode">none</option> <option name="drilldown">none</option> <option name="height">317</option> <option name="rangeColors">["0xcba700","0xdc4e41"]</option> <option name="rangeValues">[200]</option> <option name="refresh.display">progressbar</option> <option name="trellis.enabled">0</option> <option name="trellis.size">large</option> <option name="unitPosition">after</option> <option name="useColors">1</option> </single> </panel> </row> </form>