Dashboards & Visualizations

Why are results different if my search is used in the dashboard versus the search bar?

bawan
New Member

I have used sub earch, while running from search bar its showing correct result as single value. But when put it on Dashboard panel, its showing No result found. I tried to put that query in CDATA as well, but no luck.

index="aa1" sourcetype="adlist" earliest=-8d latest=now
| table ComputerName
| dedup ComputerName
| eval ComputerName = lower(ComputerName)
| join type=inner ComputerName
[ search index="aa1" sourcetype="serverg" earliest=-8d latest=now
| rename Name as "ComputerName"
| eval ComputerName = lower(ComputerName)
| table ComputerName
]
| join type=inner ComputerName
[ search index=bb1 source=DSM sourcetype=hostp earliest=-2d latest=now
| rex field=_raw "Hostname=\"(?P[^.]+)" | rename Hostname as "ComputerName" | eval ComputerName = lower(ComputerName) | table ComputerName, Pattern | join type=left "Pattern" [ search index=bb1 source=DSM sourcetype=hostp earliest=-2d latest=now | table Pattern | dedup Pattern | sort-"Pattern" | streamstats count as row | eval Compliance = case(row=1, "Compliant(N, N-1, N-2)", row=2, "Compliant(N, N-1, N-2)", row=3 , "Compliant(N, N-1, N-2)", row>3 , "Non Compliant") | table "Pattern", Compliance] | table ComputerName, Pattern , Compliance] | table ComputerName, Pattern, Compliance | top limit=10 Compliance | search Compliance="Compliant(N, N-1, N-2)" | fields count 
0 Karma

DalJeanis
SplunkTrust
SplunkTrust

When converting to a dashboard, I almost always have to update the "escaping" to correct minor issues with what the system did for me automatically.

However, it seems like this can be refactored to get your desired results more easily.

From the inside out, you only want computers in index bb1 in the last two days with the three most recent patterns. You are going to throw away all the ComputerName records that don't have one of them anyway, so let's only pull those records in the first place. That looks like this...

  index=bb1 source=DSM sourcetype=hostp earliest=-2d latest=now 
       [ search index=bb1 source=DSM sourcetype=hostp earliest=-2d latest=now 
        | fields Pattern | dedup Pattern | sort - Pattern | streamstats count as row 
        | where row<=3 | table Pattern]
  | rex field=_raw "Hostname=\"(?<ComputerName>[^.]+)" 
  | eval ComputerName = lower(ComputerName)

Now, from the outside, you want all the ComputerNames that have been in the aa1 index in the last 8 days, but only if they were in both sourcetypes adlist and serverg. That looks like this...

  index="aa1" sourcetype="adlist" OR sourcetype="serverg" earliest=-8d latest=now 
  | fields index sourcetype Name ComputerName
  | eval ComputerName=lower(coalesce(ComputerName, Name))
  | stats values(index) as index values(sourcetype) as sourcetype by ComputerName
  | where mvcount(sourcetype>1)
  | fields - sourcetype 

And the final join should look like this...

index="aa1" sourcetype="adlist" OR sourcetype="serverg" earliest=-8d latest=now 
| fields index sourcetype Name ComputerName
| eval ComputerName=lower(coalesce(ComputerName, Name))
| stats values(index) as index values(sourcetype) as sourcetype by ComputerName
| where mvcount(sourcetype>1)
| fields - sourcetype 
| join ComputerName
       [ search index=bb1 source=DSM sourcetype=hostp earliest=-2d latest=now 
             [ search index=bb1 source=DSM sourcetype=hostp earliest=-2d latest=now 
              | fields Pattern | dedup Pattern | sort - Pattern | streamstats count as row 
              | where row<=3 | table Pattern]
        | rex field=_raw "Hostname=\"(?<ComputerName>[^.]+)" 
        | eval ComputerName = lower(ComputerName)
        ] 
| stats count

This refactor should run a bit quicker than the original.

0 Karma

bawan
New Member

Found the Problem:

SubQuery join is not working on Dashboard , while same is working on Search Bar.

For eg.

[Query 1] join [ Query 2 join Query 3] Not working on Dashboard .
I changed it to
[Query 1] join [ Query 2] join [Query 3]

0 Karma

bawan
New Member

Please ignore the above query .. below is the updated query :

