Hello
I am trying to create a report which compares users present on day x with the users present on day y.After comparing it has to give the output as a User added or removed. I am able to do it in a bit complex way but the issue with my solution is I have to dynamically rename the fields based on the value (integer) being created by the rex command. I am using the rex command to figure out the day on the user being added.
The image below shows a snippet of the table after doing the dedup to get the Unique Users.
So after this I am doing this
|rex field="Report Run Date_Time" "\d+\/(?<day>\d+)\/" | contingency "User Information _Editable" day | rename 8 as newday 7 as oldday|eval toa = newday-oldday|eval Diff=case(toa>0,"Added",toa<0,"Removed",toa==0,"Already Present")|table "User Information _Editable" "Diff"
This does give me the added/removed result. But as you see I had to rename the fields to be able to do a difference. Any ideas on how I can solve this over a random user given dates.
Try this (after you get unique your unique users)
<your search, unique users> | rex field="Report Run Date_Time" "(?<ReportDate>[^ ]+)"|eventstats min(ReportDate) as dayY, max(ReportDate) as dayX | eval day=if(ReportDate=dayY,"PrevDay","CurrDay") | contingency "User Information _Editable" day |eval toa = newday-oldday|eval Diff=case(toa>0,"Added",toa<0,"Removed",toa==0,"Already Present")|table "User Information _Editable" "Diff"
This query works only when the time frame selected is for 2 days.
Try this (after you get unique your unique users)
<your search, unique users> | rex field="Report Run Date_Time" "(?<ReportDate>[^ ]+)"|eventstats min(ReportDate) as dayY, max(ReportDate) as dayX | eval day=if(ReportDate=dayY,"PrevDay","CurrDay") | contingency "User Information _Editable" day |eval toa = newday-oldday|eval Diff=case(toa>0,"Added",toa<0,"Removed",toa==0,"Already Present")|table "User Information _Editable" "Diff"
This query works only when the time frame selected is for 2 days.
Thanks that works. Completely forgot that I could use eventstats to determine the min and max. 🙂