I have to get the count of records with multiple status and due date less than current date..
Below query - This query works for single status means only Prod.. I get error on clubbing multiple status
index = "my index" Transaction_Type =* Priority=* Category=* |eventstats count as Total_Events |eval due_time= strptime('Due By',"%d.%m.%Y") |eval now_time= now() |stats count as Total_events count(eval((due_time <= now_time) AND ('Status'!="Prod" OR 'Status'!="Go Live" OR 'Status'!="Completed"))) as Missed_count
Please help
Few points about searches
1) They are more efficient if you use positive scenarios rather than negative (or NOT) scenarios i.e. if you know which status you need include only those.
2) You should filter results upfront rather than later. This will give most performance improvement.
IN the above case I assume Status is part of your extracted fields then
index = "my index" Transaction_Type=* Priority=* Category=* AND NOT (Status="Prod" OR Status="Go Live" OR Status="Completed") |eventstats count as Total_Events |eval due_time= strptime('Due By',"%d.%m.%Y") |eval now_time= now() |stats count as Total_events count(eval((due_time <= now_time))) as Missed_count
3) You should also be converting now_time to dd.mm.YYYY format.
PS: If you want to continue to use your existing query you can try the following since you are applying not for each conditions, OR should be replaced with AND:
index = "my index" Transaction_Type = Priority= Category=* |eventstats count as Total_Events |eval due_time= strptime('Due By',"%d.%m.%Y") |eval now_time= now() |stats count as Total_events count(eval((due_time <= now_time) AND ('Status'!="Prod" AND 'Status'!="Go Live" AND 'Status'!="Completed"))) as Missed_count
Few points about searches
1) They are more efficient if you use positive scenarios rather than negative (or NOT) scenarios i.e. if you know which status you need include only those.
2) You should filter results upfront rather than later. This will give most performance improvement.
IN the above case I assume Status is part of your extracted fields then
index = "my index" Transaction_Type=* Priority=* Category=* AND NOT (Status="Prod" OR Status="Go Live" OR Status="Completed") |eventstats count as Total_Events |eval due_time= strptime('Due By',"%d.%m.%Y") |eval now_time= now() |stats count as Total_events count(eval((due_time <= now_time))) as Missed_count
3) You should also be converting now_time to dd.mm.YYYY format.
PS: If you want to continue to use your existing query you can try the following since you are applying not for each conditions, OR should be replaced with AND:
index = "my index" Transaction_Type = Priority= Category=* |eventstats count as Total_Events |eval due_time= strptime('Due By',"%d.%m.%Y") |eval now_time= now() |stats count as Total_events count(eval((due_time <= now_time) AND ('Status'!="Prod" AND 'Status'!="Go Live" AND 'Status'!="Completed"))) as Missed_count
Thank you so much.. It worked.. Problem is I have to calculate the %. Hence If i give in base search I will not be able to get total count to calculate % . In the first query my Total_Events is same as Missed_count..
If there is any other better approach please let me know.. Thanks a lot..