Splunk Dev

Need help in extracting results from two indexes?

amaurya1
Explorer

In the below query, I'm using indexes "abc" and "def" and extracting the results only for the accounts which are present in index "abc" and not in "def" for each hour.
The query works fine but I've an additional requirement where I want to have a third column where I want the count of events coming only from index "abc" in each hour. I'm not able to add that condition, can someone please help?

(index=abc sourcetype=xyz event_type= "Only_Fail")
OR
( index=def (TYPE1=10 OR TYPE2=20) )
| eval dex1 = if(index=="abc", 1, 0)
| eval dex2 = if(index=="def", 1, 0)
| eval myaccounts = coalesce(CUST_ID, account_number)
| stats min(_time) as _time, sum(dex1) as dex1, sum(dex2) as dex2 by myaccounts | where dex2 = 0 and dex1 > 0
| timechart span=1h count(myaccounts) as total_accounts,sum(dex1) as all_fails

0 Karma

Anantha123
Communicator

if you want to see the count in same timechart then use appendcols and add the total events of 1st query.

(index=abc sourcetype=xyz event_type= "Only_Fail")
OR
( index=def (TYPE1=10 OR TYPE2=20) )
| eval dex1 = if(index=="abc", 1, 0)
| eval dex2 = if(index=="def", 1, 0)
| eval myaccounts = coalesce(CUST_ID, account_number)
| stats min(_time) as _time, sum(dex1) as dex1, sum(dex2) as dex2 by myaccounts
| where dex2 = 0 and dex1 > 0
| timechart span=1h count(myaccounts) as total_accounts,sum(dex1) as all_fails
| appendcols
[ search (index=abc sourcetype=xyz event_type= "Only_Fail")
| timechart count as Total ]

0 Karma

amaurya1
Explorer

Hi @Anantha123.. thanks for your reply but this is not giving the correct answer. This query will give the total events only for the accounts which have been received from the existing query(ie before appendcols). I want all the events from the index abc with these conditions (index=abc sourcetype=xyz event_type= "Only_Fail")

0 Karma

Anantha123
Communicator

appendcols doesnot take any values from before searches . Appendcols queries as new query . Else can you try doing the calc the total and then use appendcols to extract accounts .

(index=abc sourcetype=xyz event_type= "Only_Fail")
| timechart count as Total
| appendcols
[ search (index=abc sourcetype=xyz event_type= "Only_Fail") OR ( index=def (TYPE1=10 OR TYPE2=20) )
| eval dex1 = if(index=="abc", 1, 0)
| eval dex2 = if(index=="def", 1, 0)
| eval myaccounts = coalesce(CUST_ID, account_number)
| stats min(_time) as _time, sum(dex1) as dex1, sum(dex2) as dex2 by myaccounts
| where dex2 = 0 and dex1 > 0
| timechart span=1h count(myaccounts) as total_accounts,sum(dex1) as all_fails ]

0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud | Unified Identity - Now Available for Existing Splunk ...

Raise your hand if you’ve already forgotten your username or password when logging into an account. (We can’t ...

Index This | How many sides does a circle have?

February 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...