Query is failing at where condition

[thambisetty]

Did you modify your query before posting here in community or is that the same query where you are facing issue.

and also, provide example output.

[swathiadireddy]

Hi @thambisetty 

I haven't modified the query.

[thambisetty]

index=ABC sourcetype=XYZ

|rex user1
| rex Log_ID

can you check, is the above query working for you? I don't think it works because there is an error in rex command.

[richgalloway]

I'm not surprised the where command is failing.  Keep in mind the command looks at a single event at a time and asks itself "does this event have a field called 'user1' and a field called 'user2' and do those two fields have the same value?".  The answer, of course, is always "no" because no event has both a user1 and a user2 field.

All is not lost, however.  The two searches can be stitched together using stats.

...
| eval user_type=if(in (ID," AUT22222", " NWC333333"," AUT55555"),"Logout_user",nothing)

| eval user=coalesce(user1, user2)
| stats values(*) as * by user

| stats count(eval(user_type="Logout_user")) as Logout values(user2) as user2

[swathiadireddy]

That didn't work!

Below two query's work and wanted to calculate the Active_user count when user1=user2 

Active_user=Login-Logout

[impurush]

Hi @swathiadireddy 

Please try the below query, you are using the user1 inside the subquery which will not give any result.

index=cert sourcetype="pulse*" 
| rex user2 
| rex Log_ID 
| search "AUT22222" OR "NWC333333" OR "AUT55555" 
| eval user_type=if(in (ID," AUT22222", " NWC333333"," AUT55555"),"Logout_user",nothing) 
| append 
    [ search index=ABC sourcetype=XYZ 
    | rex user1 
    | rex Log_ID 
    | search "AUT99999" 
    | eval user_type=case(ID == " AUT99999","Login_user",1=1, nothing) 
    | stats count(eval(user_type="Login_user")) as Login values(user1) as user1 
    | eval Active_user = Login ] 
| where user1=user2 
| stats count(eval(user_type="Logout_user")) as Logout values(user2) as user2 
| eval Active_user = Login - Logout 
| table Active_user

[swathiadireddy]

Thanks @impurush  but it didn't work