index="aa1" sourcetype="adlist" earliest=-8d latest=now
| table ComputerName
| dedup ComputerName
| eval ComputerName = lower(ComputerName)
| join type=inner ComputerName
[ search index="aa1" sourcetype="serverg" earliest=-8d latest=now
| rename Name as "ComputerName"
| eval ComputerName = lower(ComputerName)
| table ComputerName
]
| join type=inner ComputerName
[ search index=bb1 source=DSM sourcetype=hostp earliest=-2d latest=now
| rex field=_raw "Hostname=\"(?P[^.]+)"
| rename Hostname as "ComputerName"
| eval ComputerName = lower(ComputerName)
| table ComputerName, Pattern
| join type=left "Pattern"
[ search index=bb1 source=DSM sourcetype=hostp earliest=-2d latest=now
| table Pattern
| dedup Pattern
| sort-"Pattern"
| streamstats count as row
| eval Compliance = case(row=1, "Compliant(N, N-1, N-2)", row=2, "Compliant(N, N-1, N-2)", row=3 , "Compliant(N, N-1, N-2)", row>3 , "Non Compliant")
| table "Pattern", Compliance]
| table ComputerName, Pattern , Compliance]
| table ComputerName, Pattern, Compliance
| top limit=10 Compliance
| search Compliance="Compliant(N, N-1, N-2)"
| fields count

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi bawan,
I'm not sure to have understood your request, anyway, please review your search

index="aa1" sourcetype="adlist" earliest=-8d latest=now 
| eval ComputerName = lower(ComputerName) 
| dedup ComputerName 
| join type=inner ComputerName 
   [ search index="aa1" sourcetype="serverg" earliest=-8d latest=now 
   | rename Name as "ComputerName" 
   | eval ComputerName = lower(ComputerName) 
   | fields ComputerName 
   ] 
| join type=inner ComputerName 
   [ search index=bb1 source=DSM sourcetype=hostp earliest=-2d latest=now 
   | rex field=_raw "Hostname=\"(?P[^.]+)" 
   | rename Hostname as "ComputerName" 
   | eval ComputerName = lower(ComputerName) 
   | fields ComputerName, Pattern 
   | join type=left Pattern 
      [ search index=bb1 source=DSM sourcetype=hostp earliest=-2d latest=now 
      | table Pattern 
      | dedup Pattern 
      | sort -Pattern 
      | streamstats count as row 
      | eval Compliance = case(row=1, "Compliant(N, N-1, N-2)", row=2, "Compliant(N, N-1, N-2)", row=3 , "Compliant(N, N-1, N-2)", row>3 , "Non Compliant") 
      | fields Pattern  Compliance
      ]
   | fields ComputerName Pattern Compliance
   ]
| top Compliance 
| search Compliance="Compliant(N, N-1, N-2)" 
| table count

To debug your problem:
at first run your subsearches and verify if results are more o less 50000 events because there's a limit to 50000 for subsearch results;
after try to run each subsearch and check if you have the same result running the same searches putting 0 in sort commands (there a limit to 10000 results for sort command).

At the end verify if you can semplify your search because join is not so performant!
e.g. first part of your search could be transformed in

index="aa1" (sourcetype="adlist" OR sourcetype="serverg") earliest=-8d latest=now 
| rename Name as "ComputerName" 
| eval ComputerName = lower(ComputerName) 
| stats values(sourcetype) AS sourcetype BY ComputerName
| search sourcetype="adlist" sourcetype="serverg"

Bye.
Giuseppe

0 Karma

bawan
New Member

Hi Giuseppe ,
Thanks for the response, my query is working fine when I search from Search bar. Only problem is, its not showing values when I put it on Dashboard. Same Dashboard is working on Pre pod instance whose version is 6.6.1 and Prod splunk instance version is 6.5.1. Also, I am using same data source in background for both the instances.

0 Karma

gcusello
SplunkTrust
SplunkTrust

Are you using search bar of the same app of dashboard?
Have you, in you dashboard Post Process Searches?
Bye.
Giuseppe

0 Karma

bawan
New Member

On Dashboard panel where its showing No result found, i click on Open in search for that panel , it will open new search bar. On this, it show No results found, but when i hit enter key for search it will show result.

0 Karma

gcusello
SplunkTrust
SplunkTrust

only two last stupid checks:
try to add a new panel using the search that correctly runs and verify if it runs.

After how did you copied this search?
when you open your dashboard with dashboard editor, on the left side of each row of the search they should be some grey lines that mean tabs, check if there are in every row of your search, if not delete spaces on the left of each row: I found problems related to dirty chars in dashboards.

Bye.
Giuseppe

0 Karma

bawan
New Member

Did both the things, still facing same issue.

0 Karma

gcusello
SplunkTrust
SplunkTrust

Sorry I haven't any other idea!
Open a case to Splunk Support.
Bye.
Giuseppe

0 Karma

bawan
New Member

Version of SPLUNK Enterprise is 6.5.1

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...