Hello,
I have a list of assets like this:
date,material,username,status
01/12/15,"IPad #4654654",eric,lent
01/12/15,"Iphone #4547879",paul,lent
01/15/15,"IPad #4654654",eric,return
01/16/15,"Keyboard #454456",eric,lent
01/17/15,"Nexus 7 #414456",eric,lent
and I would like to extract the list of materials that are actually lent by user. In this case:
eric Keyboard #454456 01/16/15
Nexus 7 #414456 01/17/15
paul Iphone #4547879 01/12/15
How I can do this?
Thanks in advance for your help.
Try this:
| inputcsv mycsv.csv
| eval statusInteger = if(match(status,"lent"), 1, -1)
| eval date = strptime(date, "%m/%d/%y")
| stats sum(statusInteger) as status, max(date) as date by username, material
| where status != 0
| fields - status
| fieldformat date=strftime(date, "%m/%d/%y")
| stats list(material) as material, list(date) as date by username
Output:
Try this:
| inputcsv mycsv.csv
| eval statusInteger = if(match(status,"lent"), 1, -1)
| eval date = strptime(date, "%m/%d/%y")
| stats sum(statusInteger) as status, max(date) as date by username, material
| where status != 0
| fields - status
| fieldformat date=strftime(date, "%m/%d/%y")
| stats list(material) as material, list(date) as date by username
Output:
Thanks it's working !
Assuming you already have the fields extracted from your csv type data, try something like this
your base search status=lent | stats list(material) as material list(date) as date by username
Thanks for your answer, but it's not working as i need, with your answer i have :
eric IPad #4654654
Keyboard #454456
Nexus 7 #414456
The material IPad has beed returned (01/15/15,"IPad #4654654",eric,return) however it shouldn't appear.