Splunk Search

Two dashboards values in single value panel should equal to third dashboard

gopiven
Explorer

Hello Experts

I have 3 dashboards basically.

Board 1 represents total login attempts for an hour (including successful & failure attempts)
Search example:

index=main1 SOURCETYPE=base1 "TEXT MATCHING FOR SUCCESSFUL LOGIN" OR "TEXT MATCHING FOR FAILURE LOGIN"
index=main2 SOURCETYPE=base2 "TEXT MATCHING FOR SUCCESSFUL LOGIN" OR "TEXT MATCHING FOR FAILURE LOGIN"
index=main3 SOURCETYPE=base3 "TEXT MATCHING FOR SUCCESSFUL LOGIN" OR "TEXT MATCHING FOR FAILURE LOGIN" | dedup username |timechart span=1m count(username) as TOTAL_COUNT | eval _time=_time-now()%3600 | timechart span=1h sum(TOTAL_COUNT) as ALLCOUNT | tail 3 | tail 2 | eval _time=_time+now()%3600

Board 2 represents successful attempts for an hour (among total login counts from board 1)
Search example:

index=main1 SOURCETYPE=base1 "TEXT MATCHING FOR SUCCESSFUL LOGIN"
index=main2 SOURCETYPE=base2 "TEXT MATCHING FOR SUCCESSFUL LOGIN"
index=main3 SOURCETYPE=base3 "TEXT MATCHING FOR SUCCESSFUL LOGIN" | dedup username |timechart span=1m count(username) as TOTAL_COUNT | eval _time=_time-now()%3600 | timechart span=1h sum(TOTAL_COUNT) as ALLCOUNT | tail 3 | tail 2 | eval _time=_time+now()%3600

Board 3 represents failed attempts for an hour (among total login counts from board 1)
Search example:

index=main1 SOURCETYPE=base1 "TEXT MATCHING FOR FAILURE LOGIN"
index=main2 SOURCETYPE=base2 "TEXT MATCHING FOR FAILURE LOGIN"
index=main3 SOURCETYPE=base3 "TEXT MATCHING FOR FAILURE LOGIN" | dedup username |timechart span=1m count(username) as TOTAL_COUNT | eval _time=_time-now()%3600 | timechart span=1h sum(TOTAL_COUNT) as ALLCOUNT | tail 3 | tail 2 | eval _time=_time+now()%3600

At any point of time total login should show the sum of successful & failure attempts and it has to compare with the trend for last 60 mins (in single value panel and time set as last 120 min)
But in my board it both total & successful login showing same values and total attempts is not equal to sum of successful & failure attempts.

Kindly help me to tune the query like, total login should show the sum of successful & failure attempts.

or any other way to make this simple one please.

0 Karma

woodcock
Esteemed Legend

You are doing it wrong in many ways. The most important is that it is TOTALLY INVALID to do a dc and them later sum that as a total count (think about it). Try this for a base search that drives all the other post-process searches:

((index="main1" AND sourcetype="base1") OR (index="main2" AND sourcetype="base2") OR (index="main3" AND sourcetype="base3")) AND ("TEXT MATCHING FOR SUCCESSFUL LOGIN" OR "TEXT MATCHING FOR FAILURE LOGIN")
| multireport
[ timechart span=1m dc(username) AS minutely count ]
[ timechart span=1h dc(username) AS hourly_count ]

Then you can in your post_process search do something like this:

| search minutely_count="*"

OR

| search hourly_count="*"
0 Karma

richgalloway
SplunkTrust
SplunkTrust

@niketnilay's comments are good and should be converted to an answer. That said, I think your queries need parentheses and "OR".

Also, the dedup command throws away all but the most recent event for each user. That means you are counting users who have logged in or failed to login, not login attempts.
What is the purpose of eval _time=_time-now()%3600? Perhaps the goal is to convert _time into hours ago, but because of the order of operations the result is to subtract a number of seconds (0-3599) from _time. Either way, the timechart command probably will not display the expected results.
Why two consecutive tail commands? Only the second is needed.
A tail command after timechart doesn't make sense to me. It displays the two oldest results. What is the intended purpose?

Try this:

