Count does not shown as 0 when the username is not found in the sourcetype

[florencegoh]

Hi,
I want to shown the Total as 0 if username in lookup table has not event log . Using the fillnull value , it does not shown the results.

index=main sourcetype=oracle_ ACTION_NAME=LOGON [inputlookup testconn|fields username]|stats count AS Total by user |fillnull value=NULL

How to go changing it ? Would appreciate any advise.

[rjthibod]

For this scenario where you want a value for not present users, you probably have to do something a little nasty with an append or left join ... unless someone else can think of something cleaner.

index=main sourcetype=oracle_ ACTION_NAME=LOGON [inputlookup testconn|fields username]
| stats count as Total by username
| append [ | inputlookup testconn|fields username | eval Total=0]
| stats max(Total) as Total by username

[florencegoh]

Hi The above works..
If i need to use two fields in testconn inputlookup file to match against the events the result shows will be all 0 shown.

Here is the constructed command. Can advise on this.

index=main sourcetype=oracle ACTION_NAME=LOGON [inputlookup testconn|fields username,datsource]
| stats count(username) as uTotal,count(source) as sTotal by username datsource
| append [ | inputlookup testconn|fields username,datsource | eval uTotal=0,sTotal=0]
| stats max(uTotal),max(sTotal) by username datsource

The result is to shown username and datsource have total of the access and shown 0 with at the total of the access if there is no access.

sample output

username datsource uTotal sTotal

test1 DB1 10 10
test2 DB2 0 0
test1 DB1 0 0

[rjthibod]

Try this. You need to make the second search include the combination of all the username and datasource values. i do that using chart and then turning it back into a list of rows using untable.

index=main sourcetype=oracle ACTION_NAME=LOGON [inputlookup testconn|fields username,datsource]
| stats count(username) as uTotal,count(datsource) as sTotal by username datsource
| append 
  [ 
    | inputlookup testconn
    |fields username,datsource
    | chart limit=0 count by username, datsource
    | untable username datsource count
    | eval uTotal=0
    | eval sTotal=0
    | fields - count
  ]
| stats max(uTotal) as uTotal,max(sTotal) as sTotal by username datsource

[florencegoh]

the result shows are with values and compares against all datsource.
The results of wanted is that if there based on the inputlookup eg

username datsource
user1 db1
user2 db2
user1 db2

return results based on fixed number of inputlookup file with 3 username
username datsource count(username)

user1 db1 10
user2 db2 0
user1 db2 8

[rjthibod]

OK, what about this search? If it is incorrect, please share the output it produces and compare that to what it should be.

 index=main sourcetype=oracle ACTION_NAME=LOGON [inputlookup testconn|fields username,datsource]
 | stats count by username datsource
 | append 
   [ 
     | inputlookup testconn
     | fields username,datsource
     | eval count = 0
   ]
 | stats max(count) as count by username datsource

[florencegoh]

This is the search result shows as all value are 0 for the above query

username datsource count
user1 db1 0
user2 db2 0
user1 db2 0

It is showing the correct number of inputlookup testconn file where there is 3 records.

But the count is not picking up the user1 connection to datsource of db1, user2 connection to datsource of db2 and user1 datsource of db2. (Note: user1 username is on both db1 and also db2)

The result should be something similiar as below.
username datsource count
user1 db1 8
user2 db2 0 - assume there are no event logs found and should show

as 0.
user1 db2 6

[rjthibod]

I am sorry, but I don't see what the issue could be. Please double-check all of the syntax and names.

I ran the same kind of search with a lookup on my own data, and I did get the correct results.

[kamlesh_vaghela]

HI florencegoh,
can you please try this one?

index=main sourcetype=oracle_ ACTION_NAME=LOGON [inputlookup testconn|fields username]|stats count AS Total by user | appendcols [|stats count | rename count as Total]

Thanks