I have a lookup table that shows all the next-level managers of a particular manager as UserManager UserManagerx1 UserManagerx2 UserManagerx3... UserManagerx20. The top-level manager has about 20 nested managers, but all others have far fewer.
My indexed data has only data on the direct user and manager.
I have a dashboard select input to select the top-level manager that the user is interested in. I'm trying to create a search that will then display data of all direct report users as well as all levels below that manager via the lookup table.
This isn't working as expected. Thanks for any suggestions.
index="disk_index"
[| inputlookup sdmanager.csv
| table UserManager]
| search blah blah blah
| append
[ search index="disk_index" UserManager="$top_manager$"
]
| append
[search index="disk_index" UserManager="$top_manager$"
| lookup sdmanager.csv UserManager OUTPUT UserManagerx1 as UserManager
]
| append
[ search index="disk_index" UserManager="$top_manager$"
| lookup sdmanager.csv UserManager OUTPUT UserManagerx2 as UserManager
]
and so on until
| append
[ search index="disk_index" UserManager="$top_manager$"
| lookup sdmanager.csv UserManager OUTPUT UserManagerx20 as UserManager
]
| stats sum(UserUsage) as TotalUserUsage by UserManager DiskUser
And then I've also tried like this...
index="disk_index"
[| inputlookup sdmanager.csv
| table UserManager]
| search blah blah blah
| join type=inner
[| search UserManager="LAST,FIRST"
| stats sum(UserUsage) as TotalUserUsage by UserManager configuration process DiskUser ]
| join type=outer
[ | inputlookup sdmanager.csv | search UserManager="LAST,FIRST" | fields UserMangerx1
| stats sum(UserUsage) as TotalUserUsage by UserManager configuration process DiskUser ]
| table UserManager configuration process DiskUser TotalUserUsage
My next idea is to try map
, but I've never used it and it seems widely discouraged. Perhaps my lookup should be formatted differently? Or a foreach
command? Thanks again
index="disk_index"
[| inputlookup sdmanager.csv
| foreach UserManager UserManagerx* [ eval MatchUser=mvappend(MatchUser,<<FIELD>>)]
| where match(MatchUser,"$UserManager$")
| table UserManager]
| stats sum(UserUsage) as TotalUserUsage by UserManager ipArchitecture process configuration drop DiskUser
| table UserManager ipArchitecture process configuration drop DiskUser TotalUserUsage
Hi, @cblanton
How about this?
for the selected manager and then append the same search with each UserManagerx*n* from the lookup
Does this achieve your goal?
index="disk_index"
[| inputlookup sdmanager.csv
| foreach UserManager UserManagerx* [ eval MatchUser=mvappend(MatchUser,<<FIELD>>)]
| where match(MatchUser,"$UserManager$")
| table UserManager]
| stats sum(UserUsage) as TotalUserUsage by UserManager ipArchitecture process configuration drop DiskUser
| table UserManager ipArchitecture process configuration drop DiskUser TotalUserUsage
Hi, @cblanton
How about this?
for the selected manager and then append the same search with each UserManagerx*n* from the lookup
Does this achieve your goal?
I see,
but, where OR how does UserManagerxn display.
I'm not sure the relation DiskUser and UserManagerxn.
index="disk_index"
[| inputlookup sdmanager.csv
| search UserManager="$UserManager$"
| table UserManager]
| lookup sdmanager.csv UserManager OUTPUTNEW
| foreach UserManagerx* [ eval UserManager=mvappend(UserManager,<<FIELD>>)]
| mvexpand UserManager
| stats sum(UserUsage) as TotalUserUsage by UserManager ipArchitecture process configuration drop DiskUser
| table UserManager ipArchitecture process configuration drop DiskUser TotalUserUsage
Is this it? OR
index="disk_index"
[| inputlookup sdmanager.csv
| search UserManager="$UserManager$"
| foreach UserManagerx* [ eval UserManager=mvappend(UserManager,<<FIELD>>)]
| stats count by UserManager
| table UserManager]
| stats sum(UserUsage) as TotalUserUsage by UserManager ipArchitecture process configuration drop DiskUser
| table UserManager ipArchitecture process configuration drop DiskUser TotalUserUsage
It was the last one that worked.
index="disk_index"
[| inputlookup sdmanager.csv
| search UserManager="$UserManager$"
| foreach UserManagerx* [ eval UserManager=mvappend(UserManager,<<FIELD>>)]
| stats count by UserManager
| table UserManager]
| stats sum(UserUsage) as TotalUserUsage by UserManager ipArchitecture process configuration drop DiskUser
| table UserManager ipArchitecture process configuration drop DiskUser TotalUserUsage
I see.
After all, it is easy to do if logs and examples are presented.
Thank you for your reply.
that does it! thank you so much!
DiskUser is irrelevant.
I just want to look up all the corresponding UserManagerxn for the token selected UserManager and append the search with UserManager=UserManagerxn.
So if I try this, with my pretend csv. If the token passes NOBEL,ALFRED as the UserManager I expect to get all search results with NOBEL,ALFRED as UserManager plus PAGE,LARRY and MEAD,MARGARET, but instead I get only NOBEL,ALFRED and ROGERS,FRED. So it's finding the match in the UserManagerxn field and returning the UserManager, instead of matching the UserManager and returning all the UserManagerxn.
sdmanager.csv
UserManager,UserManagerx1,UserManagerx2,UserManagerx3,UserManagerx4,UserManagerx5,UserManagerx6,UserManagerx7
"ROGERS,FRED","SIMPSON,BART","ANDREWS,JULIE","CURIE,MARIE","NOBEL,AFLRED","PAGE,LARRY","MEAD,MARGRET","PAL,CHANDRANI"
"SIMPSON,BART","ROGERS,FRED","ANDREWS,JULIE","CURIE,MARIE","PAGE,LARRY","MEAD,MARGRET",,
"ANDREWS,JULIE","CURIE,MARIE",,,,,,
"CURIE,MARIE",,,,,,,,
"NOBEL,AFLRED","PAGE,LARRY","MEAD,MARGRET",,,,,
"PAGE,LARRY",,,,,,,
"MEAD,MARGRET",,,,,,,
I create the csv and lookup.
The following is my perception
Query of this assumption:
| makeresults
| eval DiskUser=split("abc234,del567,cureim,smthb,arst876,sdkji8,asdre8,skjyu876",",")
| mvexpand DiskUser
| eval UserManager=mvindex(split("ROGERS,FRED#SIMPSON,BART#ANDREWS,JULIE#CURIE,MARIE#NOBEL,AFLRED#PAGE,LARRY#MEAD,MARGRET","#"),random() % 7 )
| table DiskUser UserManager
| fillnull process configuration UserUsage
| foreach process configuration UserUsage [eval <<FIELD>> = random() % 100]
| rename COMMENT as "This is sample you provide. From here, the logic"
| lookup sdmanager.csv UserManager OUTPUTNEW
| foreach UserManager UserManagerx* [ eval MatchUser=mvappend(MatchUser,<<FIELD>>)]
| where match(MatchUser,"CURIE,MARIE")
| fields - MatchUser UserManagerx*
"CURIE,MARIE" assumes a token.
As a result, the DiskUser whose "CUIRE,MARIE" is each managers is selected.
Is my understanding correct?
DiskUser
does not specify UserManager
. DiskUser
is a field in the indexed data and no more relevant to the search than any other field that I'm tabling, like configuration
process
DiskUser
... UserManager
is a field in the indexed data and in the lookup table that has matching names.
I only need this simple search to run for the selected manager and then append the same search with each UserManagerx*n* from the lookup. And none of the fields I'm tabling are null.
index="disk_index"
[| inputlookup sdmanager.csv
| table UserManager]
| search UserManager="$UserManager$"
| stats sum(UserUsage) as TotalUserUsage by UserManager ipArchitecture process configuration drop DiskUser
| table UserManager ipArchitecture process configuration drop DiskUser TotalUserUsage
Can you provide your csv sample?
UserManager UserManagerx1 UserManagerx2 UserManagerx3 ... UserManagerx20
LAST,FIRST LAST,FIRST LAST,FIRST LAST,FIRST LAST, FIRST
LAST,FIRST LAST,FIRST LAST,FIRST
LAST,FIRST LAST,FIRST
LAST,FIRST LAST,FIRST LAST,FIRST LAST,FIRST
The dashboard input selects from all names listed as UserManager. I need corresponding user data for UserManger/DiskUser pairs for all existing UserManagerx#.
Thanks
index="disk_index"
[| inputlookup sdmanager.csv
| table UserManager]
this search is same index="disk_inde UserManager= LAST,FIRST OR UserManager= LAST,FIRST ...
I'm not sure
why many append
output same UserManager field.
and , what's DiskUser?
LAST,FIRST of same row is same?
UserManager is a field in the lookup and in the index.
DiskUser is the employee name, a direct report to the manager, and a field in the indexed data
Each LAST,FIRST is a different name.
So if UserManager Andrews,Julie is chosen in the drop down, I want to return all events where she is the manager and also all the managers that report to her, listed in the lookup as UserManagerx1 and so on.