I am retrieving two time fields one from main query and other from subquery. When I subtract both fields, I get blank output.
Query I am using:
index=main host=* *CRgsSessionInfo* PrimaryUserLogin=PrimaryUserLogOn | eval Time = _time | append [search host=* *CRgsSessionInfo PrimaryUserLogin=PrimaryUserLogoff | eval Time1 = _time ] | eval Diff= Time1-Time |table Diff
marked code. dmj
Easier fix would be to use appendcols
command instead of append
(assuming both your searches gives a single rows result). With some cleanup/best practices, it'd look like this
index=main host=* *CRgsSessionInfo* PrimaryUserLogin=PrimaryUserLogOn | eval Time = _time | table Time| appendcols [search host=* *CRgsSessionInfo PrimaryUserLogin=PrimaryUserLogoff | eval Time1 = _time | table Time1] | eval Diff= Time1-Time |table Diff
Better approach is the one suggested by @DalJeanis, when you can completely eliminate subsearch, so look at that.
So, there are a number of issues here.
First, you are not giving yourself all the other contextual fields, so you have no way to interpret your results and why they are empty. If you looked at the results without the table command, you would be able to get farther.
Second, you are appending two sets of records, so there are separate records, some for logons and some for logoffs. none of those records have both a logon and a logoff on them, so the diff will always be null. You can actually select all those same records at the same time, but you still need to link them.
Third, you are not connecting the two records in any way. stats
is one example of a verb that can be used to pull together different records, if you have a mutual key field. One would assume there is a user field, and a host field. so maybe this is the way they should be connected.
index=main host=* *CRgsSessionInfo* (PrimaryUserLogin="PrimaryUserLogOn" OR PrimaryUserLogin="PrimaryUserLogoff")
| fields _time host user PrimaryUserLogin
| eval timein=case(PrimaryUserLogin="PrimaryUserLogOn",_time)
| eval timeout=case(PrimaryUserLogin="PrimaryUserLogoff",_time)
| stats count min(timein) as timein max(timeout) as timeout by host user
| eval Diff =timeout-timein
If count is greater than 2, then it means you have multiple logins and logouts and you need a more complex search.
Hey Daljeanis,
Thank you, above query worked for me. But my count is greater than 2 can you suggest something to handle multiple logins?