(index=main1 SOURCETYPE=base1 "TEXT MATCHING FOR SUCCESSFUL LOGIN" OR "TEXT MATCHING FOR FAILURE LOGIN") OR
(index=main2 SOURCETYPE=base2 "TEXT MATCHING FOR SUCCESSFUL LOGIN" OR "TEXT MATCHING FOR FAILURE LOGIN") OR
(index=main3 SOURCETYPE=base3 "TEXT MATCHING FOR SUCCESSFUL LOGIN" OR "TEXT MATCHING FOR FAILURE LOGIN") 
|timechart span=1m count(username) as TOTAL_COUNT | timechart span=1h sum(TOTAL_COUNT) as ALLCOUNT

(index=main1 SOURCETYPE=base1 "TEXT MATCHING FOR SUCCESSFUL LOGIN") OR
(index=main2 SOURCETYPE=base2 "TEXT MATCHING FOR SUCCESSFUL LOGIN") OR
(index=main3 SOURCETYPE=base3 "TEXT MATCHING FOR SUCCESSFUL LOGIN")
|timechart span=1m count(username) as TOTAL_COUNT | timechart span=1h sum(TOTAL_COUNT) as ALLCOUNT

(index=main1 SOURCETYPE=base1 "TEXT MATCHING FOR FAILURE LOGIN") OR
(index=main2 SOURCETYPE=base2 "TEXT MATCHING FOR FAILURE LOGIN") OR
(index=main3 SOURCETYPE=base3 "TEXT MATCHING FOR FAILURE LOGIN")
|timechart span=1m count(username) as TOTAL_COUNT  | timechart span=1h sum(TOTAL_COUNT) as ALLCOUNT
---
If this reply helps you, Karma would be appreciated.
0 Karma

niketn
Legend

@gopiven this seems to be use case for a single query rather than three. If you are on Splunk 6.6 or higher you can use Trellis layout to split viz and show Total Successful And Failed logins. If you are on prior version, you can use Post-Processing to split the results to three different Single Value viz. You can try the single SPL on the following lines (untested of-course)

index=main1 SOURCETYPE=base1 "TEXT MATCHING FOR SUCCESSFUL LOGIN" OR "TEXT MATCHING FOR FAILURE LOGIN"
    OR (index=main2 SOURCETYPE=base2 "TEXT MATCHING FOR SUCCESSFUL LOGIN" OR "TEXT MATCHING FOR FAILURE LOGIN")
    OR (index=main3 SOURCETYPE=base3 "TEXT MATCHING FOR SUCCESSFUL LOGIN" OR "TEXT MATCHING FOR FAILURE LOGIN") 
| eval type=case((index=="main1" AND sourcetype=="base1" AND searchmatch("TEXT MATCHING FOR SUCCESSFUL LOGIN")) OR 
                 (index=="main2" AND sourcetype=="base2" AND searchmatch("TEXT MATCHING FOR SUCCESSFUL LOGIN")) OR 
                 (index=="main3" AND sourcetype=="base3" AND searchmatch("TEXT MATCHING FOR SUCCESSFUL LOGIN")), "Successful",
                 (index=="main1" AND sourcetype=="base1" AND searchmatch("TEXT MATCHING FOR FAILURE LOGIN")) OR 
                 (index=="main2" AND sourcetype=="base2" AND searchmatch("TEXT MATCHING FOR FAILURE LOGIN")) OR 
                 (index=="main3" AND sourcetype=="base3" AND searchmatch("TEXT MATCHING FOR FAILURE LOGIN")), "Failure",) 
| timechart span=1h count as TOTAL_COUNT count(eval(type=="Successful") as "SUCCESSFUL" count(eval(type=="Failure") as "FAILURE" by type

Following is a run anywhere example based on Splunk's _internal index where I have manipulated INFO and ERROR events as Success and Failed scenarios similar to your use case:

index=_internal sourcetype=splunkd ("INFO" OR "ERROR") 
| eval type=case(searchmatch("INFO"),"Success",
                 searchmatch("ERROR"),"Failure")
| timechart count as Total count(eval(type=="Success")) as "Success" count(eval(type=="Failure")) as "Failure"
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

gopiven
Explorer

Thanks Niketnilay!
I tried to implement as above but unable to get the expected answers.
problem behind that it contains many text search matches with special characters example: "AppTokenValidationServlet::processRequest::Failure::" etc., also

If I am doing like this
it showing same results for total login & successful login.

0 Karma
Get Updates on the Splunk Community!

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...