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
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.
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]
Hi @bawan ,
good for you, see next time!
let us know if we can help you more, or, please, accept one answer (even if your one) for the other people of Community.
Ciao and happy splunking
Giuseppe
P.S.: Karma Points are appreciated by all the contributors 😉
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
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
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.
Are you using search bar of the same app of dashboard?
Have you, in you dashboard Post Process Searches?
Bye.
Giuseppe
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.
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
Did both the things, still facing same issue.
Sorry I haven't any other idea!
Open a case to Splunk Support.
Bye.
Giuseppe
Version of SPLUNK Enterprise is 6.5.1
I have the same issue