spl qquery

[nithys]

Hi

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  

[gcusello]

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

[nithys]

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”	5
hi-cost-test-name		3

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

{"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"}

[nithys]

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”	5
hi-cost-test-name	"preprod”,2”,3””,”4”,”5”	3

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

[PickleRick]

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.

[nithys]

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>
        <!-- Token for the second query -->
        <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>