I'm trying to get a result table of all he hosts in our OSSEC environment that have changed status over the past 24 hours (vs the previous 24 hours). To that end, I've got the following, which works perfectly as far as returning the info I'm looking for:
| set diff [search sourcetype=ossec_agent_control earliest=-2d@d latest=-1d@d | dedup reporting_host | table reporting_host,status] [search sourcetype=ossec_agent_control earliest=-1d@d | dedup reporting_host | table reporting_host,status]
My problem is that this returns a table with two rows entries for each reporting host: the first line being the status from yesterday, and the second line being the status from today. So, say that a host went from "Disconnected" to "Connected". the table would be:
reporting_host status
HOST1 DISCONNECTED
HOST2 CONNECTED
What I'd like is to also have another column at the end specifying "Yesterday" for the first row, and "Today" for the second.
Anyone know of a way to do that?
Thanks.
Hi tmeader
Try this search code
| set diff [search sourcetype="ossec\_agent\_control" earliest=-1d@d | dedup reporting\_host |fieldformat yerster_day = strftime(earliest, "%m/%/%Y:%H:%M:%S")|fields reporting\_host status yerster_day] [search sourcetype="ossec\_agent\_control" earliest=d@d | dedup reporting\_host |fieldformat to_day = strftime(earliest, "%m/%/%Y:%H:%M:%S")| fields reporting\_host status to_day]|table reporting\_host status yerster_day to_day
If we put extra field in the two searches, it will caused that all the results are different.
I have the same question here. I want to show the datetime of the differences found out. Did you solve it?
you could try to add that with eval:
| set diff [search sourcetype=ossec_agent_control earliest=-2d@d latest=-1d@d | dedup reporting_host | eval day="Yesterday" | table reporting_host,status,day] [search sourcetype=ossec_agent_control earliest=-1d@d | dedup reporting_host | eval day="Today" | table reporting_host,status,day]
Yeah, tried that. The problem is that that makes EVERY host record in each of the two sub-searches have different values. One entire set will have "Yesterday" and one entire set will have "Today". Thus they all end up as different.
What I really need is a way to write out the original timestamp from the events AFTER they've been identified by the diff operation. Unfortunately I can't find any way to do this.
Did you find a way to achieve this? Would a join from the returned rows, help? I'm struggling with this same situation, but in my case, I'm receiving output of both hosts with identical info, and I don't know why.