Include zero-count items from lookup

[benefitcos]

I have a search that checks my connection logs so to track users who log into my website against a lookup csv with about 500 users listed:

sourcetype!="*Private*" "Connected" "10.0.0.44" | transaction USERNAME maxspan=210s | lookup users.csv Username AS USERNAME | stats count by USERNAME "First Name" "Last Name" Region Country "Job Title" Role Department | table USERNAME "First Name" "Last Name" Region Country "Job Title" Role Department count | rename count AS Visits | sort -Visits

This returns all users who logged in to the website in the specified period in the order of most visits to fewest. But now, I'm more interested in the users who are NOT logging into my website (so I can find out why). Basically I want the full lookup.csv user list with their Visit counts for each period. How do I return the -0- Visits users along with the N Visits users together in one table?

[benefitcos · Accepted solution]

Based on Lucas' initial suggestion and some poking around I came up with this:

sourcetype!="*Private*" "Connected" "10.0.0.44" | eval USERNAME=lower(USERNAME) | transaction USERNAME maxspan=210s | lookup users.csv Username AS USERNAME | stats count by USERNAME "First Name" "Last Name" Region Country "Job Title" Role Department | inputlookup append=t users.csv | rename Username AS USERNAME | fillnull value=0 count | dedup USERNAME | table USERNAME "First Name" "Last Name" Region Country "Job Title" Role Department count | rename count AS Visits | sort -Visits

This returns all my users sorted by most visits on top and lists all my zero connections at the end of the report. Perfect!

[jakeblack]

Thank you!

[Lucas_K]

You need to mark your two sets of data and then do a stats against it.

ie. | eval visited=1 | inputlookup append=t users.csv | fillnull value=0 visited | stats sum(visited) by Username

so in total something like this

sourcetype!="Private" "Connected" "10.0.0.44" | transaction USERNAME maxspan=210s | lookup users.csv Username AS USERNAME | eval visited=1 | inputlookup append=t users.csv | fillnull value=0 visited | stats sum(visited) by Username

Broken down.

"| eval visited=1"
The first eval sets your existing data set a count of 1 to a field called "visited" for each found event. The problem is that the lookup will only find matching usernames from your original data set. Anything without a matching field will be dropped. So we need to add it back in.

"| inputlookup append=t users.csv"

That will simply add the entire csv to the end of your initially captured events. This will also include another entry for users already found in the first data search. This doesn't matter as we will full null on all these which will have no effect when we perform a stats on it at the end.

"| fillnull value=0 visited"
As all the original data set have visited=1 doing this will create atleast 1 searchable field to 0 per username.

"| stats sum(visited) by Username"

Now simply sum together the visited field per Username. Usernames without events will show as 0's. Usernames with events will show the number of matching events.

[Lucas_K]

you need to make sure you only use stats once at the end of your query after doing the appends, otherwise you loose the data required to do the count.

[benefitcos]

Thanks Lucas for the info. This exact formula returned all the entries from my .csv file with -0- visits while ignoring the counts from my first search.

But bits and pieces of your suggestion did work nicely in my formula, while adding the dedup command. View answer above.